Product price update

[Michael Miller]

I need some assistance as I am new to access.
I have a product table which over time the price of
product will rise and fall.
When I change the price to the current price I have found
that all my order totals have changed.Is there a way to
lock the order and order detail tables or am I on the
wrong track.

 

[Al Camp]

Michael,
Given the usual Price * Qty = LineTotal scenario, you'll need to
"capture" price at the time the record is created. You can look it up in a
price list, but it has to be saved, so subsequent price changes will not
affect the calculation.
I suspect your Price field is calculated now, (via =Dlookup?). Get rid
of the calculation, and "bind" the control to a Price field added to your
(ex.) Sales table.
When you enter a PartNo on your form (or other unique product
identifier)....
Private Sub PartNo AfterUpdate()
[Price] = Dlookup(your price Dlookup syntax)
End Sub

That will store the Price, and always reflect the original Price, even after
subsequent price changes in the future.

 

[DanK]

What you've done is link the price to the invoice through
the item's table. So... when you change the price, all
your old invoices also change. Not so good eh!?

Try doing this...

Have the invoice table FILL IN the info from the item's
table through DLookup or other means. You have to have
the actual info replicated in the invoice table so that is
remains the same no matter what else is changed. Then...
if an item's description changes, all of you old invoices
will have the old description while all the new ones will
have the new description, or price, or whatever. Sorry,
you'll have to duplicate some data here. That's the price
to be paid for freedom.

 

[Michael Miller]

Thank you very much........seems so clear now.
Thanks again