How to Display Query Totals on an Access Form Without Opening Results
Question details
The user needs to display a calculated total on a Microsoft Access form based on a date input, without the query results datasheet popping up.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A user is updating a start date on a form and wants the form to recalculate and display a donation total without opening a separate query window.
- Observed behavior
- Running the query successfully calculates the results, but automatically opens the query datasheet table, interrupting the form view.
Ensure your Microsoft Access database is open, your form is accessible in Design View, and you have identified the specific text box (such as the start date field) that should trigger the calculation.
Use a Calculated Control and an After Update Event
Place the calculation directly within a form control and use a simple VBA macro to automatically recalculate the form when the input data changes.
Instead of triggering a standard query that opens a separate datasheet window, you can rely on form calculations combined with the Me.Recalc VBA command. This ensures the user interface remains seamless and the total updates in real-time.
Right-click your form in the Access navigation pane and select 'Design View' from the context menu.
Add a text box control to your form where the total should appear. Set its 'Control Source' property to your calculation formula (for example, using a DSum function).
Click on the input text box (e.g., 'StartDate'). Open the Property Sheet, navigate to the 'Event' tab, and click the ellipsis (...) next to the 'After Update' property to open the VBA builder.
In the VBA editor, type 'Me.Recalc' between the generated Private Sub and End Sub lines. It should look like this: Private Sub StartDate_AfterUpdate() \n Me.Recalc \n End Sub.
Save the VBA module and the form. Switch back to Form View. When you change the start date, the total will now update instantly on the form without displaying a query table.
Looking for a Lightweight, Free Alternative for Your Office Tasks?
While Microsoft Access handles databases, WPS Office is an exceptional, free alternative for all your document, spreadsheet, and presentation needs, offering seamless compatibility with Word, Excel, and PowerPoint.
- 1. Download WPS Office: Visit the official WPS website to download the free office suite for your operating system.
- 2. Install and Launch: Run the installer and follow the quick setup instructions. Open the application in seconds.
- 3. Open Your Office Files: Easily open, edit, and save your existing Microsoft Word, Excel, and PowerPoint files with zero formatting loss.
Frequently Asked Questions
Why does my Access query open in a new datasheet window?
By default, running a Select Query using an OpenQuery macro or a command button will display the results in Datasheet view. To prevent this, use Domain Aggregate functions like DSum directly in form controls instead of running the query object.
What does the Me.Recalc command do in Access VBA?
Me.Recalc forces the active form to recalculate all of its calculated controls immediately. This is highly useful when you want to update totals or formulas dynamically right after a user changes an input parameter, such as a date.
Can I hide the query results window using a Macro instead of VBA code?
If you are running an action query (like Append or Make Table), you can use the SetWarnings macro action to suppress pop-ups. However, Select queries will always display a datasheet when executed. The best workaround for displaying Select query data is to bind it to a form control.
