Others

Preserve Historical Product Prices in Microsoft Access Orders

Steven ParkerSteven Parker Nov 18, 2026 868 views

Question details

Store the price charged for each product on an order so it remains unchanged when the current product price changes.

Preserve Historical Product Prices in Microsoft Access Orders
Product
Microsoft Access
Device & OS
not provided
Scenario
An order-entry form uses a product combo box in an order-details subform to select products for an order.
Observed behavior
Each order line should retain its sale-time price so the original invoice can be reproduced later.
Before you start

Identify the order-details table, the product combo box on its subform, and the field that stores the current product price. The example assumes the combo box is named ProductID and the product table contains ProductID, UnitPrice, and ProductName fields.

Solution 1Recommended

Copy the product price into each Access order-details record

Use the product combo box's After Update event to save the current price on the order line when a product is selected.

Store UnitPrice in the order-details table rather than the order header, because each order can contain multiple products with different prices. The saved value is a historical snapshot, separate from the current price in the Products table.

1
Add the order-line price field

Open the order-details table in Design View. Add a field named UnitPrice with the Currency data type, then save the table.

2
Configure the product combo box

Open the order-details subform in Design View and select the ProductID combo box. In its Property Sheet, set Row Source to SELECT ProductID, UnitPrice, ProductName FROM Products ORDER BY ProductName;. Set Column Count to 3 and Bound Column to 1. Adjust Column Widths to hide the ID and price columns if you want to display only the product name.

3
Add the price control

On the subform, add a text box and set its Control Source and Name to UnitPrice. This binds the displayed price to the UnitPrice field in the order-details record.

4
Add the After Update code

Select the ProductID combo box, open the Event tab in its Property Sheet, and choose [Event Procedure] for After Update. Click the builder button and enter this code in the event procedure: Private Sub ProductID_AfterUpdate() Me.UnitPrice = Me.ProductID.Column(1) End Sub

5
Test the saved price

Open the order form in Form View, select a product on a new order line, and move to another record to save it. Change the product's current price in the Products table, then reopen the order line and confirm its UnitPrice has not changed.

Copy the product price into each Access order-details record
Check the column order: Combo box column indexes are zero-based, so Column(1) reads the second column in the example Row Source. Adjust the index if your price column is in a different position. Selecting a different product on an existing line runs the event again and replaces that line's price.
Free Microsoft Office alternative

Track Order Prices in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface and support for common Office files, making it easy to move spreadsheet-based order tracking into WPS. It does not modify Access databases or run Access form events.

Record the price charged on each order line in a spreadsheetOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface for a straightforward transitionWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why should UnitPrice be stored in the order-details table?

An order can contain several products, each with its own price. Storing UnitPrice on each order-details record preserves the amount charged for that specific line.

Why does the code use Column(1) for the price?

Access combo box columns use zero-based indexes. With ProductID, UnitPrice, and ProductName in that Row Source order, Column(1) refers to UnitPrice. Use the index that matches your own Row Source.

Will changing a product's current price update old order lines?

No. Once the copied price is saved in the order-details record, changing the Products table price does not update it. Selecting a product again on an existing line can run the After Update event and replace the saved price.

Can I enter a different price for an order line?

Yes, if the UnitPrice text box is editable. After selecting the product, change the value in that box before saving the record. Selecting another product afterward will copy that product's current price into the box.

Will this add prices to existing order lines?

No. The After Update event runs when the product selection changes; adding the field and event does not automatically populate historical prices for existing records.