How to Refresh an Access Query When Form Criteria Change
Question details
The user needs an open MS Access query to refresh its results automatically when a criteria selection, such as a MealID combo box on a search form, is modified.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Filtering database records dynamically by changing a combo box selection on a search form.
- Observed behavior
- The open query does not automatically update to reflect the newly selected form criteria and requires a manual refresh or requery action.
Ensure you have basic familiarity with the Access VBA editor and know the exact names of your search form, display form, and query.
Use a Form Based on the Query and Requery
This is the most highly recommended approach. Instead of displaying a raw query datasheet, use a form or datasheet form and trigger a Requery event.
In Microsoft Access, it is generally preferable to use forms or reports to display data rather than interacting directly with a table or query datasheet. Forms provide much better control over events, layout, and user interactions.
By binding a form to your query and using the Requery method, you can seamlessly update the displayed records whenever the criteria on your search form change.
Right-click your search form (e.g., Search0f) in the navigation pane and select Design View. Click on the combo box where the criteria are selected.
Open the Property Sheet (F4), navigate to the 'Event' tab, find the 'After Update' property, select [Event Procedure] from the dropdown, and click the ellipsis (...) button to open the VBA Editor.
Inside the VBA routine, type: DoCmd.OpenForm "YourDisplayFormName" followed on the next line by: Forms("YourDisplayFormName").Requery. Replace 'YourDisplayFormName' with the actual name of the form displaying your query results.
Close and Reopen the Query
Use this method if you strictly must work directly with a query datasheet and need it to refresh upon changing form criteria.
Looking for a Free and Lightweight Office Suite?
While Microsoft Access is a powerful tool for complex relational databases, managing everyday documents, spreadsheets, and data analysis is often much easier with WPS Office. As a free, lightweight alternative, WPS Office provides everything you need to format exported database reports, create presentations, and edit text documents without a steep learning curve.
- 1. Download and Install: Visit the official WPS Office website to download the free installation package for your operating system.
- 2. Export Access Data: Export your Access query results as an Excel or CSV file from the 'External Data' tab in MS Access.
- 3. Analyze in WPS Spreadsheet: Open the exported file in WPS Spreadsheet to utilize powerful charts, pivot tables, and data analysis tools.
Frequently Asked Questions
Why doesn't my Access query update automatically when I change a value on my form?
Queries in MS Access do not actively poll for changes in form controls. To apply new criteria to an open query or form, you must explicitly trigger a refresh action, usually by attaching a VBA 'Requery' command to the 'After Update' or 'On Change' event of the control.
Where should I place the VBA code to requery the form?
The VBA code should be placed in the 'After Update' event property of the specific control (like a combo box or text box) where the user inputs or selects the criteria.
Is it better to use a query datasheet or a form to display results?
It is highly recommended to use a form (even if set to Datasheet view) or a report rather than interacting directly with a query datasheet. Forms offer much better flexibility for formatting, user experience, and handling events like Requery.
