Excel VBA LogIn Form with Stored Passwords on Access Database

Question

I want to validate my Userfrom password with the one stored in Access Database, when I run my code below, I didn't receive any errors, however, when I try to put a wrong password, it says "Correct Password.". Can you help me on this one?

Public cn As ADODB.Connection
Public rs As ADODB.Recordset

Sub LogIn()

Dim cn As Object
Dim rs As Object
Dim strSql As String
Dim DBPath As String
Dim userId As String

If Frm_UserPW.TxtBox_Password.Text = Empty Then
    MsgBox "Please type your password."
Else
    DBPath = Range("I2").Value
    Set cn = New ADODB.Connection

        cn.Provider = "Microsoft.ACE.OLEDB.12.0"
        cn.Properties("Data Source") = DBPath
        cn.Properties("Jet OLEDB:Database Password") = "db12345"
        cn.Open

        userId = Range("C3").Value

        'Open a recordset
        Set rs = New ADODB.Recordset
            'rs.Open "Inbox", cn, adOpenKeyset, adLockOptimistic, adCmdTable

            strSql = "SELECT * FROM ApplePassword WHERE AgentName=""" & userId & """ AND Passwords =""" & Frm_UserPW.TxtBox_Password.Text & """"
        Set rs = cn.Execute(strSql)

            MsgBox "Correct Passsword."
            Frm_UserPW.Hide
            Main.Show

            'clear memory
            rs.Close
        Set rs = Nothing
        cn.Close
    Set cn = Nothing

End If

End Sub

Answer 1

You are basing the result of the password check on the success of the ability to run the query, not on the result of the query.

You are using Set rs = cn.Execute(strSql) like an If statement, i.e. if it executes the command without and error then the password must be a match.

What you need is an if statement within then works based on the results of the query, I would recommend: -

strSql = "SELECT count(*) FROM ApplePassword WHERE AgentName=""" & userId & """ AND Passwords =""" & Frm_UserPW.TxtBox_Password.Text & """"
Set rs = cn.Execute(strSql)
    if rs(0) > 0 then 'I.e. there was at least one match!
        MsgBox "Correct Password."
        Frm_UserPW.Hide
        Main.Show
    End If

    'clear memory
    rs.Close
Set rs = Nothing