FindFirst using Multiple Criteria

[mizenerman]

I'm desperate so I apologize if this post is redundant and any help is much appreciated. I have a combo box that is based on a query. The query looks up the "Last name" and "First name" of the form's source table then it finds the record and bookmarks it:

Dim MyRecSet As Object
Set MyRecSet = Me.Recordset.Clone
MyRecSet.FindFirst "[Last name] = '" & Me![Combo177] & "'"
Me.Bookmark = MyRecSet.Bookmark

The user then selects the appropriate listing from the drop down and it works beautifully. The problem is that there can be multiple entries in the table that have the same "Last name". If the user selects an entry with the same last name but different first name it only finds the first entry of the last name. Not surprising since the code is telling it to do just that. How can I get the FindFirst to look at the First name field too? Again, your help is much appreciated.

 

[VRoscioli]

Why not just add it as such:

MyRecSet.FindFirst "[Last name] = '" & Me![Combo177] & "' AND [[i]FirstNameField[/i]] = '" & [i]sourceForFirstName[/i] & "'"

-V

 

[PHV]

Say that the First name is in the second column of the combo:

Me.Recordset.FindFirst "[Last name]='" & Replace(Me!Combo177, "'", "''") & "' AND [First name]='" & Me!Combo177.Column(1) & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mizenerman]

Thank you both for your replies.

PH, that works perfectly! I never would have thought to use Replace - ever. Time and time again your posts have gotten my out of a jam. Thank you.

 

[PHV]

I never would have thought to use Replace
Hopefully you don't have any Irish last name in your DB ...