Form - Looking up ID Number to Field Name

[graycam]

Hi:

How do I NOT have a form display an ID #, but rather show it's actual
value.

For example, if I have two tables:

tblPeople
peopleID
Name
colorID

tblColor
colorID
colorName

colorID in people table is a foreign key to primary key in color table.

Rather than show that Joe's fav color is 1 and Mary's is 2, I want it
to lookup that Joe's fav color is Blue (which is colorID 1 in the color
table) and Mary's fav color is red.

Whenever I try this in a form, it just shows up that Joe's fav color is
1 and Mary's is 2.

What's wrong? How do I get the form to lookup & display that colorID 1
= blue and 2 = red?

Thanks.

Cam

 

[Steve Schapel]

Cam,

There are a couple of issues here. The first one is why have you got a
ColorID field in the tblColor table anyway. The only purpose that I can
see would be to provide uniqueness to the records. But you won't have
any duplication in the data in the ColorName field anyway, will you? So
the ColorName field will already provide uniqueness. Personally, I
would remove the ColorID field from this table, right now, and use the
ColorName field as the linking field to the People's favorite color.
And this will solve your problem straight away.

However, if you decide to stick with your existing table design, there
are a few ways you can go about this on the form. One is to represent
the ColorID field on the People form with a combobox. The Row Source of
the combobox will be the tblColor table. Set the Column Count property
of the combobox to 2, Bound Column to 1, and Column Widths to 0;x (where
x is whatever the amount to properly display the colors in the combobox.
This way, the numerical id for the color will be what is stored in the
table, but the ColorName is what will be shown.

By the way, as an aside, the word 'name' is a Reserved Word (i.e. has a
special meaning) in Access, and as such should not be used as the name
of a field or control.