Page 1 of 8 123 ... LastLast
Results 1 to 10 of 74

Tutorial -- An Access menu / permissions system

This is a discussion on Tutorial -- An Access menu / permissions system within the Microsoft Access forums, part of the Question Forums category; This is a post to put in one place an approach I use for giving different access levels to different ...

  1. #1
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,115

    Default Tutorial -- An Access menu / permissions system

    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
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  2. #2
    Board Regular StuLux's Avatar
    Join Date
    Sep 2005
    Location
    Cardiff, UK
    Posts
    578

    Default

    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?
    Stuart
    "It's 5 o'clock somewhere"

    XP Pro (SP 3)/Excel 2007 (work) and Windows 8/Excel 2013 (home)

  3. #3
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,115

    Default

    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
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  4. #4
    Board Regular
    Join Date
    Aug 2006
    Location
    Orange Park, FL
    Posts
    146

    Default

    This is an awesome post. Thanks a lot!

  5. #5
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,115

    Default

    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
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  6. #6
    Board Regular StuLux's Avatar
    Join Date
    Sep 2005
    Location
    Cardiff, UK
    Posts
    578

    Default

    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
    Stuart
    "It's 5 o'clock somewhere"

    XP Pro (SP 3)/Excel 2007 (work) and Windows 8/Excel 2013 (home)

  7. #7
    Board Regular
    Join Date
    May 2007
    Posts
    153

    Default Re: Tutorial -- An Access menu / permissions system

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

  8. #8
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,115

    Default Re: Tutorial -- An Access menu / permissions system

    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
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  9. #9
    Board Regular
    Join Date
    May 2007
    Posts
    153

    Default Re: Tutorial -- An Access menu / permissions system

    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?

  10. #10
    Board Regular
    Join Date
    May 2007
    Posts
    153

    Default Re: Tutorial -- An Access menu / permissions system

    Never mind I took out the "Me.lstMenu.Requery" and it works now.

Page 1 of 8 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com