Tutorial -- An Access menu / permissions system

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 
Denis;
Thank you for posting this. It works great. I have one question, however. In step 3. you indicate that the code needs to be entered into every form that opens. I have approximately 4 sub forms that open within each primary form. I have entered the code in the primary forms. Does it also need to be entered in the subforms? Or, is entering in the primary forms sufficient.

As to Admin rights: For each maintenance form (updating lookup tables, etc.) These are accessed off a Menu Form. I would assume that the code with a txtlevel = 3 needs to be on the Menu Form. Does it need to be on each of the subsequent maintenance forms that open using command buttons?

Thanks much.
Alan
 
Upvote 0

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Denis;
Thank you for posting this. It works great. I have one question, however. In step 3. you indicate that the code needs to be entered into every form that opens. I have approximately 4 sub forms that open within each primary form. I have entered the code in the primary forms. Does it also need to be entered in the subforms? Or, is entering in the primary forms sufficient.

As to Admin rights: For each maintenance form (updating lookup tables, etc.) These are accessed off a Menu Form. I would assume that the code with a txtlevel = 3 needs to be on the Menu Form. Does it need to be on each of the subsequent maintenance forms that open using command buttons?

Thanks much.
Alan

Hi Alan,
Thanks fior the feedback! :)

For the subforms, test it out. You *should* be able to control the permissions with the settings on the main form, but I usually add it to all forms to be sure.

For the command buttons, I would toggle their visibility on the menu form by having something like this in the form's Current event:

Code:
cmdSomeButton.Visible = (txtLevel=3)

Repeat for the number of buttons that you want to control. the admin forms that come off them shouldn't need the code because users will only get to those forms if they have Admin privileges anyway.

Denis
 
Upvote 0
This is an EXCELLENT post!!! I was looking everywhere JUST to find this one...

Since I am still new on this, I just need clarification on the tables you have specified. My understanding is that I need to create two tables tblStaff and ???? Also, above said that tblStaff will have two fields: Login and Permission. I need to enter on each user name ie 1. "John", 2 "Mark" and on the Permission ie next to John is "Edit" and Mark is "Admin", see sample:
<TABLE style="WIDTH: 161px; HEIGHT: 68px" cellSpacing=0 bgColor=#ffffff border=1><CAPTION>tblStaff</CAPTION><THEAD><TR><TH borderColor=#000000 bgColor=#c0c0c0>ID</TH><TH borderColor=#000000 bgColor=#c0c0c0>Login</TH><TH borderColor=#000000 bgColor=#c0c0c0>Permission</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5>Mark</TD><TD borderColor=#d0d7e5>Admin</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>2</TD><TD borderColor=#d0d7e5>John</TD><TD borderColor=#d0d7e5>Edit</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

Is the above correct?

Also, on the main menu, frmMenu, do I need to refer txtUser for Login and txtLever for permission?

What is the SECOND table since the above is the First?

This would be greatly appreciated...! Please help...

hasyim
 
Upvote 0
Denis,

This is an EXCELLENT idea, allowing to use the database with read-only. Since I am new to vba, please clarify the two tables you have mentioned: tblStaff and MenuItems. Is the tblStaff containing the following?
<TABLE cellSpacing=0 bgColor=#ffffff border=1><CAPTION>tblStaff</CAPTION><THEAD><TR><TH borderColor=#000000 bgColor=#c0c0c0>ID</TH><TH borderColor=#000000 bgColor=#c0c0c0>Login</TH><TH borderColor=#000000 bgColor=#c0c0c0>Permission</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5>john</TD><TD borderColor=#d0d7e5>Admin</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>2</TD><TD borderColor=#d0d7e5>mark</TD><TD borderColor=#d0d7e5>Edit</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

Is the main form data coming from tblStaff?
Do you have a sample database for the above?
Please help and many thanks...
hasyim
 
Upvote 0
Hi Hasyim, Thanks for the comments! ;-)

To clarify,
Yes -- tblStaff is set up like you have it above. And the two textboxes txtUser and txtLevel, use the values from those fields once the user logs in.

The 'Other table' is the one that provides the items for the forms / reports that show in the list box. This page shows how the listbox is created and how you populate the records in the table. It leaves out the permissions field because I created it for a different purpose, but if you read through this tutorial again and then check out the page I mentioned, it should be a bit clearer.

Basically, you create permissions for your users based on their Windows login name. Those permissions are processed by the menu form, and the listbox then populates the menu items based on the permissions. The higher the level, the more items the user sees.

Also, the menu form is unbound -- no data source at all. It doesn't need a data source, the text boxes and the list box do the work.

Denis
 
Upvote 0
Hi Denis,

I tried to build a sample and simple database, just to understand it. I got an error message when launching frmMenu, Compile Error: Sub or Function not defined. The error is pointing it to: If IsNothing(Me.txtUser) Then...

Not sure what happened, would you like to see my sample db file?

Thanks,
hasyim
 
Upvote 0
Hi Sydney,

I try to use this code to set up some securities in my database but for some reason it is giving me thie error message;

RunTime error 250
Database can not find 'frmMenu' referred to in a macro or Visual Basic code


any ideas why???

Thanks
 
Upvote 0
Never mind i correct the error.

One more question:
I have a dashboars that opens a filtered main form, the restrictions work if i am in the main form but wheni used the dashboard to filter teh form based on user name the restriction do not work.

any ideas. Thanks
 
Upvote 0
Hi Sydneygeek,

I am working on the code you have mentioned above.

I am able to get my windows login populated in "txtuser" and my level is populated in "txtlevel". However, on loading frmMenu, it ask me to "Enter Parameter Value" of form!frmmenu!txtlevel.

Can you please advise? if you want I can send over my mdb file to you.

Thanks

Raj
 
Upvote 0
Hi Raj, if that is how you typed it, change
form!frmmenu!txtlevel
to
forms!frmmenu!txtlevel

The first word is a plural because it refers to the collection of open forms.

Denis
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top