Changing Prices for next year?

D

Dustin

On my form I have a list box attached to a price table that puts in the price
when I select a certain sized purchased by the dealer. I am running a query
that adds up how much we grossed. But now in 07 we are increasing prices how
do I change prices in the table with out affecting last years numbers? Do I
need to make a 07 table and have it so I select which price table to use on
the form when the dealer purchases one?
 
K

Ken Sheridan

The price for each transaction needs to be stored with the transaction, not
merely looked up from the price table. That way the prices for the
transactions remain at the values at the times of the transaction, and are
not affected by subsequent price changes of products.

You'll find an example of this in the Order Details subform in the sample
Northwind database. In that the current unit price of a product is looked up
from the Products table and assigned to the UnitPrice field in the Order
Details table using the following code in the ProductID control's AfterUpdate
event procedure:

Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID

' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)

Exit_ProductID_AfterUpdate:
Exit Sub

Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate

End Sub

Ken Sheridan
Stafford, England
 
Top