I am trying to create a form that allows you to return results based on multiple criteria.
I have FirstName field, LastName field, and State Field.
I also have text boxes named searchFirst, searchLast, searchState where users can input criteria.
How do I build the filter so that it takes the criteria from any of these 3 fields?
For example. If I type in First Name "Mike" and State "New York" I want to see all Mikes from New York.
If I use the code below the form filters based on the first criteria (First Name) but ignores anything I type into the "State" text box.
Here's the code from one of the search boxes keyup event
I have FirstName field, LastName field, and State Field.
I also have text boxes named searchFirst, searchLast, searchState where users can input criteria.
How do I build the filter so that it takes the criteria from any of these 3 fields?
For example. If I type in First Name "Mike" and State "New York" I want to see all Mikes from New York.
If I use the code below the form filters based on the first criteria (First Name) but ignores anything I type into the "State" text box.
Here's the code from one of the search boxes keyup event
Code:
Private Sub txt_searchFirst_KeyUp(KeyCode As Integer, Shift As Integer)
On Error GoTo ErrHandler
Dim filterTextsearchFirst As String
Dim filterTextsearchLast As String
Dim filterTextsearchState As String
'Apply or update filter based on user input.
Me.searchFirst.SetFocus
filterTextsearchFirst = searchFirst.Text & ""
filterTextsearchLast = searchLast.Text & ""
filterTextsearchState = searchState.Text & ""
If Len(filterTextsearchFirst) > 0 Or Len(filterTextsearchLast) > 0 Or Len(filterTextsearchState) > 0 Then
Me.Form.Filter = "[FirstName] LIKE '*" & filterTextsearchFirst & "*' AND [LastName] LIKE '*" & filterTextsearchLast & "*' AND [STATE] LIKE '*" & filterTextsearchState & "*'"
Me.FilterOn = True
'Retain filter text in search box after refresh.
With searchFirst
.SetFocus
.Value = filterTextsearchFirst
.SelLength = 0
.SelStart = Len(searchFirst.Text)
End With
Else
' Remove filter.
Me.FilterOn = False
Me.Filter = ""
searchFirst.SetFocus
End If
Exit Sub
ErrHandler:
Resume Next
End Sub
Last edited: