Override Product Prices in a Microsoft Access Invoice Form
Question details
Copy the current product price into an editable invoice detail field when a product is selected.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A sales database needs to preserve the price charged on each invoice even when a product's current retail price changes later.
- Observed behavior
- Each invoice detail record should store its own sale price, initially populated from the selected product and editable for that sale.
Confirm that the invoice detail table has a sale-price field and that the form's sale-price control is bound to it. Identify the product combo box and the retail-price field in its row source.
Copy the Product Price with the Combo Box After Update Event
Use the product selection to populate an editable sale-price control while storing the price on the invoice detail record.
Open the invoice detail form in Design View, select the product combo box, and open its Property Sheet. On the Data tab, edit Row Source to include the product's retail-price field alongside the product ID and any display fields.
In the combo box's Row Source, note the order of the selected columns. Access numbers combo box columns from zero: if retail price is the third column, its index is 2. On the Format tab, ensure Column Count includes the price column; you can give it a width of 0 to hide it.
With the combo box selected, open the Property Sheet's Event tab. Set After Update to [Event Procedure], then click the build button (...) to open the VBA editor.
Inside the generated AfterUpdate procedure, enter `Me.[Sale Price] = Me.ProductID.Column(2)`. Replace `ProductID` and `Sale Price` with your control names and replace `2` with the retail-price column's zero-based index.
Save the form, switch to Form View, and select a product on a new invoice detail record. Confirm the sale price appears, change it if needed, and save the record. Change the product's retail price and verify that the saved invoice detail retains its original price.

Manage Invoice Worksheets with WPS Office
WPS Office is a free, lightweight alternative for working with invoice spreadsheets and other Office files. Its familiar interface and Microsoft Office format compatibility make it easy to move existing files into WPS. It does not replace Access forms or fix the Access database event.

Frequently Asked Questions
Why does the code use Column(2) for the retail price?
Access combo box columns are zero-based, so Column(2) refers to the third column in the Row Source. Use the index that matches the position of your retail-price field.
Can I override the price after selecting a product?
Yes. Keep the sale-price control editable and bound to the invoice detail table's sale-price field. After selecting the product in Form View, enter the price you want to charge.
Will old invoices change when the product's retail price changes?
No, provided the sale price is stored in the invoice detail record rather than displayed only from the product table. The saved invoice price remains separate from the current retail price.
Where do I add the After Update code?
In form Design View, select the product combo box. On the Property Sheet's Event tab, set After Update to [Event Procedure] and click the build button (...) to open the VBA procedure.




