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:
Here it is:
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:
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:
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:
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:
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.
Denis
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 FunctionYou 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 IfA 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 1Populate 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 SelectA 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 FunctionDenis
) I found I had missed the "Get Permission" code entirely. I added that and the listbox now populates :D
