How To Prevent Data Integrity From Changing Data In A saved Form Records

[ghallourim]

Hi


I Have A Purchase Invoice Form For Products I bought For My Shop, That Contains a WholSale Price Field, the wholesale price field is taken from A product table, the wholesale prices are changing all the time, so the problem is when I enter a new wholesale price in a new Purchase invoice records is changing in all the old purchase invoices records, so what I want is a way to can save or freeze the old records from changing.

[Micron]

Your current price field for items should reflect exactly that. The table that records the quantity sold/bought and the price should use the price at the time of the transaction. You don't link this field to the item's current price field, you store the value that was the price at the time, along with the quantity (in a separate field). Your form/report then calculates the line item cost by multiplying the qty by the price at the time; e.g. qty 5 x $100 = $500. It's key that you don't store the $500 value anywhere. If you apply discounts based on some condition such as quantity discounts there is likely more than one approach you can use, such as including the discount rate in the calculation.

You might want to review this for some tips
http://allenbrowne.com/AppInventory.html

[ghallourim]

Your current price field for items should reflect exactly that. The table that records the quantity sold/bought and the price should use the price at the time of the transaction. You don't link this field to the item's current price field, you store the value that was the price at the time, along with the quantity (in a separate field). Your form/report then calculates the line item cost by multiplying the qty by the price at the time; e.g. qty 5 x $100 = $500. It's key that you don't store the $500 value anywhere. If you apply discounts based on some condition such as quantity discounts there is likely more than one approach you can use, such as including the discount rate in the calculation.

You might want to review this for some tips
http://allenbrowne.com/AppInventory.html

this is not working with me because the wholesale price have a lot of relationship with other form and queries And I want to Keep That

[Micron]

Well, I have no idea what that looks like so I guess I'll have to defer to the wisdom of other responders. You might find that you'll have to elaborate on what those constraints are in order to get focused help.

[orange]

As has been suggested, readers need to see more of your database. Your table designs, a description of the business/processes involved.You may get some insight from this post.

If you search the forum with "AgreedTo", you'll get a few posts/threads that deal with issues of History of Prices. Product prices change, so you can not rely on the Product price in the Product table to be constant/reliable with time.

We really need to see more of your requirement in business terms.
Good luck.