How to Copy a Product Price into an Editable Access Order Subform
Question details
The user needs to automatically populate a product's price into an order detail record upon selection, while still allowing the price to be manually edited and permanently stored in the order table.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Creating or updating an order entry form where product prices need to be recorded historically rather than calculated dynamically.
- Observed behavior
- The product price is successfully copied from the product table to the order details table and displayed in an editable subform control.
Ensure your database design includes separate fields for the standard product price in your Products table and the final selling price in your Order Details table.
Use the Combo Box After Update Event
This is the most efficient and recommended method. It copies the price by referencing a column within the product selection combo box.
By including the product price in the combo box's Row Source query, you avoid making an extra call to the database. The value is pulled directly from the loaded combo box data.
Open your Order Details subform in Design View and select the Product ID combo box.
Open the Property Sheet (F4). On the Data tab, ensure the Row Source query includes the Product Price field as one of its columns.
Navigate to the Event tab in the Property Sheet, click on 'After Update', choose 'Code Builder', and click OK.
Enter the following VBA code: Me.UnitPrice = Me.ItemID.Column(1). Replace 'UnitPrice' and 'ItemID' with your actual control names.
Save the VBA module, switch the form back to Form View, and select a product to verify that the price copies over and remains editable.

Use the DLookup Function
An alternative approach if you prefer not to include the price field in the combo box columns.
Looking for a Free Office Suite? Try WPS Office
While Microsoft Access is a specialized database tool, your everyday office tasks like document editing, spreadsheet data analysis, and presentations can be seamlessly handled by WPS Office. It is highly compatible with Microsoft Word, Excel, and PowerPoint formats while remaining lightweight and completely free to use.
- 1. Download the Installer: Visit the official WPS Office website and download the free installation package for your operating system.
- 2. Install WPS Office: Run the downloaded installer and follow the on-screen prompts to complete the setup in minutes.
- 3. Open Office Files: Launch WPS Office and instantly open your existing Word documents, Excel spreadsheets, and PowerPoint presentations.

Frequently Asked Questions
Why is the price updating dynamically for all past orders instead of saving?
This happens if your order subform is displaying the price directly from a related query rather than storing it in a bound control on the Order Details table. Ensure your Unit Price text box is bound to the SellingPrice field in the Order Details table.
Can I manually change the copied price after selecting a product?
Yes. Because the combo box After Update event only sets the value initially and the control is bound to a field in your table, users can type over the auto-populated value to override it without affecting the original Product table.
Why does Column(1) return the wrong data or an empty value?
The Column property in Microsoft Access is zero-based. If you want to extract the second column in your combo box's row source, you must use Column(1). Check your combo box's Row Source query to confirm exactly which column contains the price data.




