Why not just use the query builder, and base the datasheet on a query?
With eh query builder, you simply drop in the table you want to lookup to,
and draw a join line. If you have 4, or 5 fields that need to lookup, or
grab values from other tables, then just drop in those additional
tables..and again draw the join lines.
It is WAY easier to simply use the mouse to drop in tables..and then put the
fields and descriptions texts that are to be looked up into the query grid.
Further, often sometimes you need several fields from one table, and using
several dlooups is very messy to write and code, and it is VERY easy to make
a mistake in the syntax. (and, it is a zillion times slower in terms of
performance also).
So, you *can* use dlookup, and put a text box on the screen and go:
=dlookup("PartDescription","tblParts","[partID = " & [PartNumID])
The above would grab the field PartDescription from table tblParts, based on
the current records field of PartNumID that grabs the keyID from table parts
of partID.
However, the above is a lot of typing, and a lot of thinking on how to do
this. Why not just use the mouse..and build a query? It is so easy to do
this...nearly child's play as compared to building the above complex syntax.
So, using the mouse and the query builder is sooo much easer. You just droop
in your main table..and then start dropping in any additional table you need
to lookup to..and draw some join lines....what could be easier?
So, just build query..and drop in those tables, and then drop in any field
from those tables that you need to have into the query grid.
Just remember that as you drop in those additional tables and draw the join
lines...remember to make the joins "left" joins. That means you need to
double click on the join line..and select the option to allow any record
from the parent table...and maybe a child record. (if you don't do
this..then you will find that you can only drop in ONE table to use for the
lookups..and that is not much use). Further, often a child record is not
set, or the lookoup value may not be entered yet..and thus again if you use
a left join..the parent record will still appear in the list. So, that left
join option reads as:
Include all records from "Parent Table Name" and only those records from
"child table (lookup table name) goes here" where the joined fields are
equal.
