Prevent Clear All Filters from Exposing Records in Microsoft Access 2016
Question details
The user needs Access 2016 forms to display only the signed-in user's records, even when the user selects Clear All Filters on the Home ribbon.
- Product
- Microsoft Access 2016
- Device & OS
- not provided
- Scenario
- A multiuser database uses a login form to identify users and limit the records displayed in other forms.
- Observed behavior
- Users can select Clear All Filters on the Home ribbon, so ordinary form filters are not sufficient to keep other users' records out of view.
Identify the field that associates each record with a user and the login-form control that stores the authenticated user's ID. The example below uses frmLogin, txtUserID, tblRecords, and UserID; replace these names with your actual objects.
Restrict each form's RecordSource with a login-based parameter query
Use this approach to ensure that clearing a form filter does not expand the form's underlying record set to include other users' records.
A form filter narrows records after the form's RecordSource supplies them. Placing the user condition in the RecordSource query instead means Clear All Filters removes only additional form filtering, not the query's user condition.
After successful authentication, populate frmLogin's txtUserID control with the authenticated user's ID. Hide the login form rather than closing it; in the login form's VBA procedure, use Me.Visible = False. The form must remain open while dependent queries run.
Select Create > Query Design, add tblRecords, and include the fields the form needs. Add UserID to the query grid and enter [Forms]![frmLogin]![txtUserID] in its Criteria row. This condition belongs in the query, not in the form's Filter property.
Save the query as qryCurrentUserRecords. With frmLogin still open and txtUserID populated, open the query from the Navigation Pane and confirm that it returns only records whose UserID matches the signed-in user. If Access prompts for the parameter, check the form and control names and whether the login form is open.
Open a user-specific form in Design View, press F4 to open the Property Sheet, and select Form. On the Data tab, set Record Source to qryCurrentUserRecords, then save the form. For forms that need different tables or fields, create equivalent queries with the same user condition. Review subform RecordSource settings as well.
Open the form, apply an additional filter, and select Home > Sort & Filter > Advanced > Clear All Filters. Verify that only the signed-in user's records remain. After changing the authenticated user, close and reopen dependent forms or call their Requery method so they load the new user's record set.
Use WPS Office for Documents and Spreadsheet Reports Alongside Access
WPS Office does not fix Access form filtering or replace Access database functionality. It offers a free, lightweight, easy-to-use alternative for related document and spreadsheet work, with a familiar interface and Microsoft Office format compatibility for a smooth migration of those files.
Frequently Asked Questions
Why does Clear All Filters expose records when I use a form filter?
A form filter only limits what is displayed from the form's RecordSource. Home > Sort & Filter > Advanced > Clear All Filters removes that display restriction. Put the user condition in the query assigned under Property Sheet > Data > Record Source so the underlying result set remains user-specific.
Does the login form need to stay visible?
No. It must stay open so the query can resolve [Forms]![frmLogin]![txtUserID], but it can be hidden. After authenticating the user and setting the control value, use Me.Visible = False in the login form's VBA procedure instead of closing the form.
Why does Access ask me to enter a parameter value?
Access may be unable to resolve the form-control reference. Open frmLogin, confirm that txtUserID contains the authenticated user's ID, and check the query's Criteria row for exact form and control names. Also verify that the ID value and the table's UserID field use compatible data types.
Does a parameter query provide database-level row security?
No. It limits the records returned to forms that use that query, but it does not prevent direct table access or changes to the query. Use backend authorization when users must be prevented from retrieving other users' data through any access path.
Can WPS Office open this Access database and change its forms?
WPS Office is not an Access database editor and cannot modify Access form RecordSource settings. Make those changes in Microsoft Access. For spreadsheet reporting, export authorized results from Access through External Data > Excel, then open the exported file in WPS Spreadsheet.
