How to Use DLookup to Display a Product Price in an Access Form
Question details
The user wants to automatically display the correct order unit price in an Access form when a product is selected from a combo box, and permanently store that price in the sales record.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Creating or modifying an order entry form where selecting a product from a drop-down list populates its current price into an Order Unit Price field.
- Observed behavior
- Setting the price using a DLookup formula in the Default Value property only applies to new records and does not refresh when the product selection is changed.
Ensure your form is bound to an Order Details table and that your Product combo box pulls data from a Products table containing the current unit prices.
Use the Combo Box After Update Event (Recommended)
Using VBA in the After Update event is more efficient than DLookup and allows you to easily store the historical price at the exact time of the sale.
Instead of using DLookup, a best practice in Access is to include the unit price directly in the row source of your product combo box. You can then use a simple VBA command to push that price into your bound order field whenever the combo box selection changes. This method guarantees that historical prices are preserved even if the base product price is modified later.
Open your form in Design View, select the SalesProductName combo box, open the Property Sheet, and click the Data tab. Ensure the Row Source query includes the ProductID, ProductName, and UnitPrice columns.
Still on the Data tab, set the Bound Column to the one containing your ProductID (usually 1) to ensure the database correctly links the product.
Go to the Event tab, click the ellipsis (...) next to 'After Update', and choose Code Builder. Enter the following code to push the price into your field: Me.OrderUnitPrice = Me.SalesProductName.Column(3).

Correct the DLookup Expression Syntax
If you must use DLookup as the control source for the unit price field, you need to properly concatenate the combo box value into the criteria string.
Looking for a Free, Lightweight Office Suite?
While Microsoft Access manages your complex databases, WPS Office is the perfect lightweight alternative for all your document, spreadsheet, and presentation needs. Enjoy full compatibility without the heavy subscription costs.
- 1. Visit the Official Website: Go to wps.com to download the latest free version of WPS Office.
- 2. Install the Software: Run the lightweight installer and follow the on-screen prompts to set up the suite on your computer.
- 3. Open Your Office Files: Instantly open, edit, and save your existing Word, Excel, and PowerPoint files without any formatting loss.

Frequently Asked Questions
Why doesn't the Default Value property update when I change the product in the combo box?
In Microsoft Access, a control's Default Value property is only evaluated once when a completely new record is initialized. It does not act as a dynamic formula and will not refresh if you change related field values later.
Why is it important to store the historical price in the order table?
Product prices fluctuate over time. If your form only dynamically references the current price in the main Products table, all past orders will retroactively show the new price when it changes. Storing the price at the time of the sale ensures your historical revenue and order records remain perfectly accurate.
How do I reference a specific column in an Access combo box using VBA?
You can reference specific columns using the .Column(index) property of the combo box. Note that VBA uses zero-based indexing for columns, meaning .Column(0) refers to the first column, .Column(1) is the second column, and so on.




