How do you get the name of a field from a linked table?

[Guest]

What is the easiest way to get the name or description of a field onto a form
if there are 3 tables, where table3 has the descriptions for both? I only
want to save the MainID1 and DetailID2 if they change. The description is
only for display purposes.

Table1 contains the MainID1 field-i.e. AA Or AB
Table2 contains the DetailID2 field-i.e. AA Or AB
Table3 contains the ID field, description - this description corresponds to
both MainID1 and MainID2. (i.e. AA=Autos, AB=Bank, etc.)

Thank you!!

 

[Rob Oldfield]

The question is a bit confusing. Is AA a field name or a piece of data
within a field? What table is linked? Are you just saying that AA is a
piece of data (that might appear in two different tables) and you just want
to get at a description of that piece of data from within a third table?

 

[Guest]

AA is just a sample of what the data is like.

The question is a bit confusing. Is AA a field name or a piece of data
within a field? What table is linked? Are you just saying that AA is a
piece of data (that might appear in two different tables) and you just want
to get at a description of that piece of data from within a third table?

 

[Rob Oldfield]

In that case you can just add function...

function descrip(t as string) as string
descrip=dlookup("[Description]","Table3","xxx='"+t+"'")
end function

where xxx is the name of the field containing AA in Table3, and just use
that anywhere.

 

[Marshall Barton]

What is the easiest way to get the name or description of a field onto a form
if there are 3 tables, where table3 has the descriptions for both? I only
want to save the MainID1 and DetailID2 if they change. The description is
only for display purposes.

Table1 contains the MainID1 field-i.e. AA Or AB
Table2 contains the DetailID2 field-i.e. AA Or AB
Table3 contains the ID field, description - this description corresponds to
both MainID1 and MainID2. (i.e. AA=Autos, AB=Bank, etc.)

I would need to see the form's record source query to be
sure, but there should be a way to Join the third table so
the description can be included in the query's dataset.


Maybe something like:

SELECT table1.MainID1,
table2.DetailID2,
table3,Description
FROM table1 LEFT JOIN (table2
LEFT JOIN table3
ON table2.DateilID2 = table3.ID)
ON table1.MainID1 = table2.DetailID2