Search Filters - Problem with Code

[echelon99]

Hi sub,

I apologize in advance for what will probably be a confusing-ass post. I have zero VBA experience and was handed a major database to finsh off coding last week.

Now, for my current form, I'm trying to create search filters that show results from a table that I have connected to this form (frm_ORE5, tbl_ORE5). The database was already set up with search parameters and had some buttons for filters set up. I was tasked with adding more filters (to be specific, filter out by regions).

This is everything I've managed to code out so far, and I'm sure some of it is wrong. (frmRegions, is the button form on the box, with buttons for all four regions on it).




Private Sub frmRegions_AfterUpdate()
Call refresh_Filters
End Sub

Private Function regionselect()
Select Case Me.frmRegions

Case 1
Region = "Canada"

Case 2
Region = "USA"

Case 3
Region = "Singapore"

Case 4
Region = "Europe & Asia Pacific"

End Select

End Function

Private Sub txtDate1_AfterUpdate()
Call refresh_Filters
End Sub
Private Sub txtDate2_AfterUpdate()
Call refresh_Filters
End Sub
Private Sub txtSearch_AfterUpdate()
Call refresh_Filters
End Sub

Private Sub refresh_Filters()

'This code probably needs to be refreshed to properly align with the layered search used on all pages
'(e.g. the txtSearch, dateSearch, GroupFilter, and Trending)
Dim searchFilter, dateFilter, allFilter As String
Dim searchString, date1String, date2String As String

Me.Refresh

If IsNull(Me.txtSearch) Then
searchString = "*"
Else
searchString = Me.txtSearch
End If


If IsNull(Me.txtDate1) Then
date1String = "1/1/2000"
Else
date1String = Me.txtDate1
End If


If IsNull(Me.txtDate2) Then
date2String = "1/1/2020"
Else
date2String = Me.txtDate2
End If

searchFilter = "(" & "[Event Name] Like '*" & searchString & "*'" & ")"
dateFilter = "(" & "[OpERA Create Date] Between " & "#" & date1String & "#" & " AND " & "#" & date2String & "#" & ")"
regionfilter = "(" & "[Region] Like '*" & regionselect & "*'" & ")"
allFilter = searchFilter & " AND " & dateFilter & " AND " & regionfilter
'(([tbl_ORE].[OpERA Create Date] Between #1/1/2013# And #4/1/2015#)) AND ([tbl_ORE].[Event Name] Like "*csim*") AND ([tbl_ORE].[Region] Like "*Canada*")
Me.frm_ORE_All.Form.Filter = allFilter
Me.frm_ORE_All.Form.FilterOn = True

'regionFilterResult = Function(frmRegions_AfterUpdate)



End Sub


Of course, while all the other search functions work (date, trending etc), the ones I've written out (the region filters) don't do anytying. Clicking on them provides no result.

At this point, really any tips/advice would be greatly appreciated.

Thanks

[jonh]

Your Regionselect function doesn't return a value. Looks like it's just setting an undefined variable called Region.
Type 'option explicit' to make sure all variables are dimensioned to prevent these kinds of issues.