Chapter 7. Exploring VBA in Microsoft Access

10.6 Adjust an Application Based on Who's Logged In

10.6.1 Problem

You've secured your database so that certain classes of users can't edit data using a particular form or run a specific report, but this doesn't prevent them from trying to open the form or report and receiving a permission error. You'd like your application to adjust itself based on the current user's security level. Is there any way to accomplish this?

10.6.2 Solution

Using VBA code, you can create a function that determines if the current user is a member of a security group. Based on the value this function returns, you can change any runtime property of any form or control, thus adapting your application to the user's security level.

Because this solution makes use of Access Security, you'll need to join the workgroup you created when you secured the database before you can try the sample database.

Now start Access. You will be prompted for a username and password. Enter the name of a user from Section 10.1.2's Table 10-1. With the exception of the Paul and Admin accounts, the passwords for these are blank. (The passwords for the built-in Admin account and the Paul account are both "password"; note that case is significant.)

Load 10-06.MDB and open the frmSwitchboard form. Depending on which user you logged in as, you will see either a Manager-, Programmer-, or Default-level form. For example, Manager-level users will see two Manager buttons and a Close button. In addition, a Close menu item will be included in the File menu. In contrast, a member of the Programmers group will see two Programmer buttons, no Close button, and no File Close menu item.

To implement this system in your own database, follow these steps:

1. Import the basGroupMember module into your database.

2. For each form you want to customize at runtime based on the user's group membership, attach an event procedure to the form's Open event that calls the acbAmMemberOfGroup function one or more times within an If...Then statement. Because users can be members of more than one group, you need to check for membership in the "highest"-level groups first, in decreasing order of security level.

3. Once you have determined the security level for the currently logged-in user, selectively hide and unhide controls on the form to suit your application's needs. You might also want to alter the caption of labels or other controls or customize other aspects of the form's look and feel. Finally, you can customize the menus for the form by changing the form's MenuBar property to point to different sets of menu macros. We have done all of this in the sample frmSwitchboard form. The runtime customizations to frmSwitchboard are summarized in Table 10-15.

    

   Table 10-15. Customizations made to frmSwitchboard

   Group	Visible buttons	lblMenu caption	File Close menu available?
   Managers	Manager #1Manager #2Close	Manager Main Menu	Yes
   Programmers	Programmer #1Programmer #2	Programmer Main Menu	No
   (Default)	Default #1Default #2	Default Main Menu	No

    

   The code that drives this customization process for frmSwitchboard is shown here:

   Private Sub Form_Open(Cancel As Integer)    ' Adapt switchboard to match level of logged-in user.        ' Because users can be members of more than one group,    ' you need to check membership in decreasing order,    ' starting with the highest-level group.    If acbAmMemberOfGroup("Managers") Then       With Me          !cmdManager1.Visible = True          !cmdManager2.Visible = True          !cmdProgrammer1.Visible = False          !cmdProgrammer2.Visible = False          !cmdDefault1.Visible = False          !cmdDefault2.Visible = False          !cmdClose.Visible = True          !lblMenu.Caption = "Manager Main Menu"       End With    ElseIf acbAmMemberOfGroup("Programmers") Then       With Me          !cmdManager1.Visible = False          !cmdManager2.Visible = False          !cmdProgrammer1.Visible = True          !cmdProgrammer2.Visible = True          !cmdDefault1.Visible = False          !cmdDefault2.Visible = False          !cmdClose.Visible = False          !lblMenu.Caption = "Programmer Main Menu"       End With    Else       With Me          !cmdManager1.Visible = False          !cmdManager2.Visible = False          !cmdProgrammer1.Visible = False          !cmdProgrammer2.Visible = False          !cmdDefault1.Visible = True          !cmdDefault2.Visible = True          !cmdClose.Visible = False          !lblMenu.Caption = "Default Main Menu"      End With    End If End Sub

10.6.3 Discussion

By default, the form is saved with the least-secure options set; if anything goes wrong, this provides a little extra assurance. When any user opens frmSwitchboard, the Load event procedure is called, and the form's look and feel is customized on the fly. Group membership is determined using the acbAmMemberOfGroup function found in basGroupMember:

Public Function acbAmMemberOfGroup(strGroup As String)           Dim wrk As DAO.Workspace    Dim usr As DAO.User    Dim strTest As String        Set wrk = DBEngine.Workspaces(0)        ' Refresh collections to stay in sync with Access UI.    wrk.Users.Refresh    wrk.Groups.Refresh        ' Set up pointer to current user.    Set usr = wrk.Users(CurrentUser(  ))        ' Handle errors in line.    On Error Resume Next    ' If any property of the Groups collection using the    ' passed-in group works, we're a member. Otherwise, an    ' error will occur and we can assume we are not a member.    strTest = usr.Groups(strGroup).Name    acbAmMemberOfGroup = (Err = 0) End Function

This function is simple: it determines if a user is a member of a group by setting a pointer to the Users collection of the current user and then attempting to get the name of the group in the Groups collection of that user. If this fails, the user is not a member of the group in question. If it succeeds, the user must be a member of the group. See Section 10.5.2 for more details on the programmatic manipulation of user and group collections.

We could have based the form customizations on the name of the current user using the built-in CurrentUser function, but this requires us to consider each user individually, which should be avoided if possible. It's much easier to manage groups of users rather than individual users. Still, you can always add more tests to the If...Then statement in the Load event procedure.

 

Versions of Access prior to Access 95 did not allow a user to check group membership unless the user was also a member of the Admins group, but the recent versions of Access allow this.

 

It's important that you include an Else clause in the If...Then statement of the Load event procedure to handle users who are not members of any of the groups you for which have tested. In the sample event procedure, we have tested for membership in only the Managers and Programmers groups. Any users who are not members of either group are handled by the Else clause.

You can use this technique to alter any runtime property in response to the user's group membership, including:

• Whether certain menu items appear

• Whether certain controls are visible, and therefore active

• What query a form is based on; some users can see more records than others

• What data entry controls are visible; some users can enter more fields than others

• What toolbars are shown