Filtering Data for different Users

Introduction.

In a Network-based MS-Access Application several Users may involve updating information into a common Master Table from different Locations or for different categories. When a particular User Logs in to the Application he expects to see or work with the records that belong to his area only rather than struggling with the full size of the Table.

It is always a good idea to save the User IDs of the Users and Data Entry/Editing Date and Time (Time Stamp) into the records when they are added or updated. This can help in so many ways when needed later.

For example, a User may come up and say he made some changes to a wrong record but he doesn't know which one. He needs to find and correct the record, but he doesn't know how to find it. With approximate date and time from him and with the aid of the User ID and Time Stamp, we can prepare a listing of records or filter records and this may help him to find the record for corrections.

Please see my earlier Article on Who changed the Data, where we are dealing with a more serious issue and explains how to update User IDs and Time Stamp into Records.

These kinds of situations may not occur every day, but what we are now going to look at is an everyday requirement and we need the User IDs saved into the records for this exercise.

Limiting User Access to Records

For limiting access to the records that belong to a particular User we need to create a Select Query on the main table using the User ID as criteria. This Query must be re-defined each time when different User(s) opens his Instance of the same Application on their respective Workstations. For all practical purposes, this main Query will be used as a Source for the Data Entry/Editing Forms, Report preparations, and so on. If you have a main Table with a large amount of data this method will reduce the volume of records that a particular user works with.

But there may be other Users who belong to a more privileged Group like Admins Group or Managers Group or Supervisors Group that need full data for viewing or for Report preparations. In all these situations we have to redefine the Query as the situation demands.

Here, we need to look at three things:

1. The Query must be redefined immediately after the User opens his Instance of the Application before he is able to do anything else with it.
2. Find the User ID and Workgroup, he belongs to.
3. Redefine the main Query based on the User ID and the Workgroup Status

Sample Data for Trial Runs

Since we don't have a ready-made Table with User IDs recorded on them, we will use the Orders Table from the NorthWind.mdb sample Database. If you don't know where to find this Database, please visit the Page Saving Data on Forms not in Table for its location references and Import the table Orders into your Project.

The Orders table has a country name, and field (ShipCountry) at the end of the field list and we will take the value from this field as User IDs for our example. We will deal with the User IDs and User Groups in the VBA code as they should be and to see the data filtering action we will use the Country Names instead of User IDs. If you already have a Table with User IDs in a Secured Database then replace the Table and Field Names in the SQL string in the VBA Code.

Changing Filter Query Definition through VBA

The Code below is run from the Startup Screen's Form_Unload Event Procedure. The Query Definition of OrdersQ is changed depending on the Current User and his Group status and then closes the Start-up Screen and opens the Control Screen (Main Screen).

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

Users Group is the default Workgroup for all Users and a particular User can also belong to other groups like serial numbers 1 to 3 above qualifying him for special privileges.

If you try this Code in a Workstation where MS-Access Security is not implemented the default User ID will be Admin, a member of the Admins Group and you are automatically logged in by MS-Access without asking for your User ID or Password. You may not be able to open a Secured database with this UserID because all privileges to the Admin User will be removed by the Workgroup Administrator, otherwise, you cannot call it a fully Secured Database and you can open such a Database after re-installing MS-Access.

Go through the MS-Access & Security Pages to learn more about Microsoft Access Workgroups and methods for securing MS-Access Applications and related issues.

We are checking the Current User's Group Status, in the Code. If he belongs to only the Users Group then re-define the Query with his UserId to filter and provide the records belonging to him.

If the User belongs to the MANAGERS or SUPERVISORS or ADMINS GROUP then he will get full data access, all others will be treated as part of the common Users Group or similar Groups created by the Workgroup Administrator. In either case, he will be provided with the records that belong to him.

Running of the above VBA Code

You can implement this Code on your Startup Screen's Form Unload Event Procedure or save it in a Global Module with the change on the Subroutine name line to something else like Public Function RedefineQuery()

Don't forget to remove the Cancel as Integer parameter declaration.

If you have saved the Code in a Global Module with the above Change you can run the code from within an Autoexec Macro. Call the Function from the Macro using the RunCode Action and name the Macro as Autoexec. The Macro runs automatically when the user opens the database and the Query definition changes instantly.

Check 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/