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
Private Sub Form_Load
End Sub
If forms!frmMenu!txtLevel = 1 Then
Me.AllowEdits = False
Else
Me.AllowEdits = True
End If
This is a method that I have used successfully. 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: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
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:
With the code you copied.Code:Private Sub Form_Load End Sub
3. On each form that opens you need this in the Form_Load event:
DenisCode:If forms!frmMenu!txtLevel = 1 Then Me.AllowEdits = False Else Me.AllowEdits = True End If
Book1 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | MenuItemstable | |||||
2 | ||||||
3 | Field | DataType | Comment | |||
4 | Item | Text | Descriptivename | |||
5 | Form | Text | Objectname(realnameindb) | |||
6 | ObjectType | Text | FormorReport | |||
7 | SortOrder | Number | Usedtosetorderinlist | |||
Sheet1 |
RowSource for listbox (lstLaunch)
SELECT MenuItems.Item, MenuItems.Form, MenuItems.ObjectType FROM MenuItems ORDER BY MenuItems.SortOrder;
OnClick event for list box (lstLaunch)
Dim sForm As String, sType As String
sForm = Me.lstLaunch.Column(1)
sType = Me.lstLaunch.Column(2)
Select Case sType
Case "Form"
DoCmd.OpenForm sForm
Case "Report"
DoCmd.OpenReport sForm, acViewPreview
End Select