Finding records, need help

[thefourthwall]

I've got a search form with two unbound combo boxes at the top. One is for searching records by phone number, the other combo box is for searching records by last name.

This is great unless one person has multiple phone numbers assigned. I need to display each record (phone assignment) even if there are multiple phones assigned to the same person.

The combo for searching by last name's row source is

SELECT qryEmployeeDevice.LAST_NAME, qryEmployeeDevice.FIRST_NAME, qryEmployeeDevice.MIDDLE_INITIAL, qryEmployeeDevice.TZS_CODE, qryEmployeeDevice.EID
FROM qryEmployeeDevice
ORDER BY qryEmployeeDevice.LAST_NAME, qryEmployeeDevice.FIRST_NAME;

and the code behind the After Update event is

Private Sub Combo94_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[EID] = '" & Me![Combo94] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

If I pull up, say Smith, Jim and he has two phones assigned, the first ph# displays; if I choose the second Smith, Jim in the list, the form does not refresh, since the data displayed is based on the EID field and in this case its the same for both entries. Will appreciate a better way of looking at this, thanks.

 

[PHV]

You may try this:

rs.FindFirst "EID='" & Me!Combo94 & "' AND TZS_CODE='" & Me!Combo94.Column(3) & "'"

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

 

[thefourthwall]

I used that code and the results are the same as before. Any thoughts?

 

[thefourthwall]

Wondering if adding another field to the query behind the combo box so that the code can match on something else . . . this is interesting!