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.

 

4 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

Leave a Reply

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