Create a "Permissions" attribute (i.e. column/field) where ever you have usernames and passwords saved for users.
Create standard permission levels like admin, manager, user, guest, etc.
I would then create a lookup table (tlkpPermissionLevel) containing two attributes "Permissions" and "ID" with the primary key set to the permissions attribute. This is useful if you have a form where you enter user information, such as first name, last name, username and password, and so forth, but may also set persmissions there as well by showing only permissible options in a combo box drop down list by querying the permissions table. If not using a form for adding username, password, and permissions then disregard this step. Example of form query for permissible permissions where red is the name of combo box drop down list and blue is name of attribute in the tlkpPermissionLevel table:
Private Sub txtPermissionLevel_GotFocus()
Dim strSQL As String
strSQL = "SELECT PermissionName FROM tlkpPermissionLevel ORDER BY ID "
CurrentDb.OpenRecordset strSQL
Me![txtPermissionLevel].RowSource = strSQL
Me![txtPermissionLevel].Requery
End Sub
On each form containing permission dependent functionality simply call on the permissions level then set (blue) what each user based on their permission level is permitted to perform.
Private Sub Form_Load()
On Error Resume Next
Dim Permission As String
Permission = [Forms]![frmLogin]![txtPermissions].Value
If Permission = "Admin" Then
'code whatever you want to happen
.AllowAdditions = True
.AllowEdits = True
.AllowDeletions = True
ElseIf Permission = "Manager" Then
'code whatever you want to happen
.AllowAdditions = False
.AllowEdits = True
.AllowDeletions = False
ElseIf Permission = "User" Then
'code whatever you want to happen
.AllowAdditions = False
.AllowEdits = False
.AllowDeletions = False
ElseIf Nz(Permission, "") = "" Or Nz(Permission, "") = 0 Then
'code whatever you want to happen
ElseIf Permission <> "Admin" Or Permission <> "Manager" Or Permission <> "User" Then
'code whatever you want to happen
End If
DoCmd.GoToRecord , , acNewRec
End Sub
The last two permissions are more for handling some issue in case the permissions field for users are blank or something else that is not approved.
From there you should create a hidden space on your login form that retrieves the permissions level if successfully logged in. This way when ever you call the permissions level check on your forms to allow or disallow certain features it does it from the frontend (user interface) rather than bogging down the network each time some permission needs checking. All permissions checks for permissible features should call on the login form text field holding the permissions value. Just be sure NOT to program in a close (i.e. DoCmd.Close) for the login form otherwise permission level will not be able to evaluate. It might be useful to call any fields you need to filter results of forms onto the login form but hide the text boxes by selecting each one and Property Sheet > Format > Visible = No and Property Sheet > Data > Locked = Yes. This way all lookup permission fields either user id, username, permissions, etcetera can be done.
Just drop this on your Login form VBA, where red is the name of text box set in Property Sheet > Other > Name:
Me.txtPermissions.Value = DLookup("Permissions", "tblStaff", "[UserName] = '" & Me.txtUserName & "'")
For example you may want to filter by departments as well to limit who can access certain types of records and to do that repeat the aforementioned and add an invisible text field on the login VBA like:
Me.txtDepartment.Value = DLookup("Department", "tblStaff", "[UserName] = '" & Me.txtUserName & "'")