Access Multi-field Search with Results as you type

问题:

Good morning, all,

My question today is regarding multi-field search.

I have a split form (fields and individual record at the top, all data in datasheet view on the bottom). I have multiple fields I would like to search on, so that a user may find a particular person based on a number of criteria.

Here is the VBA that a colleague helped me with that works PERFECTLY right now, and I'd like to add just a bit more functionality to it by allowing it to search on more than just the one field.

Private Sub txtSearch_Change()
    Dim strFilter As String
    On Error Resume Next
    If Me.txtSearch.Text <> "" Then
        strFilter = "[Last_Name] Like '*" & Me.txtSearch.Text & "*'"
        Me.Filter = strFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    With Me.txtSearch
        .SetFocus
        .SelStart = Len(Me.txtSearch.Text)
    End With
End Sub

Every time I type a letter, the search requeries and supplies only the information that meets that search criteria. What I would like it to do is filter even if it was a First_Name field or the SSN field, etc. Can anyone supply what bit of code I'd need to make it work? I've been searching through multiple forums, videos, posts, etc. and nothing seems to make a bit of difference as I keep throwing errors.

What is the OR statement I need to enable my search to span multiple fields within my form (assuming there is one)?

*Note that I would like to do this without a search button, so I want to keep this as a Change() event, not an AfterUpdate().

Thank you!

EDIT

Code too long for standard reply comment:

This code hangs. I'm probably setting up VBA wrong.

'This code works great, but if I put in a space character, it crashes the DB
Private Sub txtSearch_Change()
    Me.txtSearch.Text = Trim(Me.txtSearch.Text)
    Dim strFilter As String
    Dim sSearch As String

    If Me.txtSearch.Text <> "" Then
        sSearch = "'*" & Replace(Me.txtSearch.Text"'", "''") & "*'"
        strFilter2 = "[Last_Name] Like " & sSearch & " OR [First_Name] Like " & sSearch & " OR [SSN] Like " & sSearch
        Me.Filter = strFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    With Me.txtSearch
        .SetFocus
        .SelLength = 0
        .SelStart = Len(Me.txtSearch.Text)
    End With
End Sub


'This code is what I have that will reset the textbox to blank and requery, giving me all the people in the DB
Private Sub txtSearch_Click()
    Me.txtSearch.Text = ""
    Me.Requery
    With Me.txtSearch
        .SetFocus
        .SelStart = Len(Me.txtSearch.Text)
    End With
End Sub

Does this throw any red flags as to why I'm getting a crash?

回答1:

You only need to change the definition of strFilter. For convenience I would use an additional variable.

Dim sSearch As String
If Me.txtSearch.Text <> "" Then
    ' Avoid problems with search strings containing "'"
    sSearch = "'*" & Replace(Me.txtSearch.Text, "'", "''") & "*'"
    ' Add all fields you want to search with OR
    strFilter = "[Last_Name] Like " & sSearch  & " OR [First_Name] Like " & sSearch ' etc.

回答2:

First, the textbox click event whereby when you click the box, it clears the text and resets the search (no need for a reset button)

Private Sub txtSearch_Click()
    Me.txtSearch.SetFocus 'new line of code
    Me.txtSearch.Text = ""
    Me.Requery
With Me.txtSearch
    .SetFocus
    .SelStart
End With
End Sub

This is the actual search, that will search multiple fields

Private Sub txtSearch_Change()
    Dim strFilter As String
    Dim sSearch As String
    On Error Resume Next

If Me.txtSearch.Text <> "" Then
    sSearch = "'*" & Replace(Me.txtSearch.Text, "'", "''") & "*'"
    strFilter = "[Last_Name] Like " & sSearch & " OR [First_Name] Like " & sSearch & " OR [SSN] Like " & sSearch
    Me.Filter = strFilter
    Me.FilterOn = True
Else
    Me.Filter = ""
    Me.FilterOn = False
End If

If Me.Recordset.RecordCount = 0 Then 'new line of code
    Me.Filter = "" 'new line of code
    Me.FilterOn = False 'new line of code
    Me.txtSearch.SetFocus 'new line of code
    Me.txtSearch.Text = "" 'new line of code
Exit Sub 'new line of code
End If 'new line of code

With Me.txtSearch
    .SetFocus
    .SelStart = Len(Me.txtSearch.Text)
End With
End Sub