Carry the field

[Striker3070]

Access 2007, have a table that looks up two fields in another table. Fld1
and Fld2. Fld2 is a description of Fld1. So when the user select Fld1, how
can I get Fld2 to carry on to this new table?

 

[Gina Whipp]

Striker3070,

In a well normalized database you would not store the description in two
tables. You can use DLookup to get the description to display on your form.

If numeric...

=DLookup("Fld2", "YourTable", "[Fld1]=" & Me![Fld1])

If text...

=DLookup("Fld2", "YourTable", "[Fld1]='" & Me![Fld1] & "'")

OR drop the table in the query and display it as part of the query that is
the RecordSource of the form.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[John W. Vinson]

Access 2007, have a table that looks up two fields in another table. Fld1
and Fld2. Fld2 is a description of Fld1. So when the user select Fld1, how
can I get Fld2 to carry on to this new table?

You wouldn't, ordinarily.

The whole POINT of a relational database is to avoid redundnancy, thereby
avoiding redundancy. Fld2 should exist in the first table *and noplace else*!
Any time you need the information you can use a Query joining the tables, or a
DLookUp as Gina suggests, or a Combo Box storing Fld1 and displaying Fld2.

Storing the description in two different tables wastes space and (worse) risks
data anomalies: if you have the description stored in two tables, it could be
(intentionally, accidentally, even maliciously) edited in one table and not in
the other. You now have two incompatible descriptions for the same thing!