DLookUp with 2 criteria

[Guest]

I've entered the following code to the click event of a button to open a form
if the [EmpID] and [EmpName] selected from a list box and stored in Text24
and Text34 on the current form match the corresponding password in the table
to the one entered on the current form. [EmpID] is a number & [EmpName] is
Text. It works with one or the other in the code, but not with both. Can
anyone see what I'm doing wrong?
=================================================
If Me.txtPassword.Value = DLookup("EmpPassword", "tblEmployees", "[EmpID]="
& Me.Text24.Value And "[EmpName]='" & Me.Text34.Value & "'") Then
lngMyEmpID = Me.Text24.Value

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmShop"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
End If

 

[Ken Snell \(MVP\)]

DLookup("EmpPassword", "tblEmployees", "[EmpID]=" & Me.Text24.Value & " And
[EmpName]='" & Me.Text34.Value & "'")

Your syntax was close.....

By the way, you may want to trap for the possibility of a Null value being
returned by the DLookup (cannot find a matching record) so that an error
doesn't occur in the test.


Dim varPW As Variant
varPW = DLookup("EmpPassword", "tblEmployees", _
"[EmpID]=" & Me.Text24.Value & " And [EmpName]='" & _
Me.Text34.Value & "'")
If IsNull(varPW) = False Then
If Me.txtPassword.Value = varPW Then
lngMyEmpID = Me.Text24.Value

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmShop"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid
Entry!"
Me.txtPassword.SetFocus

End If

Else
MsgBox "Error in data."
End If

 

[Guest]

Thank you I'll move that quote. Also thank you for the suggestion about null
value. I'll try to put that to use in another spot. This routine has some
additional code that checks for both values and pops-up a message box if one
is missing.
Thanks again for your help. I'll try it this afternoon when the program is
available to me.

DLookup("EmpPassword", "tblEmployees", "[EmpID]=" & Me.Text24.Value & " And
[EmpName]='" & Me.Text34.Value & "'")

Your syntax was close.....

By the way, you may want to trap for the possibility of a Null value being
returned by the DLookup (cannot find a matching record) so that an error
doesn't occur in the test.


Dim varPW As Variant
varPW = DLookup("EmpPassword", "tblEmployees", _
"[EmpID]=" & Me.Text24.Value & " And [EmpName]='" & _
Me.Text34.Value & "'")
If IsNull(varPW) = False Then
If Me.txtPassword.Value = varPW Then
lngMyEmpID = Me.Text24.Value

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmShop"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid
Entry!"
Me.txtPassword.SetFocus

End If

Else
MsgBox "Error in data."
End If

--

Ken Snell
<MS ACCESS MVP>

I've entered the following code to the click event of a button to open a
form
if the [EmpID] and [EmpName] selected from a list box and stored in Text24
and Text34 on the current form match the corresponding password in the
table
to the one entered on the current form. [EmpID] is a number & [EmpName]
is
Text. It works with one or the other in the code, but not with both. Can
anyone see what I'm doing wrong?
=================================================
If Me.txtPassword.Value = DLookup("EmpPassword", "tblEmployees",
"[EmpID]="
& Me.Text24.Value And "[EmpName]='" & Me.Text34.Value & "'") Then
lngMyEmpID = Me.Text24.Value

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmShop"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
End If