How to Requery an Access Subform After Updating a Main Form Field
Question details
The user needs a Microsoft Access subform to automatically update its displayed records immediately after a specific field on the main form is changed.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A parameter query in a subform uses a main form field as a criterion, but changing the main form field does not dynamically trigger the subform to display the new dataset.
- Observed behavior
- The subform does not update upon changing the main form field. Even after applying Requery, Refresh, and saving the record, the user must navigate away from the current record and return to see the updated results.
Verify the exact name of your subform control on the main form, as targeting the incorrect control name is a common reason for Requery failures in MS Access VBA.
Use VBA in the After Update Event to Force a Recalculation and Requery
This solution forces the main form to save its current state and recalculate before explicitly requerying the subform control via VBA.
Often, simply calling 'Requery' does not work if the main record hasn't been saved or if Access hasn't recognized the new field value. By saving the record, recalculating, and targeting the subform control (rather than the form object), the subform will update instantly.
Open your main form in Design View. Right-click the field (e.g., SerNo) that acts as the criterion and select 'Properties'.
In the Property Sheet, go to the 'Event' tab. Find the 'After Update' property, select '[Event Procedure]' from the dropdown, and click the ellipsis (...) to open the VBA editor.
Input the following code: 'Me.Dirty = False' (to save the record), followed by 'Me.Recalc' (to force Access to recognize the new value), and finally 'Me!YourSubformControlName.Requery' (replacing YourSubformControlName with your actual subform control name).
Save the VBA module, switch the form back to Form View, and update the main form field to verify the subform refreshes automatically.
Link Main Form and Subform Using Master/Child Fields
Use this built-in Access feature instead of a parameter query to automatically filter subform records based on a main form field.
Looking for a Lightweight Office Solution?
While Microsoft Access is a traditional database tool, many data tracking, reporting, and management tasks can be handled efficiently using advanced spreadsheets. WPS Office provides a powerful, highly compatible, and completely free alternative for your daily document, spreadsheet, and presentation tasks.
- 1. Download the Installer: Visit the WPS Office website and download the free installation package tailored to your operating system.
- 2. Install WPS Office: Run the downloaded installer and follow the quick setup wizard to install the suite on your device.
- 3. Open Your Office Files: Launch WPS Office and instantly open your existing Word documents, Excel spreadsheets, and PowerPoint presentations with full compatibility.
Frequently Asked Questions
Why does Me.Refresh not update my Access subform records?
The Refresh method only updates the data currently displayed in existing records to reflect changes made by other users. It does not re-run the underlying query to fetch new records, remove deleted records, or apply new filter criteria. You must use Requery to re-evaluate parameters.
How do I find the correct name of my subform control?
Open your main form in Design View. Click once on the border of the subform container (do not click inside it). Open the Property Sheet, navigate to the 'Other' tab, and look at the 'Name' property. This is the control name you must use in your VBA code.
What is the difference between Me.SubformControl.Form.Requery and Me.SubformControl.Requery?
Calling '.Requery' directly on the subform control re-evaluates the control's data container on the main form, which is highly effective when main form parameters change. Calling '.Form.Requery' targets the form object nested inside the control. Usually, requerying the control is more reliable for resolving parameter updates.
