How to Add Search and Reset Buttons to an Access Continuous Form
Question details
The user wants to add functional Search and Reset buttons to a Microsoft Access continuous form to filter records across multiple fields.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Designing a user interface in a database where users need to search for specific records across multiple columns on a continuous form without opening secondary duplicate forms.
- Observed behavior
- The user needs to dynamically apply and clear form filters using VBA based on an unbound search text box.
Ensure your Access form's Default View is set to 'Continuous Forms' and that you have added an unbound text box for user input, along with two command buttons for Search and Reset.
Use VBA to Dynamically Apply Form Filters
Add a VBA script to the On Click events of your Search and Reset command buttons to adjust the form's Filter property.
By modifying the 'Me.Filter' property, you can search multiple fields at once within the same continuous form. This method avoids the need to build duplicate forms or complex subqueries.
Right-click your continuous form in the Navigation Pane and select 'Design View'.
Select your Search button, open the Property Sheet, navigate to the Event tab, and select '[Event Procedure]' for the 'On Click' event. Click the ellipsis (...) to open the VBA editor.
Enter the following code, replacing 'SearchTextBox' with your text box name and 'Field1' with your actual field names: Dim strWhere As String, strValue As String strValue = Me.SearchTextBox If strValue = "" Then Exit Sub strWhere = "[Field1] Like '*" & strValue & "*' OR [Field2] Like '*" & strValue & "*'" Me.Filter = strWhere Me.FilterOn = True
Select the Reset button, create a new '[Event Procedure]' for its On Click event, and enter the following code to clear the filter: Me.Filter = "" Me.FilterOn = False Me.SearchTextBox = ""
Streamline Your Data and Documents with WPS Office
While Microsoft Access is used for database management, WPS Office provides a free, lightweight, and highly compatible alternative for your daily document processing, spreadsheet data filtering, and presentation needs. It fully supports standard Microsoft Office formats with a familiar interface.
Frequently Asked Questions
Can I search all fields in an Access form at once without typing out every field name?
Using the standard VBA Filter property, you generally must specify each field in the WHERE clause (e.g., [Field1] Like '*text*' OR [Field2] Like '*text*'). While you can write a loop in VBA to iterate through all form controls, explicitly declaring the fields is more reliable and performs better.
Why isn't my continuous form updating when I click the Search button?
Ensure your VBA code includes the command 'Me.FilterOn = True' after defining the 'Me.Filter' string. Without this command, Access stores the filter criteria but does not apply it to display the matching records.
Does this VBA filter method work if my form is based on a query instead of a table?
Yes, adjusting the Filter property works identically whether the continuous form's Record Source is a direct table or a query. Just ensure that the field names you reference in the VBA code match the columns outputted by the query.
