Combo choices to show on table

[Guest]

Hi thanks in advance...
Basically, I am using a form to "build" a database.

I have various combo boxes on the form that get their menu items from my
"menu options table", and then "send" the data to my "MAIN DATA" table.

Things seem to be working fine, except for the fact that on the main data,
the user choices from the combo menus show up as numbers vs. the actual text
choice. So for instance if the choices are red, blue, and white, and the
user chooses blue, the entry in the table is a "2". Not a huge problem, of
course, since it's consistent throughout, but I'm curious,

Is there's a way to make the exact choices appear on the form?

(P.S., I experimented with changing field settings in table design view, and
I was able to get this, but then there was a drop down arrow on my table as
well...don't want this)

Thanks so much!

 

[Guest]

Stilla,

Your post touches on two oft-discussed issues, namely, database
normalization and the difference between what is *stored* in a table *field*,
and what is *displayed* in a form *control*.

In a propertly normalized relational database, in all but very special
cases, the only field that should be duplicated from one table to another is
the first table's primary key, called a foreign key in the second table.
This foreign key fully identifies the proper record in the first table.

However, as you note, it's often desirable to display more meaningful text
on a form or in a report. One way to do this is to create a query linking
the two tables by the common field, and including the text description from
the first table in the recordset. Base the form or report on the query, and
you can place this field just as if it were in a table.

A second way is to include the text field in the combo box' RowSource query,
set the BoundColumn to 1 to *store* the numeric foreign key, and set the
first ColumnWidth to 0" so that the 2nd field in the list is displayed after
the selection is made. The combo's properties would be something like:

ControlSource: ColorID (integer--the foreign key corresponding to the PK
of Colors)
RowSource: SELECT Colors.ColorID, Colors.Color FROM Colors ORDER BY
Colors.Color
ColumnWidths: 0";1"

Hope that helps.
Sprinks

 

[Guest]

Thank you Sprinks..

What I ended up doing is opening the relationship window and creating a
relationship b/w the field in the main data table and the corresponding field
in the table where my options are stored. It looks like it's working - still
get the drop down menus on the table itself, but i guess I'll contend with
these.

Thanks.

 

[Guest]

Stilla,

Setting the Relationship will not affect what is stored or displayed.

I can help you display what you want. Open the form in design view, select
your combo box and press <F4> to display the Properties window. From the
Data tab, please post the following combo box properties:

ControlSource
RowSourceType
RowSource
BoundColumn

From the Format tab, please post:

ColumnCount
ColumnWidths

Also, name the field in your Colors table that has the text description that
you wish to display.

Sprinks