Others

Override Product Prices in a Microsoft Access Invoice Form

Ayan MasoodAyan Masood Nov 22, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Add the price to the combo box

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.

2
Check the column position

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.

3
Create the event procedure

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.

4
Copy the selected price

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.

5
Test and adjust the price

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.

Copy the Product Price with the Combo Box After Update Event
Selecting a product updates the price: The After Update event runs when the product selection changes. Selecting a different product will replace the current sale-price value, but changing the product's retail price later will not automatically update a saved invoice detail price.
Free Microsoft Office alternative

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.

Create and edit invoice worksheets in WPS SpreadsheetsOpen and save Microsoft Excel XLSX filesWork in a familiar, easy-to-use interfaceUse a free, lightweight office suite
microsoft office alternative - wps office

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.