Looking up data

[Jayne]

I have a table with 3 cols,Product ID, Product
Description and price. This contains all products I may
need to order.

I have an order form witha lookup for the product to the
products table described above, this stores an ID, which
then automatically puts in the Description in the
description field from the products table. I want the
price to come up automatically also, but I need to store
the price seperately, as the prices may change andI don't
want the change to alter earlier records, so I can't use
the price field from the products table.

Any help please?

 

[Joan Wild]

You would store the price in the orders table as well as the products table.
That might seem redundant to you, but it really isn't. Since the price
changes, you need to store the price (as it was at the time of the order)
with the order.

You can look up the current price in the product table, and store it in the
orders table. Look at the Orders form (and subform) for an example of this.
The afterupdate of the productID, looks up the price.