User login form with different levels of access

[trentgaming]

Can someone help me with my login form.
when I am trying to login with level 2 user nothing happens but in level 1 user it is all good here is the code:

Option Compare Database

Private Sub btnLogin_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 UserName", 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("[UserSecurity]", "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
DoCmd.Close
'open different form according to user level
If UserLevel = 1 Then ' for admin
DoCmd.OpenForm "Main Form"
ElseIf UserLevel = 2 Then ' for user
DoCmd.OpenForm "Navigation Form"
End If
End If
End If
End If
End Sub
Private Sub Form_Load()
Me.txtUserName.SetFocus
End Sub

 

[Gasman]

Please put all that within code tags and indent?

That might even show you the problem.?

 

[Minty]

Tidying this up and indenting it will as @Gasman suggested will show you the problem;

Private Sub btnLogin_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) Or IsNull(Me.txtPassword) Then
        MsgBox "Please enter UserName", vbInformation, "Username required"
        Me.txtUserName.SetFocus
        Exit Sub
    End If
      
    If IsNull(Me.txtPassword) Then
        MsgBox "Please enter Password", vbInformation, "Password required"
        Me.txtPassword.SetFocus
        Exit Sub
    End If
  
    If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin = '" & Me.txtUserName & "' And UserPassword = '" & Me.txtPassword & "'"))) Then
        MsgBox "Invalid Username or Password!"
      
    Else
        TempID = Me.txtUserName
        UserName = DLookup("[UserName]", "tblUser", "[UserLogin] = '" & TempID & "'")
        UserLevel = DLookup("[UserSecurity]", "tblUser", "[UserLogin] = '" & TempID & "'")
        TempPass = DLookup("[UserPassword]", "tblUser", "[UserLogin] = '" & TempID & "'")
        UserLogin = DLookup("[UserLogin]", "tblUser", "[UserLogin] = '" & TempID & "'")
        'DoCmd.Close
        If (TempPass = "password") Then
            DoCmd.Close
            'open different form according to user level
            If UserLevel = 1 Then ' for admin
                DoCmd.OpenForm "Main Form"
            ElseIf UserLevel = 2 Then ' for user
                DoCmd.OpenForm "Navigation Form"
            End If
        End If
    End If
End Sub

Your UserLevel statement never gets reached.

 

[Gasman]

Thanks @Minty
However that indicates to me that nether userlevel will work and the o/p stated that UserLevel = 1 is fine.

 

[Minty]

Thanks @Minty
However that indicates to me that nether userlevel will work and the o/p stated that UserLevel = 1 is fine.

I agree - in fact I wouldn't process it this way, I actively dislike if elseif constructs as they are way to easy to get lost in.

Private Sub btnLogin_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) Or IsNull(Me.txtPassword) Then
        MsgBox "Please enter UserName And Password", vbInformation, "Missing Details!"
        Me.txtUserName.SetFocus
        Exit Sub
    End If
        
    UserLevel = 2 ' Default '
        
    If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin = '" & Me.txtUserName & "' And UserPassword = '" & Me.txtPassword & "'"))) Then
        MsgBox "Invalid Username or Password!"
        Exit Sub
    End If
    
    TempID = Me.txtUserName
    UserName = DLookup("[UserName]", "tblUser", "[UserLogin] = '" & TempID & "'")
    UserLevel = DLookup("[UserSecurity]", "tblUser", "[UserLogin] = '" & TempID & "'")
    TempPass = DLookup("[UserPassword]", "tblUser", "[UserLogin] = '" & TempID & "'")
    UserLogin = DLookup("[UserLogin]", "tblUser", "[UserLogin] = '" & TempID & "'")
    
    If (TempPass = "password") Then
        DoCmd.Close         ' This closes the form, shouldn't it start the password reset option?'
    End If
        
    'open different form according to user level
    Select Case UserLevel
        Case 1                      ' for admin
        DoCmd.OpenForm "Main Form"
     Case Else                      ' for user
        DoCmd.OpenForm "Navigation Form"
    End Select

End Sub

 

[jdraw]

You may get some ideas from this Steve Bishop video on Managing User Access.

 

[Deleted member 147267]

It has been said here recently that form level code will run after Close method is invoked. I never knew that, and since the notion that it won't is being raised again, I had to test it just now. It does.

The issue may be the AND operator in the input form control testing. Maybe an Admin doesn't need a password, thus the result of that test will always be false.
EDIT -never mind. Review shows that won't be the case.
Option Explicit needs to be added here and turned on for everything.
Also suggest you step through the code and check values. Level might not be what you expect.
Might also want to do repeated lookups just once and set variables instead of DLookup the same things more than once. Will help reduce code and make things easier to follow.

 

[Minty]

@Micron I did re-write the whole thing down to about 10 lines, but until the OP comes back to us, I thought I would hold fire on more code...

Interesting that the code still runs after form close, I'd never have thought that was the case, and certainly wouldn't normally program allowing for that.
Wonder what devious use I can put that nugget of info to...

 

[Deleted member 147267]

I wouldn't code that way either. It just seems counter intuitive - a mistake, even. Not something I'd rely on either, as it seems we have seen 'sloppy' code that used to run OK failing after an upgrade; almost as if Access became less tolerant of bad code.
In this case, I think steping through the code while checking variable values and reporting back is what's needed before spending much time on this in the mean time. Unless 2 or more people can use the same Windows login session, I don't understand the whole password thing when you can just validate that they're a registered user of the db.