issue with login VBA code

siamandm
BronzeLounger
Posts: 1209
Joined: 01 May 2016, 09:58

issue with login VBA code

Post by 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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: issue with login VBA code

Post by 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
Best wishes,
Hans

siamandm
BronzeLounger
Posts: 1209
Joined: 01 May 2016, 09:58

Re: issue with login VBA code

Post by 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

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: issue with login VBA code

Post by HansV »

Could you attach a zipped database with a few dummy records in the tables?
Best wishes,
Hans

siamandm
BronzeLounger
Posts: 1209
Joined: 01 May 2016, 09:58

Re: issue with login VBA code

Post by siamandm »

here it is :)
Last edited by siamandm on 03 Apr 2024, 13:41, edited 1 time in total.

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: issue with login VBA code

Post by 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"
Best wishes,
Hans

siamandm
BronzeLounger
Posts: 1209
Joined: 01 May 2016, 09:58

Re: issue with login VBA code

Post by siamandm »

Thank you very much much appreciated

siamandm
BronzeLounger
Posts: 1209
Joined: 01 May 2016, 09:58

Re: issue with login VBA code

Post by 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

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: issue with login VBA code

Post by 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
Best wishes,
Hans

User avatar
Gasman
StarLounger
Posts: 94
Joined: 22 Feb 2022, 09:04

Re: issue with login VBA code

Post by Gasman »

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

In that case it can be outside the Select?
Using Access 2007/2019.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.

siamandm
BronzeLounger
Posts: 1209
Joined: 01 May 2016, 09:58

Re: issue with login VBA code

Post by siamandm »

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

siamandm
BronzeLounger
Posts: 1209
Joined: 01 May 2016, 09:58

Re: issue with login VBA code

Post by siamandm »

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

thanks again.

User avatar
Gasman
StarLounger
Posts: 94
Joined: 22 Feb 2022, 09:04

Re: issue with login VBA code

Post by Gasman »

siamandm wrote:
04 Apr 2024, 09:37
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?
Using Access 2007/2019.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.

siamandm
BronzeLounger
Posts: 1209
Joined: 01 May 2016, 09:58

Re: issue with login VBA code

Post by siamandm »

Thank you Gasman for your support much appreciated.

Regards