That's how it should be. In a referencing table, i.e. one on the
'many' side of a one-to-many relationship a foreign key column
references the primary key of the referenced (one-side) table. In a
case like this the foreign key column is therefore a number data type
which corresponds to the numeric primary key of the referenced table.
The combo box's RowSource will return two columns, the ID column and
the text column, but by virtue of the control's ColumnWidths property
being set to something like 0cm;8cm (or equivalent in inches) the
first column is hidden (by being zero width) so you see the second
text column. The value of the control when a n item is selected,
however, is that of the hidden first column. The value is governed by
the control's BoundColumn property, which is by default 1.
As well as seeing the text value by means of a combo box like this you
can also see it by joining the referenced and referencing tables in a
query and returning the text column from the referenced table. This
is how its normally done in a report where a text box is then bound to
the text column, whereas in a form a combo box set up as described
above would more often than not be used.
If the dreaded 'lookup wizard' is used when setting the data type of a
column in table design it uses a combo box in this way as the columns
'display control'. However, it has unfortunate other consequences,
which you'll find detailed at:
http://www.mvps.org/access/lookupfields.htm
For this reason this feature should be avoided like the plague. You
can always hand-craft a combo box to act in this way in a form or use
the control wizard to set it up when designing a form. There is
nothing to be gained and much to be lost doing it in a table's
datasheet view, which should never be exposed to users in any case.
Ken Sheridan
Stafford, England