VBA Access Dlookup login and password from two tables

Question

I'm having issues with a login menu that I am creating for my database. For this database I have the location of the User login email and password in two locations. After I solve this issue, i'll make validate where the login details originated from to dictate which forms open, for now I have one form to open,

For now I just want to confirm if the the user logins and passwords are valid from either table. However it can only validate the user Login and Password from from tblMembers. If I try to enter details from tblTrainers, I would keep getting a mismatch error. I am aware what this error but not too sure how it works here.

However if I get rid off the Or statement close the statement, it works but of course I cannot use login details from tblTrainers to login. Could anyone offer any suggestions please? Code found below.

Private Sub Command1_Click()
If IsNull(Me.txtLoginID) Then
    MsgBox "Please Enter LoginID", vbInformation, "Required"
    Me.txtLoginID.SetFocus
ElseIf IsNull(Me.txtPassword) Then
    MsgBox "Please Enter A Password", vbInformation, "Required"
    Me.txtPassword.SetFocus
Else
    If (IsNull(DLookup("Member_Email", "tblMembers", "Member_Email = '" & Me.txtLoginID.Value & "' And Member_Password = '" & Me.txtPassword.Value & "'")) Or (DLookup("Trainer_Email", "tblTrainers", "Trainer_Email = '" & Me.txtLoginID.Value & "' And Trainer_Password = '" & Me.txtPassword.Value & "'"))) Then
        MsgBox "Inccorect LoginID or Password"
    Else
        DoCmd.OpenForm "mnuMain_Menu"
        DoCmd.Close acForm, "frmLogin"
    End If
End If
End Sub

Erik A · Answer 1 · 2018-05-03T10:10:44.237

You can use CurrentDb.OpenRecordset to open recordsets based on SQL queries. You can use .EOF to check if the recordset is at the end of the file, thus contains 0 records.

If you want to query multiple tables at once, you can use a UNION query for that.

If CurrentDb.OpenRecordset("SELECT 1 FROM tblMembers WHERE Member_Email = '" & Me.txtLoginID.Value & "' And Member_Password = '" & Me.txtPassword.Value & "' UNION ALL SELECT 1 FROM tblTrainers WHERE Trainer_Email = '" & Me.txtLoginID.Value & "' And Trainer_Password = '" & Me.txtPassword.Value & "'").EOF Then

Note that this login code is at risk for SQL injection, and these kind of login forms are fundamentally insecure. You can easily demonstrate SQL injection by entering ' OR 1 = 1 OR '' = ' as a username, and entering a random character in the password field. That passes as a valid login if there are entries in the table. An easy fix for SQL injection is to use parameters.

VBA Access Dlookup login and password from two tables

1 Answers1