Others

How to Copy a Product Price into an Editable Access Order Subform

Tauseeq MagsiTauseeq Magsi Nov 3, 2026 868 views

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.

How to Copy a Product Price into an Editable Access Order Subform
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.
Before you start

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.

Solution 1Recommended

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.

1
Open form in Design View

Open your Order Details subform in Design View and select the Product ID combo box.

2
Update Row Source

Open the Property Sheet (F4). On the Data tab, ensure the Row Source query includes the Product Price field as one of its columns.

3
Add VBA Event Code

Navigate to the Event tab in the Property Sheet, click on 'After Update', choose 'Code Builder', and click OK.

4
Write the assignment code

Enter the following VBA code: Me.UnitPrice = Me.ItemID.Column(1). Replace 'UnitPrice' and 'ItemID' with your actual control names.

5
Save and Test

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 Combo Box After Update Event
Zero-Based Indexing: The Column property in Microsoft Access is zero-based. This means Column(0) refers to the first column, and Column(1) refers to the second column in your Row Source query.
Free Microsoft Office alternative

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. 1. Download the Installer: Visit the official WPS Office website and download the free installation package for your operating system.
  2. 2. Install WPS Office: Run the downloaded installer and follow the on-screen prompts to complete the setup in minutes.
  3. 3. Open Office Files: Launch WPS Office and instantly open your existing Word documents, Excel spreadsheets, and PowerPoint presentations.
Highly compatible with Microsoft Excel (.xlsx) formats for managing flat-file data and order lists.Lightweight architecture runs smoothly on Windows, Mac, and Linux.Familiar user interface makes switching easy with zero learning curve.Built-in PDF editing, creation, and conversion tools.
microsoft office alternative - wps office

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.