Access Cookbook - Ken Getz [215]
In the list of users found in tblUsers, notice that there are two users that you might not have seen before: Creator and Engine. These two users are created by the Jet engine itself and cannot be used or manipulated by VBA code. As you'll see in the Solution in Recipe 10.7, you can create a Workspace object for any normal user, allowing that user to log into a new session of the Jet engine, but you can't use Creator or Engine to create new workspace objects. It's a good thing, too! Since neither can have a password (their passwords are always blank), this would otherwise provide a security breach. Because you can neither log on manually nor log on using the CreateWorkspace method with either user, these two special users don't pose a security risk.
Once you know how to enumerate through collections, as shown in this solution, you should be able to apply the same techniques to other database collections and their objects. For more information, see Chapter 4.
10.6. Adjust an Application Based on Who's Logged In
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?
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 the Solution in Recipe 10.1s 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:
Import the basGroupMember module into your database.
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.
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
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