i have a invoicing form that has a sub form that referrs to productid, quantity, sellprice etc..

when i insert my product id it automatically places the current sell price for that item, the problem is when i go and change the current sell price for that item the database changes all my old invoices that have that item to the new price.

how can i stop this?

Regards Gwoods.

PS sellprice is a calculated field.

It is typical to store the selling price with along with the other invoice details. It can seem like a normalization error, but you can think of the price as an attribute of the sale. Your alternative is to store a history of selling prices for each item with effective dates, but personally I would only do that in a situation where prices did not change very often.

So i should be storing sellprice in my concatnerated table, not in my partdetails one?
sorry if i have missunderstood

Well, typically a parts table would include info about the part, including its current selling price. An invoice details table would also include a field for the selling price applicable to that sale. So in effect you'd have a price field in both. I think the Northwind sample db uses this method. In your form for invoice details, when you choose a product you get the current selling price from the parts table and populate the field in the invoice table.

Thanks Paul really appreciated, i now think i know how to do this, i'll just use the dlookup function to get the price out of the part details table.

i owe you a beer.

kindly Gwoods.

No problem, and welcome to the site by the way. Rather than a DLookup(), the easier method is to use a combo box to let the user pick the product. Include the price field in the combo's row source (it can be hidden if you want). Then in the after update event of the combo, this will populate the price field:

Me.SellingPrice = Me.ProductCombo.Column(x)

where x is the number of the column containing the price. You'd have to change to the appropriate names of course.