Tutorial -- An Access menu / permissions system

**SydneyGeek** · Feb 18th, 2007, 05:49 AM

This is a post to put in one place an approach I use for giving different access levels to different groups of users in your Access databases. It also provides a flexible menu that adjusts to the user's permissions. It does not require the users to log in: their Windows login name is captured when they open the database. It is easy to restrict the forms. reports etc that they can use, simply by maintaining two tables; one for access levels, and one for menu items.
Parts of this have appeared on this Board before: parts are new. I hope you find it useful.

The login / menu form

You will need:

1. A table called tblStaff, with at least the fields Login and Permissions. Both are Text fields.
(a) In Login, enter the Windows login name of each user who will have ability to add or change data. Those whose names do not appear here will have read-only access to the database.
(b) In Permissions, give them values Edit or Admin

2. A form that opens as your main menu. In this example the form is frmMenu. On this form you need 2 hidden textboxes called txtUser and txtLevel.
In the Form's Load event you will need this code:

If you don't know how to do this, here's how:
Go to the form's Properties.
Click the Events tab.
Double-click the blank Load row, so you see Event Procedure.
Click the Builder (...) button to go to the code window.
Paste this code in, replacing this stuff:

Code:

Private Sub Form_Load

End Sub
With the code you copied.

Here it is:

Code:

Private Sub Form_Load()
    Dim sPermit As String
    Dim iAccess As Integer
    Dim Ctl As Access.Control
    Me.txtUser = Environ("username")
    If IsNothing(Me.txtUser) Then
        sPermit = "ReadOnly"
    Else
        sPermit = GetPermission(Me.txtUser)
    End If
    Select Case sPermit
        Case "Edit"
            iAccess = 2
        Case "Admin"
            iAccess = 3
        Case Else
            iAccess = 1
    End Select
    Me.txtLevel = iAccess
End Sub

Private Function GetPermission(sUser As String)
    If (IsNothing(DLookup("Permissions", "tblStaff", "Login='" & Forms!frmmenu!txtUser & "'"))) Then
        GetPermission = "ReadOnly"
    Else
        GetPermission = DLookup("Permissions", "tblStaff", "Login='" & Forms!frmmenu!txtUser & "'")
    End If
End Function

You give the lowest number to the lowest level of access. So, 1 is for general users whose names do not appear in the login table. They will have read-only access to forms. 2 is for those with more permissions. They will be able to add and edit data. 3 is for those with Admin privileges. They will be able to change user settings and make other behind-the-scenes modifications.

3. On each form that opens you need this in the Form_Load event:

Code:

If forms!frmMenu!txtLevel = 1 Then
   Me.AllowEdits = False
Else
   Me.AllowEdits = True
End If

A listbox to provide the menu items

This listbox will give the users the menu choices they need to navigate through the database. It is based on a table called MenuItems, with these fields:

Code:

Item        Text     The descriptive text that appears in the listbox
Level       Number   1, 2 or 3
Form        Text     The name of the form or report to open
ObjectType  Text     Form or Report
SortOrder   Number   Adjust to suit. List is sorted in ascending order.

The listbox itself is called lstMenu. It lives on the menu form that you created in the first part of this tutorial. It has these properties:

Code:

Row Source Type      Table/Query
Row Source           SELECT MenuItems.Item, MenuItems.Form, MenuItems.ObjectType FROM MenuItems WHERE (((MenuItems.Level)<=Forms!frmmenu!txtLevel)) ORDER BY MenuItems.SortOrder
Column Count         5
Column Widths        3;0;0;0;0
Bound Column         1

Populate the table and build the listbox. Check to see that it works OK. You can do that by giving yourself different levels of access in the login table, closing and re-opening the menu form and checking that the menu adjusts. Now, to make it launch the forms and reports, you will need this code in the Click event of the listbox:

Code:

    Dim sForm As String, sType As String
    sForm = Me.lstMenu.Column(1)
    sType = Me.lstMenu.Column(2)
    Select Case sType
        Case "Form"
            DoCmd.OpenForm sForm
        Case "Report"
            DoCmd.OpenReport sForm, acViewPreview
    End Select

A general function to test for "nothing"
This function below needs to go in a general code module. You will find yourself using it for much more than just the login form.

Code:

Public Function IsNothing(ByVal varValueToTest) As Integer
'-----------------------------------------------------------
' Does a "nothing" test based on data type.
'   Null = nothing
'   Empty = nothing
'   Number = 0 is nothing
'   String = "" is nothing
'   Date/Time is never nothing
' Inputs: A value to test for logical "nothing"
' Outputs: True = value passed is a logical "nothing", False = it ain't
' Created By: John L viescas 01/31/95
' Last Revised: John L viescas 01/31/95
'-----------------------------------------------------------
Dim intSuccess As Integer

    On Error GoTo IsNothing_Err
    IsNothing = True

    Select Case VarType(varValueToTest)
        Case 0      ' Empty
            GoTo IsNothing_Exit
        Case 1      ' Null
            GoTo IsNothing_Exit
        Case 2, 3, 4, 5, 6  ' Integer, Long, Single, Double, Currency
            If varValueToTest <> 0 Then IsNothing = False
        Case 7      ' Date / Time
            IsNothing = False
        Case 8      ' String
            If (Len(varValueToTest) <> 0 And varValueToTest <> " ") Then IsNothing = False
    End Select


IsNothing_Exit:
    On Error GoTo 0
    Exit Function

IsNothing_Err:
    IsNothing = True
    Resume IsNothing_Exit

End Function

Denis

**StuLux** · May 2nd, 2007, 05:48 AM

Denis

Excellent code which I am trying to use although I am still very much a VBA beginner. I have the following problem:

When I first open the frmMenu no items show in in the listbox but if I edit the form design (even if I don't actually change anything) and then reshow the form the list box is populated?? If I close the form and reopen it the list box is empty again.

This happens everytime even if I shut the database down and reopen it - do you have any idea what might be happening?

**SydneyGeek** · May 2nd, 2007, 05:57 AM

Not without looking at how you set it up. PM me and I'll provide an email address, then I'll check out the file and report back via this thread.

Denis

**Sir Phoenix** · May 2nd, 2007, 10:00 AM

This is an awesome post. Thanks a lot!

**SydneyGeek** · May 4th, 2007, 06:55 AM

Hi Stulux,

Thanks for sending the database through. I found the problem. Although my original database didn't require it, yours was waiting for a command to requery the listbox. Here is the revised code for the menu form's Load event, with the extra command:

Code:

Private Sub Form_Load()
    Dim sPermit As String
    Dim iAccess As Integer
    Dim Ctl As Access.Control
    Me.txtUser = Environ("username")
    If IsNothing(Me.txtUser) Then
        sPermit = "ReadOnly"
    Else
        sPermit = GetPermission(Me.txtUser)
    End If
    Select Case sPermit
        Case "Edit"
            iAccess = 2
        Case "Admin"
            iAccess = 3
        Case Else
            iAccess = 1
    End Select
    Me.txtLevel = iAccess
    Me.lstMenu.Requery
End Sub

By the way: Are you using Access 2007? I tested your DB in 2007 and could replicate the problem. XP didn't have the issue.

Denis

**StuLux** · May 8th, 2007, 05:56 AM

Denis

Many thanks, this certainly seems to fix it. I am using 2003 on XP so it's strange that you could only replicate it in 2007!

Stuart

**rmtimmah** · Feb 5th, 2008, 06:50 PM

SydneyGeek I am trying to get this to work for me but when I run the Menu Form I get an error in VBA for the line:

If IsNothing(Me.txtUser) Then

saying that the "sub or function is not defined".

I looked on help and IsNothing doesn't exist. Please help!!

**SydneyGeek** · Feb 5th, 2008, 09:13 PM

Did you import the code for the IsNothing function? You will need to put it in a code module. -- Last code item in the post at the top of this thread.

Denis

**rmtimmah** · Feb 5th, 2008, 09:29 PM

Yep did that and it works well now. Only thing is I closed the database to test it and when I re-open it now it crashes Access. The only way I can open it now is to have Access open and VBA open before opening the file otherwise it crashes. Any suggestions?