Micosoft Access Front-End Security. Part 3: Authentication at the individual control level.

On some of the forms in our collection management database I have buttons that I want everyone to be able to use, some I only want a few to be able to use, and a few I only want one or two people to be able to use. I control these buttons by using the computer’s user name.

First I added a field called “Level,” to the users table, tblUsers. I used four levels, with level 4 being the administrator level, with no restrictions on what that person can do. Level 1 is the most restricted, users at this level can do little more than search and read data.

In the On Click event of each button that is not a level 1 control I wrap the event procedure in the following If-Then-Else logic:

    Dim intUser As Integer                  ‘This variable holds the number for the user level.

    ‘Pick up the user level by matching the computer’s username with the user ID in tblUsers

    intUser = DLookup("[Level]", "tluUsers", "[UserID] = '" & Environ("username") & "'")

    ‘For a level 2 control you want to allow all level 2, 3, and 4 users, so the code allows

    ‘for users greater than level 1.

    ‘For a level 3 control you want to allow only level 3 and 4 users, so the code allows only

    ‘users greater than level 2.

    ‘A level 4 control has code to allow only users greater than level 3.         

    If intUser > 3 Then

        ‘This is where you would put the code for your event procedure.

    Else:

        MsgBox ("Sorry, you are not authorized to access this procedure.")

    End If

Bound text boxes can be protected by putting this If-Then-Else logic in the Got Focus procedure. If the user level is too low to allow updating the data then reset the focus to another control. In this example level 1 users are not allowed to edit the collection title, but are allowed to edit the donor name.

    If intUser < 2 Then

        MsgBox ("Sorry, you are not authorized to edit the Collection Title")

        Me.DonorsName.SetFocus

    End If

Next Week: Back-End Security.