1

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.

Improve this question

2 Answers 2

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Hi Lee Mac, thank you for your fast reply. DlookUp was my initial attempt as I am used to VlookUp from Excel. However, Dlookup would work fine if there is only one Password set to Admin. In my table I intend to set more than one Password as admin and the String from Inputbox to compare to those passwords.
I'm not sure I follow... are you saying that there could be multiple records with the same Admin value and the same Password value? And if so, why is this a problem for the use of DLookup? Could you edit your question to show some sample data describing the issue?
Let me try to simplify the example. Field1 "name", field2 "password" field3 "Admin Yes/No". Inputbox string to be compared with field2 if Admin = Yes. So multiple admins can enter their own passwors.
So Dlookup function will stop searching when the first field meet the criteria, but the consecutive admin's password will not be picked, hence not compared with Inputbox string.
In that case, I cannot see a reason why the use of DLookup would not work - could you provide an example (e.g. edit your question to include an example table of data, and a given input) for which you believe this method would fail?
|
0

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
Improve this answer

6 Comments

The code is working, but not as intended. As of now only the last password is compared with the Inputbox String.
MySQL backend? Usually login credentials contain a username and a Password(hashed). You fetch user from crewlist and then compare input password with stored. If you only compare all passwords, what if passwords are not unique? Limit recordset by using password(/user) in query criteria and yes/no fields are True/False(<>0/0). Extend Where clauseWhere Crewlist.Admin <> 0 AND Crewlist.Password = '" & AdmPass & "'""(Don't forget the hash!). No need for moving firdt/last unless you want to to count records (recordset always open with first record or if empty at .EOF).
Thanks for commenting ComputerVersteher. First point noted, I will amend Admin field with Yes/No check, now it is simple text "Yes" or "No". Second point - If two names are declared as Admins, there is no issue if their passwords are the same, it's quite unusual. I want to keep it simple for users by only typing their passwords, and If one is set as admin, the form will be unlocked for edit and so on. Point 3 noted, I will remove Movefirst and movelast.
mySQL is the name I chose, it is looking in table "Crewlist" and filters if field Admin=Yes.
That can confuse people. Just name itSQLor more detailedSqlCrewlistAdmin. Chose your naming convention and be consequent on it (Upper/Lowercase names)
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.