holding unchangeable? data in a table.

[Revelation]

I have tblInvoice that holds information about an invoice: invoiceID, CarID, CompanyID, Date, Tyres sold and the type of tyre and price of that tyre. In tblTyre i have: Type Of tyre, Price and Amount (in stock). I'd like to save invoices in the table, but I dont want the price to change as the price in tblTyres changes. Is htere a way that (in a form) a user selects a type of tyre and it works out the price but later on if the price of tyres changes the field price of tyre in tblInvoice is unchanged. So they can look back in the history.
Hope you understand me Thanks Adam.

 

[mdmilner]

Yes, but I see a problem in how you structured your tables.
What happens if you buy a particular model of tire multiple times at different prices. Which would be applied to your comparison model?

You do have a few options.
First would be to include the unique invoiceID field valeu (tblInvoice) in a field in tblTyre.

Second would be to maintain two price fields in tblTyre.
Only one would be editable by the user - to control that, you must present the table within a form and then just lock the field (that's the property name)

Which you choose depends on other needs. Do you need to do other comparisons in the future? Do you need two related tables? (I'd do the first option just as a good habit)

Mike