title: Access Multi-field Search with Results as you type
body: <p>Good morning, all,</p>
  
  <p>My question today is regarding multi-field search.</p>
  
  <p>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.</p>
  
  <p>Here is the VBA that a colleague helped me with that works PERFECTLY right now, and I'd like to add just a <em>bit</em> more functionality to it by allowing it to search on more than just the one field.</p>
  
  <pre><code>Private Sub txtSearch_Change()
      Dim strFilter As String
      On Error Resume Next
      If Me.txtSearch.Text &lt;&gt; "" Then
          strFilter = "[Last_Name] Like '*" &amp; Me.txtSearch.Text &amp; "*'"
          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
  </code></pre>
  
  <p>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.</p>
  
  <p>What is the OR statement I need to enable my search to span multiple fields within my form (assuming there is one)?</p>
  
  <p>*Note that I would like to do this without a search button, so I want to keep this as a Change() event, <strong>not</strong> an AfterUpdate().</p>
  
  <p>Thank you!</p>
  
  <p><strong><em>EDIT</em></strong></p>
  
  <p>Code too long for standard reply comment: </p>
  
  <p>This code hangs. I'm probably setting up VBA wrong.</p>
  
  <pre><code>'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 &lt;&gt; "" Then
          sSearch = "'*" &amp; Replace(Me.txtSearch.Text"'", "''") &amp; "*'"
          strFilter2 = "[Last_Name] Like " &amp; sSearch &amp; " OR [First_Name] Like " &amp; sSearch &amp; " OR [SSN] Like " &amp; 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
  </code></pre>
  
  <p>Does this throw any red flags as to why I'm getting a crash?</p>