How to Loop Through Filtered Access Records with VBA
Question details
The user wants to loop continuously through filtered records in a Microsoft Access form, returning to the first record automatically when the last record is reached.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Navigating through filtered data records continuously in an Access form using a command button.
- Observed behavior
- The standard Next Record command stops at the last record instead of looping back to the beginning of the filtered dataset.
Ensure you have a working backup of your Access database before modifying form events, and verify that your form correctly applies the intended data filters.
Use VBA Error Handling to Reset Form Navigation
By utilizing simple error handling, you can detect when Access reaches the end of the filtered records and automatically trigger a command to return to the first record.
In Microsoft Access, attempting to navigate past the last record on a form generates a runtime error. By explicitly catching this error with VBA, we can redirect the form's focus back to the very first record, creating a seamless and continuous loop for the user.
Right-click your form in the Access navigation pane and select Design View to modify the form's layout and controls.
Click on the button you intend to use for the 'Next Record' action. Open the Property Sheet (F4), navigate to the Event tab, and find the 'On Click' property.
Click the ellipsis (...) next to the On Click property, select Code Builder, and enter the following logic: use 'On Error Resume Next', execute 'RunCommand acCmdRecordsGoToNext', and if an error occurs ('If Err Then'), execute 'RunCommand acCmdRecordsGoToFirst'.
Save your VBA module, switch the form back to Form View, apply your filter, and click the button repeatedly to ensure it cycles back to the start smoothly.
Manage Your Data and Documents with WPS Office
While Microsoft Access handles complex relational databases, WPS Office provides an exceptional, lightweight alternative for everyday data management, spreadsheet analysis, and document creation without the heavy subscription costs.
- 1. Download the Installer: Visit the official WPS Office website and download the free installation package for your operating system.
- 2. Install the Software: Run the installer and follow the on-screen prompts to set up WPS Office on your device in just a few minutes.
- 3. Import Your Data: Open WPS Spreadsheets and seamlessly open your existing .xlsx or .csv data exports from Access to continue your work.
Frequently Asked Questions
Can I use this VBA loop for unfiltered Access records as well?
Yes, the exact same VBA logic works perfectly for unfiltered records. The command will simply traverse the entire underlying table or query until the absolute last record is reached, and then loop back to the first.
Why does the Next Record command cause an error at the end of the form?
In Microsoft Access, attempting to navigate beyond the last available record in a bound form natively triggers a runtime error (usually Error 2105). Using the error handling code intercepts this so the application doesn't crash.
Can I loop through records using a Recordset instead of form commands?
Yes. For backend data processing, you can declare a DAO or ADO Recordset in VBA and use a 'Do While Not rs.EOF' loop combined with 'rs.MoveNext'. However, for front-end visual form navigation, the RunCommand approach is generally simpler and more efficient.
