The DLookUp function allows the Access Developer to look up the value of a field from a table other than the form's actual Record Source. It is often used as a function in a Calculated Text Box Control (see previous post for more information about Calculated Controls). So applied to our scenario, we can use a Calculated Control containing the DLookUp function to obtain the item name from tblProducts (based on our knowledge of the product item's ID).
When we use the DLookUp function we need to provide it with three pieces of information (called parameters). These are:
- The Field Name
- The Table or Query Name
- The Criteria to find the particular record.
=DLookUp("itemName", "tblProducts", "ID = " & forms![frmOrderDetails]![ProductId])
"ID = " & forms![frmOrderDetails]![productId]