Use Dlookup In A Datasheet Form To Populate an Unbound Text Box From Another Table
For a simple example, we will create an Access database with only 2 tables:
(1) Products - This contains two columns: ItemId and Description
(2) Finders - This contains one column: ItemId.
The Products table contains
(1)
ItemID
(2) Description
The Finders table only contains one column: ItemID. Assume that there are 5000 products in the Product table, but the user only wants to select about 100 of those for a report. The Finders table contains ItemIDs for only the ones the user wants to report.
Occasionally, the user needs to update the finders table by adding or removing items. To do this, we create a form to handle this process. It will add new items and validate that the user entered the ItemID correctly by displaying the description. It is important to note that the Description is not a part of the forms's primary data source, and must be looked up.
(1) Create a form where the data source is the Finders table. It will only have one column so far.
(2) Add an unbound text box
(3) To populate the textbox, enter the following in the Control Source property for that text box:
=DLookUp("Description","Products","ItemID = '" & [Forms]![frmTest]![ItemID] & "'")
This syntax uses the ItemId entered by the user on the form, searches the Products table for a matching ItemID, and returns the Description to be populated in the text box.
Mo VBA is required to accomplish populating the text box: Just a modification of the property for the Text Box.
Depending on the data type to be returned, the syntax of the DLookup is different as shown below:
Syntax Examples
General: DLookup Usage Samples
Author
Dev Ashish
Note that the same logic applies to most Domain Aggregate Functions (DMax, DMin etc.)
Normal usage
For numerical values:
DLookup("FieldName" , "TableName" , "Criteria = n")
For strings: (note the apostrophe before and after the value)
DLookup("FieldName" , "TableName" , "Criteria= 'string'")
For dates:
DLookup("FieldName" , "TableName" , "Criteria= #date#")
Refering to a form control
For numerical values:
DLookup("FieldName", "TableName", "Criteria = " & forms!FormName!ControlName)
For strings: (note the apostrophe before and after the value)
DLookup("FieldName", "TableName", "Criteria = '" & forms!FormName!ControlName & "'")
dates:
DLookup("FieldName", "TableName", "Criteria = #" & forms!FormName!ControlName & "#")
Mix-n-Match
DLookup("FieldName", "TableName", "Criteria1 = " & Forms!FormName!Control1 _
& " AND Criteria2 = '" & Forms!FormName!Control2 & "'" _
& " AND Criteria3 =#" & Forms!FormName!Control3 & "#")