CODEWITHSUNDEEP
vbams-accesssearchrecordset
fremax
fremax

Reputation: 13

Compare String with Table fields value

Hello to all programmers, I am very new to MS Access, but have some experience with Excel (including VBA). I've been searching for few days now and tested several suggestions with zero success so far. It's getting frustrating already and I decided to ask directly for my case.

From a loaded form I get the user to input password via Inputbox, I need my code to search for that string in my table Crewlist, field Password and criteria is another field Admin (Yes/No).

To facilitate the search I made a query table containing all people with field Admin (all are Yes) and field Password.

Looking forward for any particular solution. So far I have tried creating array & recordset.

Edit (e.g as requested - table containing required info to be compared)

Field1 Field2 Field3("Admin")

Name1 password1 No

Name2 password2 Yes

Name3 password3 Yes

"If " statement comparing Inputbox string with Field2 where Admin=Yes Dlookup will return the first result, ie Password2, but Name3 and password3 should also be looked and compared).

Recordset and simple SQL code will be required, I am now working on it.

Upvotes: 1

Views: 2147

Answers (2)

Lee Mac
Lee Mac

Reputation: 16025

I would suggest using a simple DLookup expression for this task, for example, assuming that the password entered by the user is stored in a variable pwd, you might use:

DLookup("Password","Crewlist","Admin = True and Password = '" & pwd & "'")

The DLookup function will return Null if no match is found, which you can test with an If statement and the IsNull function, e.g.:

If IsNull(DLookup("Password","Crewlist","Admin = True and Password = '" & pwd & "'")) Then
    MsgBox "Invalid Password!"
Else
    ' Do Stuff
End If

Here, I only specify the Password field as the field to be looked up as DLookup requires a specific field whose value should be returned. You could instead use the DCount function and test whether the return is non-zero, e.g.:

If DCount("*","Crewlist","Admin = True and Password = '" & pwd & "'") > 0 Then
    ' Do Stuff
Else
    MsgBox "Invalid Password!"
End If

Implemented in the event handler for a button, this might be written as:

Private Sub Command0_Click()
    Dim pwd As String
    pwd = InputBox("Enter Password:", "Password")

    If pwd <> vbNullString Then
        If IsNull(DLookup("Password", "Crewlist", "Admin = True and Password = '" & pwd & "'")) Then
            MsgBox "Invalid Password!"
        Else
            MsgBox "Access Granted!"
        End If
    End If
End Sub

Note that this is only checking the password, hence, with only the above code, the user could specify the password for any Admin user and be granted access.

You could easily check the username with an additonal prompt:

Private Sub Command0_Click()
    Dim usr As String
    Dim pwd As String

    usr = InputBox("Enter Username:", "Username")
    If usr <> vbNullString Then
        pwd = InputBox("Enter Password:", "Password")
        If pwd <> vbNullString Then
            If IsNull(DLookup("Password", "Crewlist", "Admin = True and Username = '" & usr & "' and Password = '" & pwd & "'")) Then
                MsgBox "Invalid Username or Password!"
            Else
                MsgBox "Access Granted!"
            End If
        End If
    End If
End Sub

However, this would be more professional if you were to design your own modal form containing a textbox or combobox for the username and a textbox in which the user may specify their password.

Aside, storing passwords in a database in plain text is bad practice: consider hashing the password using an appropriate hash function and storing the hash value. Then, apply the same hash function to the user input and use the resulting hash value to test for a match in your database.

This way, only the user has knowledge of the password - since hashing is a one-way process, not even the database admin has knowledge of user's passwords. If a user needs to change their password, they would either be supplied with a new temporary password that they could change or would provide a new password following some other authentication.

As a general rule, never trust any service that is able to send you your original password - this reveals that such a service is storing passwords without encryption/masking.

Upvotes: 4

fremax
fremax

Reputation: 13

This is what I came with this morning after reading some examples about recordset and looping. My code in VBA

Sub Auto_Logo0_Dblclick
Dim AdmPass As String
AdmPass = Inputbox ("Admin password required")

'next part is taken from my guide book for Access
Dim Con1 As ADODB.Connection
Set Con1 = CurrentProject.Connection
Dim rcrdPass As New ADODB.Recordset
rcrdPass.Activeconnection = Con1

'SQL part
 Dim mySQL as String
    mySQL = "SELECT Crewlist.Surname, Crewlist.Password,"
    mySQL = mySQL & " Crewlist.Admin"
    mySQL = mySQL & " From Crewlist"
    mySQL = mySQL & " Where (Crewlist.Admin = 'Yes')"
 rcrdPass.Open mySQL
    With rcrdPass
          If Not .BOF And Not .EOF Then
             .MoveFirst
             .MoveLast
           While (Not .EOF)
              If AdmPass = rcrdPass.Fields("Password") Then
                 Call DoCmd.SelectObject(acTable,,True)
                 Else Msgbox ("Wrong password, try again")
                 End If
             .MoveNext
           Wend
      End If
 End With
 rcrdPass.Close
 Set rcrdPass = Nothing
 Set Con1 = Nothing
 End sub

Upvotes: 0

Related Questions