Combo box to select records on a form

[Seth]

Access 2003
Windows XP

I want to use a combo box on a form to find a record. I have inserted
the combo box on the form that has two columns, last name and first
name, and it displays them sorted first by last name and then first
name. It looks like this –

Johnson Able
Johnson Baker
Johnson Charlie

When I point to one of the entries and click, it always displays the
first record with that last name. In other words, if I click on
Johnson, Charlie, the form displays the record for Able Johnson, not
Charlie Johnson. And I cannot go to the desired record. I want it to
display the record I click on in the combo box.

 

[Golfinray]

Use the combo box wizard to create a combo with the search field you want.
After the combo is on your form, right click on it and select events. On the
afterupdate event start the code builder and type:
Me.Filter = "[yourfieldname] = """ & Me.combo# & """"
Me.filteron = true

Combo# will be like combo21 or combo6 or you give it a name if you like.

 

[Beetle]

In the table that holds the names (tblCustomers or whatever it's called) you
should have in ID field to uniquely identify each customer (i.e. CustomerID).
This field should be added to the rowsource query for your combo box. You
can make it hidden if you don't want your users to see it. It should also
be the bound column of your combo. Then when you perfom a search,
Access will find the unique ID number for the name selected in the combo
and will take you to the exact record you want.

 

[Seth]

In the table that holds the names (tblCustomers or whatever it's called) you
should have in ID field to uniquely identify each customer (i.e. CustomerID).
This field should be added to the rowsource query for your combo box. You
can make it hidden if you don't want your users to see it. It should also
be the bound column of your combo. Then when you perfom a search,
Access will find the unique ID number for the name selected in the combo
and will take you to the exact record you want.

Works perfectly. Thanks.