Access Cookbook

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 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. ...