VBA & Macro Problems

How to Assign an Access Query Result to a TempVar in VBA

Adam Davis Oct 10, 2026 868 views

Question details

The user wants to assign the result of a stored procedure or query to a TempVar in Microsoft Access when a form control is updated.

How to Assign an Access Query Result to a TempVar in VBA

Product: Microsoft Access
Device & OS: not provided
Scenario: Updating a form control to trigger VBA code that fetches a query result and stores it in a temporary variable (TempVar).
Observed behavior: Users may encounter a 'Variable not defined' error or incorrectly use Lookup() instead of DLookup(), leading to failures in assigning the correct value.

Before you start

Ensure that your Access database is saved in a trusted location so that VBA macros can run, and verify that the saved query or table you intend to reference actually exists.

Solution 1Recommended

Use DLookup() to Assign the Result to a TempVar

The most reliable way to retrieve a single value from a saved query or table in Access VBA is using the DLookup function.

When retrieving specific lookup values from a database table or a saved query to store in a TempVar, you must retrieve the value explicitly. Using RecordCount will only give you the number of rows, not the data itself.

Identify the data source

Determine the exact name of the saved query or table and the specific field name you want to retrieve.

Write the DLookup function

Use the syntax DLookup("FieldName", "QueryName", "Criteria") to explicitly fetch the returned value. Ensure you use DLookup() and not Lookup().

Assign the value to the TempVar

Assign the retrieved value to your TempVar in your form control update event using the syntax: TempVars("Club_Lookup") = DLookup(...).

Use DLookup() to Assign the Result to a TempVar

RecordCount vs DLookup: Avoid using RecordCount if you need a specific field value. RecordCount only returns the total number of records, not the actual lookup value.

Solution 2

Troubleshoot 'Variable Not Defined' Errors

If your macro fails with a 'Variable not defined' message, check your VBA code for missing declarations or typos.

Free Microsoft Office alternative

Manage Your Data Effortlessly with WPS Office

While Access handles complex relational databases, WPS Spreadsheet provides a lightweight, highly compatible alternative for managing data tables, running macros, and analyzing records without the steep learning curve.

1. Download and Install: Visit the official WPS website to download the free suite and install it on your computer.
2. Open Your Data File: Launch WPS Spreadsheet and easily open your exported .xlsx or .csv data files.
3. Run Macros and Analyze: Enable the Developer tab to run your VBA macros and use PivotTables for quick, powerful data analysis.

Free and lightweight Microsoft Office alternativeSeamless compatibility with Microsoft Excel (.xlsx) formatsSupports advanced VBA macros for automated data workflowsFamiliar tabbed interface for quick user adoption

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Access VBA code say 'Sub or Function not defined' when using Lookup?

Microsoft Access VBA uses DLookup() to look up records, not Lookup(). Change your function call to DLookup() to resolve this compile error.

How do I read the value of a TempVar in Access?

You can read a TempVar in VBA by calling TempVars("YourVariableName").Value. To use it directly in queries or macros, wrap it in brackets, such as [TempVars]![YourVariableName].

Can I use RecordCount to get the result of my query?

No, RecordCount only returns the total number of records (rows) in a recordset. If you need a specific value from a field, you must use DLookup or open a Recordset and explicitly read the field's value.

Excel & Data Problems