Assign a control a value from a table

Use the DLookup function to display the value of a field that is not in the record source for your form or report. For example, suppose you have a form based on an Order Details table. The form displays the OrderID, ProductID, UnitPrice, Quantity, and Discount fields. However, the UnitPrice field is in another table: Products. You could use the DLookup function in a calculated control to display the UnitPrice on the same form when the user selects a product.

The following example populates the UnitPrice text box with the price of the product currently selected in the ProductID combo box.

Private Sub ProductID_AfterUpdate() 
 
 ' Evaluate filter before it is passed to DLookup function. 
 strFilter = "ProductID = " & Me!ProductID 
 
 ' Look up product's unit price and assign it to the UnitPrice control. 
 Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter) 
 
End Sub

The DLookup function has three arguments. The first specifies the field you are looking up (UnitPrice); the second specifies the table (Products); and the third specifies which value to find (the value for the record where the ProductID is the same as the ProductID on the current record in the Orders subform).

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.