Dlookup

[peterbowles]

I have a form which has the fields

txtUserID, txtQuestion, txtAnswer, txtPassword

gthe form is unbound and should allow the user to enter the details in the fields above. when the user presses OK the DB will user a DLOOKUP to match the txtUserID, txtQuestion and txtAnswer to the fields in the table and output the password to related to the record in txtPass

Any ideas

 

[pono1]

Peter,

One way: Create a public function in a module so you can re-use it on other forms.

I am presuming that together txtUserID and txtQuestion and txtAnswer essentially form a composite primary key in the table -- so that no one record in a table will have the same data in all three of these fields. I am also presuming that Question and Answer are stored as strings -- if not you will have to adjust the syntax of the DLOOKUP statement.

Winging the module and OK button code -- be sure to refine and test...

Public Function GetPassword (lngUserID as Long, _
strQuestion as String, strAns as String) As Variant

Dim lngU as long
Dim strQ as String
Dim strA as String
Dim varPassword

lngU = lngUserID
strQ = strQuestion
strA = strAns

VarPassowrd = DLOOKUP("fieldpassword","tblWhatever","[fieldUserID]=" & lngU & _
		"[fieldQuestion]= '" & strQ & "'" & _
		"[fieldAnswer]= '" & strA & "'")

If isNull(varPassword) Then
	GetPassword = "Not Found"
		Exit function
End if

Getpassword = VarPassword

End function

On the button's click event, something like this should be run:

Dim FindPass as string
Dim U as Long
Dim Q as String
Dim A as String

U = me.txtUserID.Value
Q = me.txtQuestion
A = me.txtAnswer

Findpass = GetPassword (U, Q , A) 

If FindPass = "Not Found" Then
	msgbox "Can't find password"
		Exit sub
End If

Me.txtPassword.value = FindPass

Another way: Write a single Dlookup statement in a textbox on the form so you won't need to click a button and write all the code...

Regards,
Tim

 

[peterbowles]

Thanks for the help I almost have it working but I get the error

"Missing Operator"

In this code

varPassword = DLookup("Password", "tblPassword", "[UserID]=' " & strU & "'" & _
"[Question]= '" & strQ & "'" & _
"[Answer]= '" & strA & "'")

 

[Mile-O]

varPassword = DLookup("Password", "tblPassword", "[UserID]='" & strU & "' AND " & _
"[Question] = '" & strQ & "' AND " & _
"[Answer] = '" & strA & "'")

 

[peterbowles]

Thanks again