Dlookup Example
Access DLookup: Get the value of a field from a particular
record
Dlookup is one of the best tools for creating user friendly Access forms.
Make use
of Lookup Lists where ever possible to provide a consistent and efficient method
for data entry. The downside of this technique is
that some queries can get really complicated with a dozen or more lookup
tables in the query design grid.
If you have a Microsoft Access database setup as a
frontend/backend for multi user environments then, ideally, you should place
static lookup lists in the front end. This will reduce network traffic and
won't hinder other users when you use lots of Dlookups or you use the
Dlookup function against a large table. In the cases where you can't
put the look up lists in the front end because they change frequently then
the domain lookup or dynamic lookup function should not be used.
Instead, lookup the value in the query for the form or report using a
regular join to the lookup table (not a dlookup in the query).
Here's the table (Table L_Emps) setup for our Access Dlookup/Vlookup example:
Employee_ID
First_Name
Last_Name
Suppose we have a query that might include records from
employee time table and maybe some other tables and the query doesn't need
to join to the L_emps table in order to get the main results that you
want. But in the form or report you want to show the employee's name
somewhere. Here's our first example show how to get the employee's
last name:
Create an unbound text field on the form or report.
In the Control Source for this unbound field enter the following
=dlookup ("Last_Name","L_Emps","Emp_ID=" &
Employee_ID)
The Dlookup has 4 parameters: Dlookup(1,2,3,4):
-
1 - The name of the field containing the data you want
to retrieve
-
2 - The name of the table or query that contains the
data
-
3 - The field (unique index or primary key) that will
be used to retrieve the distinct value for parameter 1. Note the equals
sign at the end of this field.
-
4 - The value of the index or primary key field in 3
above
The result will be the person's last name that matches
the current Employee_ID field.
Multiple Dlookup Example for One Field
I bet you're saying.. that's great but I need the first
and last names for the person! Here's how that is done - with
multiple dlookups in one field Control Source. Here we will show Last
Name, First Name
=dlookup("Last_Name","L_Emps","Employee_ID=" &
employee_ID) & ", " & dlookup("First_Name","L_Emps","Employee_ID=" &
employee_ID)
The above code all goes on one line in the Control
Source.
Compound Lookup Example for One Report or Form Field
Here's another twist.. suppose the following table setup:
Employee_ID
First_Name
Last_Name
Department_ID
Here we have a foreign key (Department_ID) which points
to the department lookup list for which this employee works. In order
to display the department on the form or report we basically need to do two
dlookup functions in one. Here's the Access Dlookup function example query code:
= Dlookup ("Department","L_Departments","Department_ID=" &
Dlookup ("Department_ID","L_Emps","Employee_ID=" & employee_ID))
Note again that both of the above lines should be typed
into one line in the Control Source for the unbound field.
More Dlookup Examples:
| GBL_Access_Level = Nz( DLookup ("Access_Level",
"L_Employees", "Username='" & Nz(Me. ... GBL_employee_ID = Nz(DLookup("Employee_ID",
"L_Employees", |
| If Nz( DLookup ("Emp_Name", "Employees",
"Emp_Name='" & _ Me.Emp_Name & "'"), "zzzz") <> "zzzz" Then MsgBox & _ "That
name already exists in the employee |
| pitney = DLookup ("Path", "Q_path_Pitney",
1 = 1) pitney = Replace(pitney, "/", "\") pitney = Replace(pitney & "\",
"\\", "\") |
