How To Create a Dynamic Search Filter on a Microsoft Access Form
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.
Sign up for our newsletter to receive updates about new projects, including the upcoming book "Self-Guided SQL"!
We respect your privacy and will never share your information with third-parties. See our privacy policy for more information.
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
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.
I’m sorry, didn’t notice you had answered…
Tried your suggestion but it doesn’t seem to change anything… :\
That’s my best guess without actually seeing your work. I suggest you keep experimenting with it and if you find the answer, let me know.
try this code
without .text in this line txtNameFilter.Text = filterText
txtNameFilter = filterText
txtNameFilter.SelStart = Len(txtNameFilter.Text)
it works for me
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!
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.
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.
I am running into this same issue. It also bypasses the error handler routine
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