Preserve Historical Product Prices in Microsoft Access Orders
Question details
Store the price charged for each product on an order so it remains unchanged when the current product price changes.

- 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.
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.
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.
Open the order-details table in Design View. Add a field named UnitPrice with the Currency data type, then save the table.
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.
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.
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
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.

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.

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.




