Updating fields and tables

[ablettablet]

Hello all .... blimey I've nearly done it, I've nearly created a database that will help everyone work together. What a steep learning curve. However this morning I've discovered a hitch! Bother!

I've based it on the Northwind example but have completely redesigned to fit with our needs here. The problem I have is we have a products table which includes cost and selling prices. We also have a customers orders table which lists the orders the customer has had, a user can click on a specific order, this loads the order form showing the products they had and the price they paid. But when we update the selling price on the products table it also updates the customers order. I don't want it to do this, but I don't know how to stop it. I've tried checking the VBA code and all that seems different is the GetListPrice function which I commented out as I couldn't make it work.

Function GetListPrice (lProductID As Long) As Currency
GetListPrice = (DLookupNumberWrapper("Name of selling price field in products table", "name of products table","[Product ID] = " & lProductID)
End Function

Please help, it's almost ready to go live with everyone this is our last sticking point. Any help is gratefully appreciated .....

Thanks

Vic

 

[jdraw]

Normally you would have a field such as CurrentPrice in the Product table. And that is just about useless except that it is a reference.

The actual selling price you would record on the Order (OrderLineItem).
That way you actual selling price can always be determined by querying your Orders.
And since your Orders have an Order or Selling Date, you could determine the Selling Price of a Product on a Date.

If you record the Price only on the Product table and use it to determine SellingPrice on an Order, you have the problem you mention. Order, when queried will show the latest Price based on the Product table.

Consider this as 2 issues--- current price of a Product in the Product table,
-selling Price of a Product is recorded on the Order when Sale/Order is made.

There may be a lot of factors going into the actual selling price:
- discounts for favoured customers
- discounts based on volumes of items purchased
- special promotions/sales
- inventory clearance/discounted sales (no refunds....)