I have written an access2003 invoicing mdb that I obviously didn't think through enough and need some help.

Tables are:
"Customers" (which contains the customer specific [discount]) Related to "Invoice" Table
"Products" (Which contains the default [price] for each product) Related to "InvoiceDetail" Table
"Invoice" (Which contains the data that does not change for each line item) Related to "InvoiceDetail" Table
"InvoiceDetail" (Which contains the data for each line item in the Invoice, including the:
1. [price] from the "Product" table and
2. [discount] from the "Customer" table (based a query related to a combo Box in "Invoice" that allows the user to select the appropriate customer)

So, my problem is: All of the forms etc are working perfectly, BUT...

How do I keep the "Invoice" and "Invoice Detail" records from changing when [customer].[discount] or [product].[price] changes over time. In essence, after an invoice and its items have been created, I want to preserve the info that might change over time associated with it so that when I do a subsequent analysis across a date or invoice # range, I get data based on the tables as they existed at the time of the invoice creation...rather than the time of the analysis.

Can anyone help with some direction here?

if you're storing the price and the discount rate in each invoice detail record, then changes to the parent tables (Customers and Products) should not affect existing records. are you storing that data in the InvoiceDetail table? or only displaying it on the form that you're using to view the InvoiceDetail records?

hth
tina

You have encountered one of the exceptions to the principle of not repeating data. This exception is when the data is time sensitive. You need to capture the value at the time you create the record.

So the answer is to add Discount to your Invoice table and Price fields to your Invoice Detail table. When you select the Customer, you populate the Discount control with the current Discount. When you select the Product, you populate the Price control with the current Unit Price.

There are several ways to do this. The way I prefer is to add columns to the RowSource of your comboboxes (assuming you are selecting Customer and Product from a combo) for the Discount and price info. Then use the After Update event of the combo to the Control to the value of the column.

i.e. Me.txtPrice = Me.cboProduct.Column (2)

Looks like I might have stumbled on the right answer....yes, I am storing the data in the Invoice and Invoice details tables.

Thanks for setting me straight, and saving me hours of stumbling around trying to fix a problem that did not really exist.

you're welcome, glad we could help :)