Is it possible to have a lookup field that stores a value other than the primary key?

[Bazsl]

Is it possible to define a lookup field that stores a value other than the primary key of the lookup table? The lookup wizard allows me to pick the field I want to store in the database but no matter which field I pick the value stored in the table is always the primary key of the lookup table.



The lookup table in this case has two columns; Location and Description. Location is the primary key. Description has a unique index and is the field I want to store in the table. Thanks.

[ssanfu]

You could try changing the Bound column to a different number.
Or, in the look up field, move "Description" to be the first column (the bound column).

"Description" is a reserved word and shouldn't be used as object names.


Using a Look up FIELD (in a table) is the wrong think to do. (IMO)
I never, ever use look up FIELDs (in a table). (Look up tables are a different animal)

See
http://access.mvps.org/access/tencommandments.htm
and
http://access.mvps.org/access/lookupfields.htm

[ranman256]

you can do 2 Dlookups
1 to get the key,
then 2 to get the other item using the key

or

put them in a combo box,with both columns
select 1 item and pull the other column value....

sub cboBox_afterupdate
  txtBox = cboBox.column(2)
end sub

[Bazsl]

Steve,

Thanks for your reply. I am accustomed to databases where data integrity enforcement and manipulation is done on the back end using stored procedures and triggers so Access is a major conceptual shift. The reason I want to use lookup fields in a table is so we can start data entry. We have about 10,000 items that must be manually entered into one table. Getting the forms designed and learning enough VBA to make them work is going to take awhile.

[ssanfu]

We have about 10,000 items that must be manually entered into one table

Ugh.....what a nightmare!

Have you thought of creating a CSV file and importing the data?

[Bazsl]

Not really. Whatever the disadvantages of working directly with tables may be I can have some control of the integrity of the data being entered with referential integrity and lookup fields. At least it is better than having unskilled volunteers keying the data into a spreadsheet with no validation at all. This is a small app for a very small not-for-profit that my daughter works with. I am just an old retired programmer who "volunteered" to help out from a thousand miles away.