Filtering Data for different Users

Introduction.

In a network-based Microsoft Access application, multiple users are likely to update a shared master table simultaneously, either from different locations or categories of data. When a user logs into the application, they typically expect to see and work with only the records relevant to their assigned area, rather than navigating through the entire dataset.

To make this possible, it is best practice to store the User ID of the person who created or updated each record, along with a date-time stamp indicating when the change was made. This simple measure offers several long-term benefits.

For example, a user might report that they accidentally modified the wrong record but cannot recall exactly which one.  By using the stored User ID and timestamp, combined with an approximate date and time provided by the user, you can quickly filter or list matching records, helping them identify and correct the error.

I have discussed a more advanced scenario in my earlier article Who Changed the Data, which covers how to track and audit changes to records in detail. The approach we are focusing on here is less about rare troubleshooting cases and more about meeting an everyday operational need—ensuring that each user only works with records assigned to them. For this, having User IDs stored with each record is essential.

Limiting User Access to Records.

To restrict access so that each user sees only their relevant records, we can create a Select Query on the main table, using the User ID as the filter criterion. This query will serve as the source for data entry forms, editing forms, report preparation, and other operations.

In a multi-user network application, this query must be redefined dynamically each time a different user launches their instance of the application on their workstation. By doing so, the user will work only with records that belong to their assigned area, avoiding the overhead of loading and scrolling through the full dataset.

However, certain privileged groups—such as Administrators, Managers, or Supervisors—may require access to the entire dataset for oversight, reporting, or decision-making purposes. In such cases, the query must be redefined to lift the restrictions, providing full data access.

This approach addresses three key requirements:

1. Redefining the query immediately upon application launch. This ensures that the filtering is applied before the user can interact with any forms, reports, or other objects.

2. Identifying the User ID and associated Workgroup – This information determines the level of access to grant.

3. Applying the appropriate filter logic – For standard users, restrict to their User ID; for privileged groups, provide full access.

By combining User ID tracking with dynamic query redefinition, we can ensure both security and efficiency in multi-user Access applications.

Sample Data for Trial Runs.

Since we do not have a dedicated table containing actual User IDs, we will use the Orders table from the Northwind.mdb sample database for this example.

If you are unsure where to locate this database, refer to the article Saving Data on Forms Not in a Table for location details. Once located, import the Orders table into your project.

In the Orders table, the last field in the list is ShipCountry, which contains the country name for each order. For demonstration purposes, we will treat the ShipCountry value as our “User ID.” This will allow us to see the filtering logic in action.

In the accompanying VBA code, we will write the logic exactly as it would be for real User IDs and User Groups. The only difference is that for this example, we are substituting Country Names in place of actual IDs.

If you already have a secured database with a table containing real User IDs, simply replace the table and field names in the SQL string within the VBA code to adapt it to your system.

Changing Filter Query Definition through VBA.

The following VBA code runs from the Form_Unload event procedure of the Startup Screen form.

Its purpose is to:

1. Redefine the SQL of the query OrdersQ so that it filters records based on the current user and the user group membership.

2. Adjust the query definition before the user gains access to any data.

3. Close the Startup Screen and open the Control Screen (Main Screen) of the application.

Private Sub Form_Unload(Cancel As Integer)
Dim xsql As String, xsql0 As String, xfilter As String
Dim usrName As String, grpName As String
Dim i As Integer, j As Integer, usrFlag As Boolean
Dim wsp As Workspace, cdb As Database, QryDef As QueryDef

On Error GoTo Form_Unload_Err

xsql0 = "SELECT Orders.* FROM Orders "

xfilter = "WHERE (((Orders.ShipCountry)= '"

'enable this line with changes to Table and Field Names
'xfilter = "WHERE (((Table.UserID) = '"

usrName = CurrentUser

Set wsp = DBEngine.Workspaces(0)
i = wsp.Users(usrName).Groups.Count

If i = 1 Then ' he has only one Group that is USERS
    GoTo NextStep
End If

'check the User's Group status.
usrFlag = False
For j = 0 To i - 1
    grpName = wsp.Users(usrName).Groups(j).Name
    If usrFlag = False And (grpName = "MANAGERS" Or grpName = "SUPERVISORS" Or grpName = "Admins") Then
       usrFlag = True
       Exit For
    End If
Next

NextStep:

If usrFlag Then ' MANAGERS, SUPERVISORS or member of the Admins Group
    xsql = xsql0 & ";" 
        ' give full data access
Else
   ' xfilter = xfilter & usrName & "'));"
 'enable this line if Workgroups exists
    xfilter = xfilter & "USA" & "'));" 'try with different Country Names

    xsql = xsql0 & xfilter
End If

'change the Query definition
Set cdb = CurrentDb
Set QryDef = cdb.QueryDefs("OrdersQ")
   QryDef.SQL = xsql 
   cdb.QueryDefs.Refresh
   Set cdb = Nothing
   Set QryDef = Nothing

'Open the Main Control Screen
     DoCmd.OpenForm "Control", acNormal

Form_Unload_Exit:  
Exit Sub

Form_Unload_Err:  
MsgBox Err.Description, , "Form_Unload()"  
Resume Form_Unload_Exit
End Sub

Users and Workgroups

We assume that a particular User can belong to one of four groups:

1. Admins
2. MANAGERS
3. SUPERVISORS
4. Users

The Users group is the default workgroup for all users. However, a particular user can also belong to other groups, such as those listed in serial numbers 1 to 3 above, which grant special privileges.

If you run this code on a workstation where Microsoft Access security is not enabled, the default user ID will be Admin, a member of the Admins group. In this case, Microsoft Access will log you in automatically without prompting for a user ID or password. Note that you may not be able to open a secured database with this default Admin user ID because, in a properly secured Database, the Workgroup Administrator will have removed all privileges from the Admin account. Without doing so, the database cannot be considered fully secured. To regain access to the Admin account, you would need to reinstall Microsoft Access.

For more information about Microsoft Access workgroups, application security, and related topics, refer to the MS-Access & Security pages.

In the code example, we check the current user’s group membership. If the user belongs only to the Users group, the query will be redefined to filter records based on their user ID, so that they can access only their own data.

Users who are members of the MANAGERS, SUPERVISORS, or ADMINS groups will have unrestricted access to all data. All others will be treated as part of the Users group (or a similar group created by the Workgroup Administrator) and will only be able to access their own records.

Running the above VBA Code.

You can implement this code in the Form_Unload event procedure of your Startup Screen, or save it in a global module by renaming the subroutine to something like Public Function RedefineQuery().

Be sure to remove the Cancel As Integer parameter declaration if you move the code to a global module.

If stored in a global module, the function can be executed through an AutoExec macro. Create a macro named AutoExec, use the RunCode action, and specify the function name. This macro will run automatically when the database opens, instantly updating the query definition.

Check the Keyboard Shortcuts Page for AutoKeys Macro and its usage.

Earlier Post Link References:

• Progress Meter
• Keyboard Shortcuts
• Find or Filter Data on Form
• Who is Online
• Automated Email Alerts/