Please Help! DLookup... What Am I Doing Wrong!!!

[AmandaA531]

This DLookup has me stumped!

In a form [frm_MCOs], I have 2 fields, [ChangeAnalystApproval], [DocControlPassword]
In a table [tbl_Employees], I have 2 fields [EmployeeName], [Passcode]

I would like the database to compare the fields in [frm_MCOs] form to fields in [tbl_Employees] table like so:
[frm_MCOs]![ChangeAnalystApproval] must equal [tbl_Employees]![EmployeeName]
[frm_MCOs]![DocControlPassword] must equal [tbl_Employees]![Passcode]
If Password is blank, display msgbox1
If Passcode and DocControlPassword match, display the msgbox2
If Passcode doesn’t match DocControlPassword, display msgbox3

It recognizes the "blank" Password but the problem I'm having is that it doesn't seem to be comparing the Passcode and DocControlPassword.

Here is my code:



Private Sub DocControlCheckPassword_Click()

' msgbox1
If IsNull(Me.DocControlPassword.Value) Then
MsgBox "You cannot enter a blank Password. Try again."
Me.DocControlPassword.SetFocus
End If

' msgbox2
If DLookup("Passcode", "tbl_Employees", "EmployeeName='" & Me.DocControlPassword & "'") Then
MsgBox "You have approved this change."
Else
' msgbox3
MsgBox "Password does not match. Please try again."
Me.DocControlPassword.SetFocus
End If

End Sub

[PhilS]

the problem I'm having is that it doesn't seem to be comparing the Passcode and DocControlPassword.

... "EmployeeName='" & Me.DocControlPassword & "'" ....
Looking at that line of code, the cause of problem is pretty obvious, isn't it?

[AmandaA531]

Looking at that line of code, the cause of problem is pretty obvious, isn't it?

Thanks for that tip PhilS :yes
But correcting the code to
"Passcode=" & Me.DocControlPassword = "'" didn't help.
It's still not comparing the 2 fields. If I type in the wrong or right password, it tells me that it's not correct.

[OBP]

Shouldn't that be
"Passcode= '" & Me.DocControlPassword = "'"

[AmandaA531]

OPB, it doesn't work that way either.

Des this help clarify?
ChangeAnalystApproval [EmployeeName] is a combo box from a query.
Find the [EmployeeName] in the [tbl_Employees] and retrieve the [Passcode]
Then the [frm_MCOs]![DocControlPassword] must equal [tbl_Employees]![Passcode]If Password is blank, display msgbox1
If Passcode and DocControlPassword match, display the msgbox2
If Passcode doesn’t match DocControlPassword, display msgbox3

[PhilS]

If you want to check if EmployeeName AND Passcode match, you need to check both, either in the criteria expression or by comparing the result of the lookup with the value in the form.

The direct, boolean evaluation of the DLookup return value looks fishy to me. I suspect it will not work. So, I replaced the DLookup with a DCount.


If DCount("*", "tbl_Employees", "EmployeeName='" & Me!ChangeAnalystApproval & "' AND Passcode='" & Me!DocControlPassword & "'") Then

[OBP]

I have the Password (in Password format & set to Invsible) and the Names on the login form, so there is no looking up to do.

[AmandaA531]

Thank you, thank you, thank you :bow: PhilS!!!
I was having such a difficult time wrapping my head around this but it works perfectly now!