VBA Access Requerying a Query that is outside of the current Form Object

Requerying External Queries in MS Access VBA

Learn how to programmatically refresh or requery a saved Access query (QueryDef) from within a VBA form, even when it's not directly bound to the form's record source.

In Microsoft Access, forms often display data sourced from tables or queries. While requerying a form's direct RecordSource is straightforward using Me.Requery or Me.Refresh, situations arise where you need to update a separate, saved query object (a QueryDef) that isn't directly tied to the current form. This is particularly common when a query serves as the RecordSource for a subform, a report, or is used by other parts of your application, and its underlying data or parameters have changed.

Understanding the Need for External Query Requery

Access forms and reports often rely on QueryDef objects to retrieve and filter data. When the data in the underlying tables changes, or if the query itself uses parameters that have been updated (e.g., criteria based on controls on a different form), the QueryDef might hold stale results. Simply requerying the current form won't update these external QueryDef objects. To ensure data consistency and accuracy across your application, you must explicitly tell Access to re-evaluate these queries.

flowchart TD
    A[User Action on Form1] --> B{Data Changed or Parameter Updated?}
    B -->|Yes| C[Need to Update QueryDef 'MyQuery']
    C --> D{Form1.Requery?}
    D -->|No, MyQuery is external| E[Programmatically Requery 'MyQuery']
    E --> F[Subform/Report/Other Object Uses Updated 'MyQuery']
    F --> G[Display Fresh Data]
    D -->|Yes, if MyQuery is Form1's RecordSource| G

Flowchart illustrating the need for external query requery.

Methods for Requerying a QueryDef

There isn't a direct QueryDef.Requery method in Access VBA. Instead, you achieve the effect of 'requerying' by forcing Access to re-evaluate the query definition. The most common and effective way to do this is by referencing the query in a context that causes it to be recompiled or re-executed. This typically involves setting the RecordSource of a temporary object or, more commonly, by refreshing a control or object that uses that query.

Practical Implementation: Requerying a Subform's RecordSource

The most frequent scenario for needing to requery an external query is when that query serves as the RecordSource for a subform. If the main form updates data or parameters that affect the subform's query, you need to tell the subform to refresh its data based on the updated query definition.

Private Sub cmdUpdateData_Click()
    ' Assume 'MySubformControl' is the name of the subform control on the main form
    ' And 'MySubformQuery' is the name of the QueryDef used by the subform

    ' 1. Perform actions that might change data or parameters
    '    e.g., Me.txtParameter = "NewValue"
    '    e.Refresh ' Refresh main form if its data affects the query

    ' 2. Requery the subform control. This forces the subform to re-evaluate
    '    its RecordSource (MySubformQuery) and fetch fresh data.
    Me.MySubformControl.Requery

    MsgBox "Subform data has been refreshed!", vbInformation
End Sub

VBA code to requery a subform control, which in turn re-evaluates its underlying QueryDef.

Requerying a QueryDef Used by a Report

Similar to subforms, if a report uses a QueryDef as its RecordSource, you'll need to ensure that query is up-to-date before opening or previewing the report. While you can't Requery a report directly in the same way as a form, opening it will cause it to execute its RecordSource query. If the query relies on parameters from an open form, ensure those parameters are set before opening the report.

Private Sub cmdOpenReport_Click()
    ' Assume 'rptMyReport' uses 'MyReportQuery' as its RecordSource
    ' And 'MyReportQuery' relies on a control on the current form, e.g., Me.txtDateFilter

    ' Ensure any parameters on the current form are updated/saved if necessary
    Me.Refresh

    ' Open the report. Access will execute 'MyReportQuery' with current parameter values.
    DoCmd.OpenReport "rptMyReport", acViewPreview
End Sub

VBA code to open a report, ensuring its underlying QueryDef is re-evaluated with current parameters.

Advanced Scenario: Forcing Re-evaluation of a Standalone QueryDef

In rare cases, you might need to force a QueryDef to re-evaluate without immediately binding it to a form or report. This can be useful if you're using the QueryDef programmatically (e.g., to populate an array or for data manipulation). One way to achieve this is to temporarily assign the QueryDef to a Recordset object and then close it. This action forces Access to compile and execute the query.

Private Sub ForceQueryDefReevaluation()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset

    Set db = CurrentDb

    On Error Resume Next
    Set qdf = db.QueryDefs("MyStandaloneQuery")
    On Error GoTo 0

    If Not qdf Is Nothing Then
        ' Temporarily open a recordset based on the QueryDef
        ' This forces Access to re-evaluate the query definition.
        Set rs = qdf.OpenRecordset(dbOpenSnapshot)
        rs.Close
        Set rs = Nothing
        MsgBox "QueryDef 'MyStandaloneQuery' has been re-evaluated.", vbInformation
    Else
        MsgBox "QueryDef 'MyStandaloneQuery' not found.", vbExclamation
    End If

    Set qdf = Nothing
    Set db = Nothing
End Sub

VBA code to force re-evaluation of a standalone QueryDef by opening a temporary recordset.

By understanding these techniques, you can ensure that your Access applications always display and process the most current data, even when dealing with complex interdependencies between forms, reports, and saved queries.