Others

How to Use DLookup to Display a Product Price in an Access Form

Camila MilosovichCamila Milosovich Nov 3, 2026 870 views

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.

How to Use DLookup to Display a Product Price in an Access Form
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.
Before you start

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.

Solution 1Recommended

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.

1
Update the Combo Box Row Source

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.

2
Bind the Product ID

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.

3
Add the After Update VBA Code

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).

Use the Combo Box After Update Event (Recommended)
Column Indexing: VBA combo box columns are zero-indexed. Make sure you adjust the Column(n) number to match the actual position of your price column.
Free Microsoft Office alternative

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. 1. Visit the Official Website: Go to wps.com to download the latest free version of WPS Office.
  2. 2. Install the Software: Run the lightweight installer and follow the on-screen prompts to set up the suite on your computer.
  3. 3. Open Your Office Files: Instantly open, edit, and save your existing Word, Excel, and PowerPoint files without any formatting loss.
Fully compatible with Microsoft Excel, Word, and PowerPoint formatsLightweight installation with incredibly fast startup timesFree to use with a familiar, easy-to-navigate tabbed interfaceSeamless migration of your existing Office documents
QA img-9

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.