How to Stop an Access VBA Recordset Loop for Filtered Queries
Question details
The user needs to stop an Access mail-merge macro loop from continuing after all records from a filtered query have been processed.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Running a VBA mail-merge macro that iterates through a filtered query recordset.
- Observed behavior
- The macro continues to count and loop through every record in the entire database instead of stopping when the filtered records are exhausted.
Make sure you have a backup of your current Access database or VBA module before modifying the macro code, so you can safely revert if necessary.
Use VBA Error Handling to Exit the Loop
Implement inline error handling to gracefully exit the loop when the VBA execution tries to process a record beyond the filtered dataset.
When a macro uses a loop that isn't strictly bounded by the end-of-file (EOF) marker of a filtered recordset, it might attempt to process rows that don't exist in the query, or fall back to the main table. By using 'On Error Resume Next' and checking the 'Err.Number', you can detect when the Execute statement fails and immediately break the loop.
Press 'ALT + F11' in Microsoft Access to open the Visual Basic for Applications editor and locate the module containing your mail-merge macro.
At the beginning of your routine, insert the statement 'On Error Resume Next' to prevent the macro from crashing when it hits an empty record.
Immediately after the 'Execute' statement inside your loop, add the condition: 'If Err.Number <> 0 Then Exit For'. Note: If you are using a Do loop, use 'Exit Do' instead.
Save the VBA module and run the macro again. The loop will now cleanly exit as soon as it exhausts the filtered query results.
Try WPS Office for Seamless Mail Merge and Document Creation
If you are struggling with complex VBA macros in Access for simple mail-merge tasks, consider switching to WPS Office. It provides a straightforward, user-friendly Mail Merge feature in Writer that seamlessly integrates with your spreadsheets, saving you from writing complicated code.
- 1. Open WPS Writer: Launch WPS Writer and navigate to the 'References' tab on the top ribbon.
- 2. Import Your Data Source: Click on 'Mail Merge', then select 'Open Data Source' to choose the spreadsheet containing your filtered data.
- 3. Insert Fields and Merge: Insert the required merge fields into your document and click 'Merge to New Document' to instantly generate your files.
Frequently Asked Questions
Why does RecordCount return incorrect values in an Access VBA Recordset?
By default, DAO and ADO Recordsets only load the first few records to save memory. To get an accurate RecordCount, you must move to the end of the recordset using 'Recordset.MoveLast' before checking the count.
Can I use EOF to stop an Access VBA recordset loop instead of error handling?
Yes, checking for End-Of-File (EOF) is the standard method for recordset iteration. Using a 'Do While Not Recordset.EOF' loop guarantees the process stops exactly when there are no more records left in your filtered query.
What does On Error Resume Next do in VBA?
The 'On Error Resume Next' statement instructs VBA to ignore run-time errors and proceed to the very next line of code. It allows developers to handle errors manually by checking 'Err.Number' immediately after a line that might fail.
