How to Filter an Access Form with a Text Box Using VBA
Question details
The user needs to filter a Microsoft Access form based on text entered in a text box via VBA, specifically avoiding inefficient requeries after every keystroke on a massive recordset.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Searching and filtering through a large recordset of over 40,000 rows by typing an entry (like a street name) into a text box and pressing Enter.
- Observed behavior
- Triggering the filter on every keystroke causes extreme inefficiency and lag. Furthermore, referencing the text box name instead of the record source field name in the filter expression results in the filter failing to apply.
Verify the exact field name in your form's underlying table or query that you intend to filter. Ensure your search text box has a clearly distinct name from the data field to prevent circular references in your VBA code.
Apply the VBA Filter Using the After Update Event
Triggering the VBA filter only after the user finishes typing (such as pressing Enter) prevents the severe lag caused by requerying a large dataset on every keystroke.
When dealing with substantial recordsets, applying a filter using the On Key Up event forces the database to requery the data repeatedly as each character is typed. Utilizing the After Update event ensures the filter runs only once the user hits Enter or shifts focus away from the text box.
Additionally, a filter acts like an SQL WHERE clause. The left side of your expression must point to an actual field in the form's record source, not the name of the text box itself.
Right-click your Microsoft Access form in the navigation pane and select Design View. Click on the text box you want to use for searching.
Open the Property Sheet (F4), navigate to the Event tab, and locate the 'On After Update' property. Click the ellipsis (...) button and choose Code Builder to open the VBA Editor.
In the VBA editor, type your filter code. Ensure you reference the underlying data field correctly. Use the Replace function to prevent errors from apostrophes in the search text: Me.Filter = "[YourFieldName] LIKE '*" & Replace(Me.YourTextBoxName, "'", "''") & "*'"
Add the command to activate the filter on the very next line: Me.FilterOn = True
Clear the Filter When the Search Box is Empty
Add logic to your VBA code to remove the filter and display all records whenever the user clears the search text box.
Looking for a Lightweight Office Suite?
While WPS Office does not feature a direct database application like Access, it provides powerful, free alternatives to Word, Excel, and PowerPoint. Experience a fast, highly compatible office suite designed to streamline your daily workflow.
- 1. Download the Installer: Visit the official WPS Office website and click the Free Download button.
- 2. Install WPS Office: Run the downloaded installer and follow the simple on-screen instructions to set up the software.
- 3. Start Working: Open WPS Office to instantly view and edit your spreadsheets, documents, and presentations with full format compatibility.
Frequently Asked Questions
Why does filtering a large Access database lag so much?
Filtering causes lag when it is attached to the On Key Up or On Change events. This triggers the database to repeatedly requery the entire dataset after every single keystroke. Using the After Update event prevents this by only applying the search once you press Enter.
Why does my VBA filter do nothing when I type text?
This commonly happens when the left side of your VBA filter expression incorrectly references the text box control's name rather than the actual data field name located in the form's underlying record source.
How do I handle apostrophes in the search text box to avoid VBA errors?
Apostrophes act as string delimiters in VBA and SQL, which breaks the filter string. Use the VBA Replace function around your text box reference to escape them by doubling the quotes, for example: Replace(Me.SearchBox, "'", "''").
