How to Remove an Access Form Filter Without Leaving the Current Record
Question details
The user needs to remove a filter on a parent Access form without the form resetting to the first record, ensuring the currently selected record remains active.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Navigating between records using an Access subform and parent form setup where the parent form is filtered.
- Observed behavior
- When the filter is removed from the main form, Access automatically resets the record pointer to the first record instead of keeping the active record in focus.
Ensure you have basic familiarity with Microsoft Access VBA and have created a backup of your database before modifying form events and properties.
Use RecordsetClone and Bookmark in the Subform's Current Event
This is the most seamless method as it navigates the parent form's record pointer directly to the matching record without applying a hard filter.
By utilizing the RecordsetClone property, you can search for a specific record in the background without affecting the form's display. Once found, applying the clone's bookmark to the parent form moves the focus to the correct record seamlessly.
Open your Access database, right-click your subform in the Navigation Pane, and select 'Design View'.
Open the Property Sheet (F4), navigate to the 'Event' tab, click on 'On Current', and click the builder button (...) to open the VBA editor.
Input code to use the parent's RecordsetClone to find the matching ID: `Me.Parent.RecordsetClone.FindFirst "AddressID = " & Nz(Me.AddressID, 0)`.
Add an If statement to check for matches: `If Not .RecordsetClone.NoMatch Then .Bookmark = .RecordsetClone.Bookmark`.
Save your VBA module, switch the parent form to Form View, and test the navigation to ensure the record stays active without filtering.
Close and Reopen the Form to the Desired Record
A simpler workaround that closes the parent form and immediately reopens it, targeted at the current record's primary key.
Try WPS Office for your everyday document and spreadsheet needs
While WPS Office does not include a database management tool like Microsoft Access, it is an excellent, lightweight, and free alternative for Microsoft Word, Excel, and PowerPoint. It's perfect for users looking to streamline their daily office tasks with high compatibility and a familiar interface.
- 1. Visit the official WPS website: Navigate to wps.com to access the latest version of WPS Office.
- 2. Download the software: Click the Free Download button to get the lightweight installation package for your operating system.
- 3. Install and launch: Run the installer and open WPS Office to seamlessly manage your Word, Excel, and PowerPoint files.
Frequently Asked Questions
Why does my Access form go to the first record when a filter is removed?
When you clear a filter in Access, the form's Recordset is entirely rebuilt and refreshed. By default, Access resets the record pointer to the first record in the newly unfiltered recordset, losing your previous position.
What is RecordsetClone in Microsoft Access?
RecordsetClone is a property that creates a separate, independent copy of a form's underlying records. It allows you to search or navigate through records using VBA without immediately affecting what is currently displayed on the form.
Can I use macros instead of VBA to maintain the current record without filtering?
While basic filtering can be managed with macros, locating a specific record using RecordsetClone and accurately assigning Bookmarks requires VBA code for precise control over the form's record pointer.
