How To Create a Dynamic Search Filter on a Microsoft Access Form

Print Friendly, PDF & Email

Best practice when retrieving data in Microsoft Access or any database, for that matter, is to limit the amount of data that you pull at any one time. This means using the proper filters and limiting the number of fields retrieved.  Often, you still end up with a lot of information to look through anyway. That’s when it can help to have a dynamic search filter that will enable you to easily search the records with just a few keystrokes.

One easy way to do this is use the form’s Filter and FilterOn properties along with the KeyUp event as shown here.

The form shown above is set to Continuous Forms and has just a simple query behind it based on a Contacts table with 1000 records in it.  The filter box in the top right-hand corner will hold the search string.  Let’s look at the On Key Up event for the textbox:

Private Sub txtNameFilter_KeyUp(KeyCode As Integer, Shift As Integer)

On Error GoTo errHandler

Dim filterText As String

'Apply or update filter based on user input.
If Len(txtNameFilter.Text) > 0 Then
   filterText = txtNameFilter.Text
   Me.Form.Filter = "[Contacts]![first_name] LIKE '*" & filterText & "*' OR [Contacts]![last_name] LIKE '*" & filterText & "*'"
   Me.FilterOn = True
  'Retain filter text in search box after refresh.
   txtNameFilter.Text = filterText
   txtNameFilter.SelStart = Len(txtNameFilter.Text)
Else
   ' Remove filter.
   Me.Filter = ""
   Me.FilterOn = False
   txtNameFilter.SetFocus
End If

Exit Sub

errHandler:

MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."

End Sub

Every time the user presses and releases a key, the routine starts by determining if there’s still a search string in the box (Len > 0).  If there is, it stores the string in the filterText variable.

Then it sets the form’s filter to use the search string on the required fields. The Filter property accepts the same criteria spec that you would see in a SQL statement.  In this example, the LIKE keyword is used on the first_name and last_name fields to indicate that any records where either field contains the search string should be included in the results.

"[Contacts]![first_name] LIKE '*" & filterText & "*' OR 
[Contacts]![last_name] LIKE '*" & filterText & "*'"

The FilterOn property then ensures that the filter is activated if it isn’t already.

The next two lines retain the search string within the search box.

  'Retain filter text in search box after refresh.
   txtNameFilter.Text = filterText
   txtNameFilter.SelStart = Len(txtNameFilter.Text)

The reason for this is that, in this example, the search box is placed on the same form as the records that are being filtered.  When the filter is applied, the form is requeried and the search box is blanked since it’s not bound to anything. The lines above were my solution for retaining the search value.  The contents of the variable are placed back in the search box and the next line places the cursor at the end of the text in the box so that when the user hit’s another key, the search text is edited appropriately.

If your search box is filtering the contents of a subform or another form other than the one it’s on, these two lines will not be necessary.

As shown above, when I type Tom in the search box, the records are automatically filtered to return only records where either the first or last name contains that string.  If I remove a letter and leave ‘To’, for example, the results would automatically adjust again.

This type of dynamic search box can save your users a bit of time when working with a large number of records.  Even 25 or 50 records can be hard for many users to search through and making things a little easier definitely improves the usability of your programs.

 

10 thoughts on “How To Create a Dynamic Search Filter on a Microsoft Access Form

  1. CG

    Hi there.
    Using your example on my database but instead, I’m only filtering one field instead of two, because the complete name is all on the same cell. Problem is that when I try to use “space” between the names, it ignores is.

    Example:
    – need to search for “Joe Scott”
    – Typing “Joe” works perfectly, try to use the “space” and then “Scott” and it becomes “JoeScott”.

    Any help?
    Thanks in advance

    Reply
    1. Andrew Comeau Post author

      I think the bug is in this line:

      txtNameFilter.SelStart = Len(txtNameFilter.Text)

      The LEN function ignores trailing spaces so, after you put in the space, it returns the cursor to before the space.

      This would be one way to fix it –
      txtNameFilter.SelStart = Len(Replace(txtNameFilter.Text, " ", "."))

      That replaces trailing spaces with periods before counting the length but it won’t affect the text in the box.

      Reply
    1. reza

      try this code
      without .text in this line txtNameFilter.Text = filterText

      txtNameFilter = filterText
      txtNameFilter.SelStart = Len(txtNameFilter.Text)

      it works for me

      Reply
  2. Zac Tyrrell

    Hi, I’m having somewhat of an issue with this.

    I have created a search button at the top of my multi-record form that, when clicked, will take data from 2 fields and will then filter the form records accordingly. The two fields are a drop-down box containing the field names, and the second is a text box to type in what you want to filter by for that field. The code i am using is as follows:

    Private Sub Search_Click()

    ‘On Error GoTo errHandler (marked with ‘ for now so i can use the access “debug” button)

    Dim txtSearch As String

    If Len(txtSearch) > 0 Then
    (Form name).Form.Filter = “[” & Me.cmbSearch & “] like ‘*” & Me.txtSearch & “*'”
    (Form name).Form.FilterOn = True
    Else
    (Form name).Form.Filter = “”
    (Form name).Form.FilterOn = False
    End If

    Exit Sub

    ‘errHandler:

    ‘MsgBox Err.Number & ” – ” & Err.Description, vbOKOnly, “Error …”

    End Sub

    The issue is, when i click the search button to try and filter, i get the error saying “Object required”. I have also included a “reset” button that constantly returns the same error, with code as follows:

    Private Sub reset_Click()
    (Form name).Form.Filter = “”
    (Form name).Form.FilterOn = False
    (Form name).Requery
    End Sub

    I can’t find anything useful on this anywhere, please help!

    Reply
    1. Andrew Comeau Post author

      Your form needs to be based on an actual saved query object in order to use the in-form filter. In other works, you need to create a query, save it to the database and then base your form off of that instead of storing the SQL in the form’s record source. That would be the first thing I’d check.

      Reply
  3. Robin Brooks

    Hi Andrew, Your filter method is working really well for me when the text I enter into the txtNameFilter text box is found, however when the search string isn’t found I get the error:

    “2185 – You can’t reference a property or method for a control unless the control has the focus.”

    Do you know why this might be?

    Thanks.

    Reply
    1. Zane L

      I modified my code to get a record count before applying the filter. This worked for me.

      Private Sub Text_Filter_EMR_Key_KeyUp(KeyCode As Integer, Shift As Integer)

      On Error GoTo errHandler

      Dim filterText As String
      Dim records As Integer

      filterText = Text_Filter_EMR_Key.Text

      ‘Get a record count
      records = DCount(“[EMR_KEY]”, “[ERR_LIST]”, “[EMR_KEY] LIKE ‘*” & filterText & “*'”)

      ‘Apply or update filter based on user input.
      If records > 0 Then

      Me.Form.Filter = “[ERR_LIST]![EMR_KEY] LIKE ‘*” & filterText & “*'”
      Me.FilterOn = True

      ‘Retain filter text in search box after refresh.
      Text_Filter_EMR_Key.Text = filterText
      Text_Filter_EMR_Key.SelStart = Len(Text_Filter_EMR_Key.Text)
      Else
      ‘ Remove filter.
      MsgBox “No Matching Records”, vbOKOnly
      Text_Filter_EMR_Key.Text = “”
      Me.Filter = “”
      Me.FilterOn = False
      Me.Text_Filter_EMR_Key.SetFocus
      End If
      Exit Sub

      errHandler:
      MsgBox Err.Number & ” – ” & Err.Description, vbOKOnly, “Error …”
      End Sub

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *