Restriction User Edits based on selected Login.

[MikeEmerald]

Hello,



I was wondering if anyone could help me with my following code.
I currently have 3 user groups (Admin/User/Guest[read only]) and depending on who is logged in i want to be able to restrict what they can do.

Below is my login control:

Option Compare Database

Private Sub Command1_Click()
Dim User As String
Dim UserLevel As Integer
Dim TempPass As String
Dim ID As Integer
Dim UserName As String
Dim TempID As String

If IsNull(Me.txtUserName) Then
 MsgBox "Please enter User Name", vbInformation, "Username required"
 Me.txtUserName.SetFocus
ElseIf IsNull(Me.txtPassword) Then
 MsgBox "Please enter Password", vbInformation, "Password required"
 Me.txtPassword.SetFocus
Else
 If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin = '" & Me.txtUserName.Value & "' And UserPassword = '" & Me.txtPassword.Value & "'"))) Then
 MsgBox "Invalid Username or Password!"
 Else
 TempID = Me.txtUserName.Value
 UserName = DLookup("[UserName]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 UserLevel = DLookup("[UserType]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 TempPass = DLookup("[UserPassword]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 UserLogin = DLookup("[UserLogin]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 DoCmd.Close
 If (TempPass = "password") Then
 MsgBox "Please change Password", vbInformation, "New password required"
 DoCmd.OpenForm "frmUserinfo", , , "[UserLogin] = " & UserLogin
 Else
 'open different form according to user level
 If UserLevel = 1 Then ' for Admin
 DoCmd.OpenForm "Raw Material Database"
 Else
 If UserLevel = 2 Then ' for Users
 DoCmd.OpenForm "Raw Material Database"
 Else
If UserLevel = 3 Then ' for Guests
 End If
 End If
 End If
 End If
 End If
End If
End Sub
Private Sub cmdLogin_Click()
End Sub
Private Sub Form_Load()
Me.txtUserName.SetFocus
End Sub

This works fine however i want it so on my database form that edits are restricted to the Admin/User only so im trying to get the following code to work on my form.

Option Compare Database
Private Sub Form_Load()
Dim User As String
Dim UserLevel As Integer
Dim TempPass As String
Dim ID As Integer
Dim UserName As String
Dim TempID As String
If Forms!frmLogin!UserLevel = 1 Then
    Me.AllowEdits = True
    Me.AllowAdditions = True
    Me.AllowDeletes = True
Else
    Me.AllowEdits = False
    Me.AllowAdditions = False
    Me.AllowDeletes = False
End If
End Sub

However i am not sure how to sort that code so it works.

Any help would be highly appricated.

Thanks

[andy49]

Is the user level=1 line being recognised? It might be best to pass the variable to your new form using openargs.

https://msdn.microsoft.com/en-us/lib.../ff836583.aspx




Sent from my iPhone using Tapatalk

[davegri]

 If UserLevel = 1 Then ' for Admin
 DoCmd.OpenForm "Raw Material Database",,,,,,Userlevel
 Else
 If UserLevel = 2 Then ' for Users
 DoCmd.OpenForm "Raw Material Database",,,,,,UserLevel

Then in the second block of code replace IFs with this: (We don't know what you want to do differently with userlevels other than 1 except disable all of them)

Select case OpenArgs
    case  1  
        Me.AllowEdits = True
        Me.AllowAdditions = True
        Me.AllowDeletes = True
    case Else 'anything but 1
        Me.AllowEdits = False
        Me.AllowAdditions = False
        Me.AllowDeletes = False
End Select

And bye the way, it's good practice to follow all "Option Compare Database" at the top of all modules with "Option Explicit"

[MikeEmerald]

 If UserLevel = 1 Then ' for Admin
 DoCmd.OpenForm "Raw Material Database",,,,,,Userlevel
 Else
 If UserLevel = 2 Then ' for Users
 DoCmd.OpenForm "Raw Material Database",,,,,,UserLevel

Then in the second block of code replace IFs with this: (We don't know what you want to do differently with userlevels other than 1 except disable all of them)

Select case OpenArgs
    case  1  
        Me.AllowEdits = True
        Me.AllowAdditions = True
        Me.AllowDeletes = True
    case Else 'anything but 1
        Me.AllowEdits = False
        Me.AllowAdditions = False
        Me.AllowDeletes = False
End Select

And bye the way, it's good practice to follow all "Option Compare Database" at the top of all modules with "Option Explicit"

Dave, this worked brilliantly but one minor snaggle - I have a combo box with a drop down on the form that selects the record, allow edits has to be enabled currently to allow the "guest" (read only) account to select a record but this means he is able to make changes to the data. How can i get around this?

Thanks,

Mike

[andy49]

Do you mean lock the combo box so it is select from the list only?


Sent from my iPhone using Tapatalk

[davegri]

I have a combo box with a drop down on the form that selects the record

Let's call the form that contains block 1 and block 2 code frmMain.
What form is the combobox on? The frmMain, or another form? If another form, is that form opened via the frmMain code?
Can we assign security for all other forms by having frmMain open all other pertinent forms using OpenArgs? Or do we need to open forms needing security from places other than frmMain?
If the latter we need to have a global UserLevel, available in any code module, not just frmMain's code.

[davegri]

I did a little test and this seems to work for a combobox search.

Private Sub cboSearch_Enter()
    Me.AllowEdits = True
End Sub


Private Sub cboSearch_Exit(Cancel As Integer)
    Me.AllowEdits = False
End Sub