Make a relationship table ID Field, a drop-down?

[Guest]

How would I get the drop-down to display the recognizable Name and input the
corresponding Key?

I've got a couple of tables, one for expenses, and one for payee
information. Rent Check -> Condo Asso. So I've got a one to many
relationship setup, where one or more expenses link to a single payee.

In the expense table, I have the foreign ID field linked to the primary key
in the payee table. Good so far.

The problem:
I’d like to make PayeeID field a drop-down box with a list of the payee’s
from the payee table. Which I can with the PayeeID data type => List Box and
Row Source Type => Table/Query. The problem seems to be with the Row Source.
I can make it list, in the Payee Table, either the Primary Key (a list of
numbers I don’t recognize) or the PayeeName (words that make sense). Having
it list words that make sense, is the only way to go, but when I select
“Condo†for example, it tries to put the text “Condo†in the field, not the
Primary Key, so I get the error “The value you entered isn’t valid for this
field†– “For example, you may have entered text in a numeric field or a
number that is larger than the FieldSize setting permits.â€

How would I get the drop-down to display the recognizable Name and input the
corresponding Key?

 

[Jeff Boyce]

Aaron

Given the group you posted in (tablesdbdesign) and your description, I'm
going to guess that you are trying to do this inside a table, rather than in
a form.

I'll further speculate that you are trying to use the "lookup" data type.
If so, this is what's causing your "problem". The Access lookup datatype
appears to be storing the text value, when in fact, it is storing the key
value of the looked up table.

This causes confusion when trying, say, to query your table, since your
natural inclination is to try to find the values you see (the text).

A scan of this 'group will reveal a strong consensus to NOT using the lookup
data type, replacing it with the long numeric (or other suitable data type
consistent with the key value in the looked up table). Another strong
recommendation is to NOT work directly in the tables, but rather, to use
forms.

If you create a form, based on your table, and add a combo box, with a row
source of the looked up table, you can see the text value in your form, but
you can store the key value in your primary table. The difference with this
from your situation is that you will KNOW that it is a key (not the text
description). And anyone using the database after you will not have to be
advised that this particular table has a potentially confusing lookup data
type!

 

[John Vinson]

You are correct, I'm working in the tables to edit and add entries in my
database. I've found it more intuitive to work with a large volume of fields
at once then being able to only view one at a time, but I'll try the forms
now again, and see if they're more helpful.

Note that a Form is NOT limited to viewing one record at a time!
Change the default view of the form from Single to Continuous and you
can see and edit multiple records onscreen.

John W. Vinson[MVP]
(no longer chatting for now)

 

[Guest]

Note that a Form is NOT limited to viewing one record at a time!
Change the default view of the form from Single to Continuous and you
can see and edit multiple records onscreen.

Thanks John, but I'm not sure where to change this setting, I've done help
searches for how to, but didn't find anything useful.

~Aaron

 

[John Vinson]

Thanks John, but I'm not sure where to change this setting, I've done help
searches for how to, but didn't find anything useful.

~Aaron

Open the Form in design view. Select View... Properties from the menu;
or right mouseclick the little square at the upper left intersection
of the rulers and select Properties. On the "All" tab the "Default
View" property is sixth from the top (in Access2002, it might be
different in other versions).

John W. Vinson[MVP]
(no longer chatting for now)