Lookup Fields and Combo Box

[DMH]

Help would be much appreciated!

I'm trying to add a combo box to a form that uses a tables lookup field to
search for a record. It works fine, however the combo box displays the
numeric id of the lookup field instead of text. How can I get the combo box
to display the same column the table displays?

 

[Jeff Boyce]

Lookup field data types in tables lead to considerable confusion ... as
you've found!

You might want to consider changing that table data field type to whatever
the underlying ID field is, and NOT relying on the table for your view of
the data.

That way, you can build your combobox control against the original (lookup)
table (NOTE -- this is NOT a lookup field, but a lookup table) and set the
properties for bound column as needed.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[DMH]

Thanks Jeff,

I think I tried what you suggested with no luck. Here's a little more
detail on what I'm trying to do. I have a form (frm_SearchOriginator) that
has a record source of tbl_FSR that contains the lookup field
"OriginatorName". I created a combo box to search on this lookup field in
the table and it works just fine except the combo box only displays the
primary key autonumber instead of column 1 which is the Originator Name?
When I open the table is displays the Originators Name but won't show it in
the combo box?

Thank you for helping! I'm so frustrated and I can't believe this is so
difficult to figure out.

 

[Jeff Boyce]

As I mentioned previously, don't use the lookup field as the source for your
combobox. Use the underlying lookup table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[DMH]

Did that and the combo box displays the fields I want but isn't retrieving
the records from the table. Seems as though I'm missing a link. I have the
record source for the form set to the table I want to update and the combo
box control set to the lookup table. How do I make the connection between
the two now?

Thanks

 

[Jeff Boyce]

The combobox is based on the underlying lookup table.

The combobox is also "bound" to the field in your first table that will hold
the ID value.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[DMH]

Ok the rowsource for the combo box is the lookup table and the control source
for the combo box is the lookup field in the source table. All that does for
me is when I choose a field from the combo list it simply changes the lookup
field in the source table to match? It's still not returning the records
that match the field in the combo box. Source Table is tbl_FSR with the
lookup field "OriginatorName", Lookup Table is tbl_Originator. Do you have
any examples of this you can show me?

 

[DMH]

I finally figured it out! Thanks for trying to work through this with me.

 

[Jeff Boyce]

I'm not sure how to say it differently. STOP using the lookup field in your
table! Convert it to the datatype of the underlying ID.

Then do as you've described, making the rowsource the lookup table (or
better still, a query based on that table), and the control source the field
(a "foreign key" field, NOT a lookup field). The confusion you are
experiencing is due to the use of the lookup datatype field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[DMH]

I got it Jeff thanks.

I'm not sure how to say it differently. STOP using the lookup field in your
table! Convert it to the datatype of the underlying ID.

Then do as you've described, making the rowsource the lookup table (or
better still, a query based on that table), and the control source the field
(a "foreign key" field, NOT a lookup field). The confusion you are
experiencing is due to the use of the lookup datatype field.

Regards

Jeff Boyce
Microsoft Office/Access MVP