ACC2000: How to Use DLookup to Look Up Values in Forms or Reports

ID: Q209743

The information in this article applies to:

Microsoft Access 2000

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).

SUMMARY

You can display multiple fields in a combo box or a list box on a form or a report even when those fields come from a table that is not bound to the form or report. You can also update controls, such as text boxes, with new information based on what a user selects from a combo box or a list box.

To accomplish these objectives, you could use one of the following techniques:

Use AutoLookup in forms.

Use multiple DLookup() functions in forms and reports.

Use the Column property of a multiple-column combo box to update a text box control with new information as focus moves from row to row in the combo box.

This article shows you how to use the DLookup() function to look up values in forms or reports.

MORE INFORMATION

The syntax for the DLookup() function is a follows:

DLookup(expr, domain, [criteria])

The DLookup() function returns a value from a field in a specified set of records called the domain. The DLookup() function specifies the criteria for the domain. To make the domain dependent on one or more values listed in controls on a form or report, refer to the controls in the DLookup() criteria argument.

The following example looks in the Employees table (the domain) and returns the Last Name of the Employee ID specified in the [ControlName] control on the form.

NOTE: In the following example, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this example.


   =DLookup("[LastName]", "Employees", "[EmployeeID] = " & _ 
   Forms![FormName]![ControlName])

DLookup() returns one value from a single field even if more than one record satisfies the criteria. If no record satisfies the criteria or if the domain contains no records, DLookup() returns Null.

REFERENCES

For additional information about the DLookup() function, please see the following article in the Microsoft Knowledge Base:

Q208786 ACC2000: DLookup() Usage, Examples, and Troubleshooting

For more information about the DLookup() function, click Microsoft Access Help on the Help menu, type "DLookup function" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: inf functions


Keywords          : kbdta FmsHowto 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: May 25, 1999