the selection from combo box prints ID column instead of alpha dat

D

dbuser

I have created a combo box from table. Created query to select ID and column
containing title information. Use input form, combo box is choice to select
a code for potential candidate. When looking at table that data is input to;
noticed combo box data is the "ID" column number - not the column information
desired. Any help? Thanks very much in advance
 
J

John W. Vinson

I have created a combo box from table. Created query to select ID and column
containing title information. Use input form, combo box is choice to select
a code for potential candidate. When looking at table that data is input to;
noticed combo box data is the "ID" column number - not the column information
desired. Any help? Thanks very much in advance

It's doing *exactly what it should be doing*. You want to store the ID in the
table.

If you want to see the text related to that ID on a Form use a combo box on
the form, bound to the ID field but displaying the text. If you want to see it
on a Report, it's better to base the report on a Query joining your main table
to the title information table.
 
K

ken

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top