Drop Down Listing two fields to get the record I want 1

[RobotMush]

I have in the past gotten a drop down list with two fields in it to be able to get the exact record I am wanting. However, in doing a cut past and changing the name to the new database I get a

Run Tim Erro '3077':
Syntax error (missing operator) in expression.

The offending Program is as follows...

Private Sub RecLookUp_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Last Name] = '" & Me![RecLookUp] & "'And Nz(First Name) ='" & Nz(Me![RecLookUp].Column(1)) & "'"
Me.Bookmark = rs.Bookmark
End Sub

which is the result of my cut and paste a working model that is as follows...

Private Sub Combo20_AfterUpdate()
' Find the record in Client-frm that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[strLNCo] = '" & Me![Combo20] & "'And Nz(strFN) ='" & Nz(Me![Combo20].Column(1)) & "'"
Me.Bookmark = rs.Bookmark
End Sub

I do not know why one program works and the other does not.
Can fresh eyes see where I'm screwing up?

Thank You
RobotMush (Technical User)

 

[PHV]

You may try this (space before AND, brackets around First Name, 2nd arg for Nz and quoting of single quotes in searched strings):
rs.FindFirst "[Last Name]='" & Replace(Me![RecLookUp], [tt]"'", "''") & "'[/tt] AND Nz([First Name],[tt]'')='"[/tt] & Replace(Nz(Me![RecLookUp].Column(1)), [tt]"'", "''") & "'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RobotMush]

I'll be darn, IT WORKS!...
Now if I can figure out what all those little characters mean and what I had just typed. This is what seperates the Programmers from the Technical Users.

Thanks for the help PH

RobotMush (Technical Users)