I need to separate Users and admin on login

[koketsomaseko]

I need to separate Users and admin on login so that the admin can go to admin form and users to specific form using vba access
here is my login code

Private Sub cmdLogin_Click()
'Check to see if data is entered into the password box
If IsNull(Me.cboUserName) Or Me.cboUserName = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.cboUserName.SetFocus
Exit Sub
End If
'Check to see if data is entered into the password box

If IsNull(Me.PassWordText) Or Me.PassWordText = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.PassWordText.SetFocus
Exit Sub
End If

'Check value of password in tblEmployees to see if this
'matches value chosen in combo box

If Me.PassWordText.Value = DLookup("Password", "Login", _
"[EmployeeID]=" & Me.cboUserName.Value) Then

EmployeeID = Me.cboUserName.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogin", acSaveNo
DoCmd.OpenForm "Welcome"

ElseIf cboUserName = "koketso" And Password = "12345" Then
MsgBox "Welcome, admin", vbInformation, "Admin Area"
MsgBox "Please Exercise Caution When Altering Back End", vbInformation, "Manager Area"
DoCmd.Close
DoCmd.OpenForm "Admin"



Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.PassWordTex

 

[burrina]

My approach to this is to have a Admin command button on the Login form and only allow it to be unlocked if the user is an admin.
Something like this, of course edit to your needs.

'Enable/Disable Administrator Command Button.Same for Programmer.
If DLookup("[admn]", "tblUserSecurity_Sec", "[userID]='" & Me.txtUserID.Value & "'") = -1 Then
Me.cmdadmin.Enabled = True
Else
Me.cmdadmin.Enabled = False
End If

HTH

 

[pr2-eugin]

Hi koketsomaseko, first let me begin with a bit of grumble.

1. Please use Code tags when presenting VBA code.
2. Provide complete code, the last bit is missing.
​

Now regarding your issue,

1. I would prefer using the ListIndex property of the ComboBox (not a crime using the IsNull and NullString check, just a suggestion).
2. DLookup will throw a Null if nothing fits the criteria and using it against to compare a String is no good, try wrapping them with the Nz function.
3. Why assigning the EmployeeID variable a value?
4. your ComboBox seems to return a Number but you are comparing it against a Stirng in the ElseIf statement.
​

Now suggestion for further improvement.

1. Create a New field in the Login Table, call it accessLevel.
2. In the newly created field add in who is admin, who is user, who is manager etc.
3. Finally adapt your code as,
​

Private Sub cmdLogin_Click()
   [COLOR=Green] 'Check to see if data is entered into the password box[/COLOR]
    If Me.cboUserName.ListIndex = -1 Then
        MsgBox "You must select a User.", vbOKOnly, "Required Data"
        Me.cboUserName.SetFocus
        Exit Sub
    End If
    
    [COLOR=Green]'Check to see if data is entered into the password box[/COLOR]
    If IsNull(Me.PassWordText) Or Me.PassWordText = "" Then
        MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.PassWordText.SetFocus
        Exit Sub
    End If

    [COLOR=Green]'Check value of password in tblEmployees to see if this
    'matches value chosen in combo box[/COLOR]
    If Me.PassWordText = Nz(DLookup("Password", "Login", "[EmployeeID] = " & Me.cboUserName), "Garbage") Then
        Select Case Nx(DLookup("accessLevel", "Login", "[EmployeeID] = " & Me.cboUserName), "Garbage")
            Case "Admin"
                MsgBox "Welcome, admin", vbInformation, "Admin Area"
                MsgBox "Please Exercise Caution When Altering Back End", vbInformation, "Manager Area"
                DoCmd.Close acForm, "frmLogin"
                DoCmd.OpenForm "Admin"
            Case "Manager"
                'Close logon form and open splash screen
                DoCmd.Close acForm, "frmLogin", acSaveNo
                DoCmd.OpenForm "Welcome"
            Case "User"
                [COLOR=Green]'User Form Code[/COLOR]
            Case Else
                MsgBox "Unable to recogonize your Logon Credentials, please contact DB Admin", vbInformation
        End Select
    Else
        MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
        "Invalid Entry!"
    End If
End Sub

 

[pr2-eugin]

My approach to this is to have a Admin command button on the Login form and only allow it to be unlocked if the user is an admin.

Just curious on this idea. At what stage will the user be validated if he/she is an Admin? Also am I right in thinking that there is a need for a new column in the table?

 

[koketsomaseko]

[Private Sub cmdLogin_Click()
'Check to see if data is entered into the password box
If Me.cboUserName.ListIndex = -1 Then
MsgBox "You must select a User.", vbOKOnly, "Required Data"
Me.cboUserName.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box
If IsNull(Me.PassWordText) Or Me.PassWordText = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.PassWordText.SetFocus
Exit Sub
End If

'Check value of password in tblEmployees to see if this
'matches value chosen in combo box
If Me.PassWordText = Nz(DLookup("Password", "Login", "[EmployeeID] = " & Me.cboUserName.Value), "Garbage") Then
Select Case Nz(DLookup("accessLevel", "Login", "[EmployeeID] = " & Me.cboUserName.Value), "Garbage")


Case "Admin"
MsgBox "Welcome, admin", vbInformation, "Admin Area"
MsgBox "Please Exercise Caution When Altering Back End", vbInformation, "Manager Area"
DoCmd.Close acForm, "login"
DoCmd.OpenForm "Admin"
Case "Manager"
'Close logon form and open splash screen
DoCmd.Close acForm, "login", acSaveNo
DoCmd.OpenForm "Welcome"
Case "User"
'User Form Code
Case Else
MsgBox "Unable to recogonize your Logon Credentials, please contact DB Admin", vbInformation
End Select
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
End If
End Sub
[/CODE][/QUOTE]

thanks alot i managed to change it a bit but it worked

 

[burrina]

Paul, Here is the code answer. No need for another field. Anyway, he solved it, so no matter.

'Enable/Disable Administrator Command Button.Same for Programmer.
If DLookup("[admn]", "tblUserSecurity_Sec", "[userID]='" & Me.txtUserID.Value & "'") = -1 Then
Me.cmdadmin.Enabled = True
Else
Me.cmdadmin.Enabled = False
End If

 

[pr2-eugin]

thanks alot i managed to change it a bit but it worked

Glad you have it working.

Paul, Here is the code answer. No need for another field. Anyway, he solved it, so no matter.

It does not make sense ! At what point is this code executed? Button Click ? Control After Update? You say it does not need another field, so what field is admn? UserID is a String?

 

[burrina]

admn is a Yes/No field in the table. That code is executed using the AfterUpdateEvent of the password. Hope this clears it up. Anyway , since his thread is solved! My setup is different from his.

Thanks,

 

[pr2-eugin]

My setup is different from his.

Exactly my point. Good day !