Updating product price

[Abdulaym]

Hi, trust you are all doing great?
I noticed that when I update my product price to a new price the old price which I have already issued invoices for also change to the new price. e.g I sold bag for $10 a week ago and now the price increase to $12. I want the one I sold for $10 to remain as $10 and the new sales to be $12.
Thanks for your usual assistance

[Bob Fitz]

You should have a field in the sales detail table to store the price of each sale.
On the sales detail form you should have a combo based on the products table.
Use the after update event of the combo to enter the current price into the price on the form.

[Abdulaym]

Sir how do I Use the after update event of the combo to enter the current price into the price on the form. I mean the code sir.

[Welshgasman]

It is simple value asignment?

[Bob Fitz]

Can you post a copy of the db

[mike60smart]

In tblSales you need to add a field "CostPrice"
Then on your Form with the Combobox you use to select the Product.
The Combobox Record Source will include the Cost Price

SELECT tblProducts.Product_ID, tblProducts.Product_Name, tblProducts.Unit_CostFROM tblProducts
ORDER BY tblProducts.Product_Name;

Then in the After Update of the Combobox you would have the following AfterUpdate Event

Me.CostPrice = Me.cboProducts.Column(2)

[Gicu]

Here is some reading material for you:
https://answers.microsoft.com/en-us/...e-ad51607cc936
https://www.access-programmers.co.uk...ecords.248750/
Cheers,

[Abdulaym]

Thanks for the response sir, am new in Access, pls how do I enter the first code sir? And how do I include the Cost Price in the code? Thanks

[Welshgasman]

Thanks for the response sir, am new in Access, pls how do I enter the first code sir? And how do I include the Cost Price in the code? Thanks

And seemingly not prepared to learn?

Here is code from my diabetes DB.
This set values for other fields in the record.
You would just use the Cost field as another value to store.

    Me.txtCarbsCalc = Me.cboFoodIDFK.Column(4) * Me.txtFactor
    Me.txtSugarCalc = Me.cboFoodIDFK.Column(5) * Me.txtFactor
    Me.txtFibreCalc = Me.cboFoodIDFK.Column(6) * Me.txtFactor

Column 4 is carbs, 5 is sugar and 6 is Fibre. The txtControls are bound to fields in the record.

You will likely need to the same for the tax rate.

[Abdulaym]

Good day sir, this was the message I got when I enter the first code: Check the subquery's syntax and enclose the query in parentheses.

[Welshgasman]

Good day sir, this was the message I got when I enter the first code: Check the subquery's syntax and enclose the query in parentheses.

Seriously?
What good is that statement to anyone?
At least show what code you have used and where you have used it.

Where is a subquery coming into it?, it is a simple value assignment.
You assign the values in the AfterUpdate of the product control.

You might be better off buying an off the shelf system?
This is not a small task you are trying to undertake and if you need it in a short space of time, I can not see that happening.

[Abdulaym]

@Bob Fitz: sir where will I enter this code sir?is't in the control source of CostPrice or in the Combo that gives me the product?

SELECT tblProducts.Product_ID, tblProducts.Product_Name, tblProducts.Unit_CostFROM tblProducts
ORDER BY tblProducts.Product_Name;

[Bob Fitz]

@Bob Fitz: sir where will I enter this code sir?is't in the control source of CostPrice or in the Combo that gives me the product?

SELECT tblProducts.Product_ID, tblProducts.Product_Name, tblProducts.Unit_CostFROM tblProducts
ORDER BY tblProducts.Product_Name;

In post #5 I asked you

Can you post a copy of the db

If you post the db we may be able to make the required changes for you