Compound Dlookup Example
Microsoft Access Nested DLookup Domain Aggregate Function
This example shows how you can use nested Dlookup functions
to get nested table results in a relational hierarchy.
What this example shows is multiple Dlookup functions within one.
Here are the tables in this example:
We will have a query based only on the M_Customer_Orders
SELECT M_Customer_Orders.Customer_ID, M_Customer_Orders.Product_ID,
Did you know that most template databases often do not do exactly what you need done? We can create a custom template that fits your needs exactly.
The form design is as follows:
The control source compound dlookup code is much longer than can be
displayed in design mode of the form. I use notepad to construct the
code and the copy and paste it into the control source. Here is how
the code looks when in notepad:
Dlookup("Org_ID","M_Customers","Customer_ID=" & nz(Customer_ID,0))))
You can see from the code that we work our way from left to right starting with the end
result and working are way down to the ID field available to us on the form
(Customer_ID). With the customer_ID we can obtain the Org_ID from the
M_Customers table. Then, we get the Contact_Method_ID for that
customer from the L_Orgs table.
Easy isn't it! Below is the final result: