On a form, I need to look up a price in response to the entry of a product SKU...simple concept. The requirement is that the price never change over time...if they return to this form 3 months from now they want the form to reflect the price that was given the day the form was completed.
Something like the following will retrieve the price from a source table:
SELECT TestOrder.SKU, TestLookup.Price
FROM TestLookup INNER JOIN TestOrder ON TestLookup.SKU = TestOrder.SKU;
But once the underlying prices are changed(guaranteed), the form will reflect the updated price.
I need to find a way to set the price permanently. I thought about copying and pasting the values into a separate control, but that seemed clumsy.
you could keep a history of the prices and put the date changed against each change. for new records use the latest price, for old ones use the price which is valid for the date the record was created.