Table LookUp

[David Chambers]

I have created a field on a form that is a combo box table look up. When I
am in the form and click on the drop down button it displays the records in
the table the field refers to, however when I try to select that particular
record, it only stores the first field in the table on the record selected.
In the properties menu I have checked to make sure multiple columns have been
selected and sized. However, it only returns the value of the one field, the
first one in the table. What can I do?

 

[John W. Vinson/MVP]

On Mon, 21 Jul 2008 07:42:05 -0700, David Chambers <David

I have created a field on a form that is a combo box table look up. When I
am in the form and click on the drop down button it displays the records in
the table the field refers to, however when I try to select that particular
record, it only stores the first field in the table on the record selected.
In the properties menu I have checked to make sure multiple columns have been
selected and sized. However, it only returns the value of the one field, the
first one in the table. What can I do?

The combo box is working exactly as it's designed to. A Combo
*displays* multiple fields but it *stores* only one, the Bound Column,
into the combo's Control Source property.

This is actually the desired behavior. Relational databases use the
"Grandmother's Pantry Principle" - "a place - ONE place! - for
everthing, everything in its place". The only field that you should be
copying from the combo's rowsource into the Form's recordsource is the
single foreign key field; you do not want to copy all of the other
fields, as they can be (and should be!) displayed using a Query or
other tools.

If you just want to see the values on the form (without storing them
in the table), you can put textboxes on the form with control sources
like

=comboboxname.Column(n)

where n is the zero based index of the field. For example, if the
fourth field in the combo's rowsource query is a phone number, you can
put a textbox labeled Phone with a control source

=comboboxname.Column(3)