You can lock all forms to AllowEdits = False, AllowAdditions = False, AllowDelete = False (in code or in the form properties)
I use the following code to detect the user. Then I lookup their preset user level in a table. All users under a certain level will have their edit access (like above) set to false.
Code:
Option Compare Database
Public PsUserNm As String 'These are public so the user kind of carries them around with them, like a badge, while they are in the DB as they enter any objects (forms, reports ...)
Public PbytUserLvl As Byte
Public Sub QuickUserNm() 'This sub is mainly for me in design mode as design mode often drops my public variable values - - - - - - -
PsUserNm = Environ("UserName")
End Sub
Public Function PublicVarSID()
Dim DBnm As String
Dim rs As Recordset
Dim sSQL As String
Dim vbANS As VbMsgBoxResult
PsUserNm = StrConv(Environ$("username"), 2)
Debug.Print
'******ALTERNATE DBA USER HIJACK*********************
If PsUserNm = "MyEnviron UserName" Then
vbANS = MsgBox("Do you want to mimic a user session?", vbYesNo, "SESSION TYPE")
If vbANS = vbYes Then
Set rs = CurrentDb.OpenRecordset("SELECT PC_UserNm " & _
"FROM AssignedRoles_t;")
Do While rs.EOF = False
sSQL = sSQL & vbCrLf & "-" & rs.Fields(0)
rs.MoveNext
Loop
PsUserNm = InputBox("Enter User SID." & sSQL, "DEFINE SESSION") 'This is so I can mimic another users Environ$ to see what they might be seeing.
sSQL = ""
End If
End If
'******END CODE FOR ALTERNATE DBA USER HIJACK*********************
'This is the user level lookup to my AssignedRoles_t.
If DCount("[UserLevel]", "AssignedRoles_t", "[PC_UserNm]='" & PsUserNm & "'") <> 0 Then
PbytUserLvl = DLookup("[UserLevel]", "AssignedRoles_t", "[PC_UserNm]='" & PsUserNm & "'")
Else: 'Exit DB is user is not in AssignedRoles_t - - - -
MsgBox "User Not Found. See Administrator.", vbOKOnly, "CLOSING DATABASE:"
Set rs = Nothing
DoCmd.Quit
Exit Function
End If
End Function
'------in any form or object, this or some variation to control objects visible or active Generally in the OnOpen Action
If PbytUserLvl = 5 Then
Me.(some button or field).Visible = True
Me.AllowEdits = True
Me.AllowDeletion = True
Me.AllowAdditions = True
'----by default these would be set to False in the form and turned only if User Level is > Level you choose
End If
'-------
AssignedRoles field names
Employee, |
First_Name, |
Middle_Initial, |
Last_Name, |
Supervisor, |
PC_UserNm, |
UserLevel |