Thread: Combo Box to find records on Form - coding problem

I have the following code in the AfterUpdate event on a combo box, which is in the header of a Form (which is based on an underlying table). The combo is used for finding records in the form body, not for populating any fields.

Private Sub cboActiveFilter_AfterUpdate()
Me.Filter = ""
Me.Filter = "Forename = '" & Me.cboActiveFilter & "'"
Me.FilterOn = True
'clears the value from the combo box after record is found, ready for next use
With Me.cboActiveFilter
.Value = Null
End With
End Sub

The combo is calling from a query which filters the table/form records according to a criteria placed on a certain textfield, and has 3 other fields besides in the query: EmpID (AutoNumber, Primary key) and 2 textfields named forename and surname". These two text fields are used in the Combo columns.

So the above works, sort of: The ComboBox populates with the filtered records & then I select for eg. "John DOE" (forename/surname) from the Drop-Down - the AfterUpdate event kicks in, and the form shifts to the matching record - but obviously, it only works for me if there is only ONE instance of the selected forename in the records. And because my database contains multiple records with the same forename (and surname), the above code only finds the first instance of the "forename", not necessarily the one I want.
EG: If I want to go to (and 'select' the "John SMITH" record in the Combo, it will only take me to "John DOE" on the form (found first)

So I tried to redo the combo, by including the unique "EmpD" field in it, making it Column 1 in the Combo - and replacing "Forename in the code, with "EmpID ie: I want the combo selection to find matching "EmpID" in the form.

This doesn't work - I'm guessing because "EmpID" is not a text-field - whereas the code is written for a text field??

If I keep the redone Combo - can the AfterUpdate code above be changed to make it find the selected "EmpID" autonumber on the form?

If I revert to the original combo (with just forname and surname) can I change the above code so that it find the matching combination of Forename & Surname in one record on the form?

Thanks :-)

annieb

edited: oops, sorry royuk - have changed to code tags!

Please use Code Tags, not Quote Tags

Annieb;
Are you able to post your db with some sample data? This would give us a better idea on how to evaluate what you are doing and offer some sound advise to resolve your issue. It is sometimes difficult to envision exactly what is happening without viewing the actual situation.

Alan