issue with login VBA code

[siamandm]

Hello All,
I have this structure

Screenshot 2024-04-03 150250.png

and im trying to use this code but i get an error

Code: Select all

Private Sub cmdLogin_Click()
    Dim userLevel As Integer
    Dim userID As Variant
    Dim userPassword As String
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    
    On Error GoTo ErrorHandler
    
    ' Validate user input
    If IsNull(Me.cboUserName) Then
        MsgBox "Please select a user name.", vbExclamation, "User Name Missing"
        Me.cboUserName.SetFocus
        Exit Sub
    ElseIf IsNull(Me.txtPassword) Then
        MsgBox "Please enter your password.", vbExclamation, "Password Missing"
        Me.txtPassword.SetFocus
        Exit Sub
    End If
    
    ' Get user ID and password
    userID = Me.cboUserName.Value
    userPassword = Me.txtPassword.Value
    
    ' Debug statements to check parameter values
    ' Debug.Print "UserID: " & userID
    ' Debug.Print "Password: " & userPassword
    
    ' Open a recordset to check credentials
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("")
    
    ' Define SQL command with parameters
    qdf.SQL = "SELECT UserID, UserName, SecurityLevel FROM tblUsers INNER JOIN tblSecurityLevel ON tblUsers.UserID = tblSecurityLevel.SecurityID WHERE UserID = @UserID AND [Password] = @Password;"
    qdf.Parameters("@UserID") = userID
    qdf.Parameters("@Password") = userPassword
    
    ' Execute query
    Set rs = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
    
    ' Check if user exists and password matches
    If Not rs.EOF Then
        ' User authenticated successfully
        userLevel = rs!SecurityLevel
        If userLevel = 1 Then
            ' Open Home Form
            DoCmd.OpenForm "frmDataEntry", acNormal
        ElseIf userLevel = 2 Then
            ' Open Change Password Form
            DoCmd.OpenForm "frmChangePassword", acNormal
        End If
        ' Close the login form
        DoCmd.Close acForm, Me.Name
    Else
        ' Invalid credentials
        MsgBox "Incorrect user name or password. Please try again.", vbExclamation, "Login Failed"
        Me.cboUserName.SetFocus
        Me.cboUserName.Dropdown
    End If
    
    ' Clean up
    rs.Close
    Set rs = Nothing
    Set qdf = Nothing
    Set db = Nothing
    
    Exit Sub

ErrorHandler:
    MsgBox "Error: " & Err.Number & " - " & Err.Description, vbCritical, "Error"
    
    ' Clean up
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    If Not qdf Is Nothing Then
        Set qdf = Nothing
    End If
    If Not db Is Nothing Then
        Set db = Nothing
    End If
End Sub

Screenshot 2024-04-03 150328.png

[HansV]

You have to use the User_Privilege_Mapping table.
And I wouldn't define a querydef, but open a recordset on a SQL string.
In the following, use the values of the SecurityID field, not of the SecurityLevel field.

Code: Select all

Private Sub cmdLogin_Click()
    Dim userLevel As Long
    Dim userID As Variant
    Dim userPassword As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String

    On Error GoTo ErrorHandler

    ' Validate user input
    If IsNull(Me.cboUserName) Then
        MsgBox "Please select a user name.", vbExclamation, "User Name Missing"
        Me.cboUserName.SetFocus
        Exit Sub
    ElseIf IsNull(Me.txtPassword) Then
        MsgBox "Please enter your password.", vbExclamation, "Password Missing"
        Me.txtPassword.SetFocus
        Exit Sub
    End If

    ' Get user ID and password
    userID = Me.cboUserName.Value
    userPassword = Me.txtPassword.Value

    ' Debug statements to check parameter values
    ' Debug.Print "UserID: " & userID
    ' Debug.Print "Password: " & userPassword

    ' Open a recordset to check credentials
    Set db = CurrentDb

    ' Define SQL command with parameters
    strSQL = "SELECT User_Privilege_Mapping.SecurityID FROM tblUsers INNER JOIN User_Privilege_Mapping " & _
            "ON tblUsers.UserID = User_Privilege_Mapping.UserID " & _
            "WHERE tblUsers.UserID = " & userID & " AND tblUsers.Password = '" & Replace(Password, "'", "''") & "'"

    ' Execute query
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)

    ' Check if user exists and password matches
    If Not rs.EOF Then
        ' User authenticated successfully
        userLevel = rs!SecurityID
        Select Case userLevel
            Case 1 ' *** Use the values of the SecurityID field ***
                ' Open Home Form
                DoCmd.OpenForm "frmDataEntry", acNormal
            Case 2
                ' Open Change Password Form
                DoCmd.OpenForm "frmChangePassword", acNormal
                ' Close the login form
                DoCmd.Close acForm, Me.Name
        End Select
    Else
        ' Invalid credentials
        MsgBox "Incorrect user name or password. Please try again.", vbExclamation, "Login Failed"
        Me.cboUserName.SetFocus
        Me.cboUserName.DropDown
    End If

ExitHandler:
    ' Clean up
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    If Not db Is Nothing Then
        Set db = Nothing
    End If

    Exit Sub

ErrorHandler:
    MsgBox "Error: " & Err.Number & " - " & Err.Description, vbCritical, "Error"
    Resume ExitHandler
End Sub

[siamandm]

thank you very much for the reply,
i have used your code, but it always says wrong user name or password !!, and im sure they are correct

[HansV]

Could you attach a zipped database with a few dummy records in the tables?

[siamandm]

here it is :)

[HansV]

Thanks, that was my mistake. Sorry about that.
The part that defines strSQL should be

Code: Select all

    strSQL = "SELECT User_Privilege_Mapping.SecurityID FROM tblUsers INNER JOIN User_Privilege_Mapping " & _
            "ON tblUsers.UserID = User_Privilege_Mapping.UserID " & _
            "WHERE tblUsers.UserID = " & userID & " AND tblUsers.Password = '" & Replace(userPassword, "'", "''") & "'"

By the way, since you let the user select the username from a combo box, the username will never be incorrect. So perhaps your message should be

Code: Select all

        MsgBox "Incorrect user name/password combination. Please try again.", vbExclamation, "Login Failed"

[siamandm]

Thank you very much much appreciated

[siamandm]

the login form suppose to be closed after login , but i notice it is not close, why is this please? it close when the next form is closed!

Code: Select all

 userLevel = rs!SecurityID
        Select Case userLevel
            Case 1 ' *** Use the values of the SecurityID field ***
                ' Open Home Form
                DoCmd.OpenForm "frmDataEntry", acNormal
            Case 2
                ' Open Change Password Form
                DoCmd.OpenForm "frmChangePassword", acNormal
                ' Close the login form
                DoCmd.Close acForm, Me.Name
        End Select

[HansV]

Does this work better?

Code: Select all

        userLevel = rs!SecurityID
       ' Close the login form
        DoCmd.Close acForm, Me.Name
 
        Select Case userLevel
            Case 1 ' *** Use the values of the SecurityID field ***
                ' Open Home Form
                DoCmd.OpenForm "frmDataEntry", acNormal
            Case 2
                ' Open Change Password Form
                DoCmd.OpenForm "frmChangePassword", acNormal
        End Select

[Gasman]

Why not use
DoCmd.Close acForm, Me.Name
for case 1 ?

In that case it can be outside the Select?

[siamandm]

i have used for case 1 as well, still the login form keeps open until i close the next one!

[siamandm]

Dear Hans, thank you very much it worked by putting the close code outside the select

thanks again.

[Gasman]

i have used for case 1 as well, still the login form keeps open until i close the next one!

Cannot see how that could happen?

[siamandm]

Thank you Gasman for your support much appreciated.

Regards