Combo Box Search

[Guest]

After reviewing many previous posts, I cant find the exact answer to (what I
think) is a simple question.

I have a table of 6,000+ names. An ID number is the primary key.

There are many records with identical last names.

I placed a combo box in a form so I can search for individual records. I
set it with columns for ID (hidden), Lastname and Firstname.

It works, but only so far. When I type in a lastname, it only returns the
first occurance (based on ID, I think) of that last name. If I wanted one of
the others, I am up a creek. The drop down on the combo box does not list
the other records with the same lastname.

Ideally, I would type a last name, click the drop down arrow, and be able to
see all the records with that last name, then select the one I want.

Sounds simple, and I'm sure it is, but I am not getting it. Help please.

 

[Jeff Boyce]

When I create a combo box to do what you've described, I'll typically first
build a query to get what I need, then tell the combo box to use the query.

The query I write would have two fields:
Field1: ThisTableID

Field2: [Lastname] & ", " & [FirstName]

I hide (0 width) the ID field, and display the Field2.

Then my combo box lists the first (of potentially many) Field2's that start
with whatever I type in, as I type (this requires setting the AutoExpand
property to Yes.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Wow, thanks Jeff. I never would have though of that and it works perfectly...

except, when I choose a record in the combobox, the other fields in the form
are not updated with that record's data...how do I 'link' the rest of the
form to the combobox results?

Thanks again.

--
Joe

When I create a combo box to do what you've described, I'll typically first
build a query to get what I need, then tell the combo box to use the query.

The query I write would have two fields:
Field1: ThisTableID

Field2: [Lastname] & ", " & [FirstName]

I hide (0 width) the ID field, and display the Field2.

Then my combo box lists the first (of potentially many) Field2's that start
with whatever I type in, as I type (this requires setting the AutoExpand
property to Yes.

Regards

Jeff Boyce
Microsoft Office/Access MVP

After reviewing many previous posts, I cant find the exact answer to (what
I
think) is a simple question.

I have a table of 6,000+ names. An ID number is the primary key.

There are many records with identical last names.

I placed a combo box in a form so I can search for individual records. I
set it with columns for ID (hidden), Lastname and Firstname.

It works, but only so far. When I type in a lastname, it only returns the
first occurance (based on ID, I think) of that last name. If I wanted one
of
the others, I am up a creek. The drop down on the combo box does not list
the other records with the same lastname.

Ideally, I would type a last name, click the drop down arrow, and be able
to
see all the records with that last name, then select the one I want.

Sounds simple, and I'm sure it is, but I am not getting it. Help please.

 

[Guest]

I think I figured it out. No need to respond. Thanks again for your help
--
Joe

When I create a combo box to do what you've described, I'll typically first
build a query to get what I need, then tell the combo box to use the query.

The query I write would have two fields:
Field1: ThisTableID

Field2: [Lastname] & ", " & [FirstName]

I hide (0 width) the ID field, and display the Field2.

Then my combo box lists the first (of potentially many) Field2's that start
with whatever I type in, as I type (this requires setting the AutoExpand
property to Yes.

Regards

Jeff Boyce
Microsoft Office/Access MVP

After reviewing many previous posts, I cant find the exact answer to (what
I
think) is a simple question.

I have a table of 6,000+ names. An ID number is the primary key.

There are many records with identical last names.

I placed a combo box in a form so I can search for individual records. I
set it with columns for ID (hidden), Lastname and Firstname.

It works, but only so far. When I type in a lastname, it only returns the
first occurance (based on ID, I think) of that last name. If I wanted one
of
the others, I am up a creek. The drop down on the combo box does not list
the other records with the same lastname.

Ideally, I would type a last name, click the drop down arrow, and be able
to
see all the records with that last name, then select the one I want.

Sounds simple, and I'm sure it is, but I am not getting it. Help please.