On a Purchase Order form I am creating, I pick an item from a combo box 
and the name and price of the item fill in on the form.  The combo box is 
looking at my tblItems table.  The problem is that when I change the price 
for the item in the table, all the purchase orders also change.  How do I 
get the price to stay at the value it was when the Purchase Order was 
created?  I think I'm missing something quite simple?

the field on your form is bound to tblItems.price, but you really need a
price field in your Purchase Order Items table for the price of each item
for that order.  Assuming you have such a field, put it on your form in
place of the tblItems price, and when the user selects an item from
tblItems, populate the PO Item price field with the value from tblItems.
Then the user can change it just for this one usage of the item.

-----Original Message-----
From: Lynn Morgenson [mailto:lmorgenson@j...]
Sent: Thursday, November 21, 2002 2:23 PM
To: Access
Subject: [access] Keep old value on table


On a Purchase Order form I am creating, I pick an item from a combo box 
and the name and price of the item fill in on the form.  The combo box is 
looking at my tblItems table.  The problem is that when I change the price 
for the item in the table, all the purchase orders also change.  How do I 
get the price to stay at the value it was when the Purchase Order was 
created?  I think I'm missing something quite simple?

Hi Lynn,

The answer to your problem is in the trusty old Northwind Traders
database. Take a look at the table schema. You'll notice a many-to-many
relationship between the Products and Orders tables resolved by a third
OrderDetails table. There is a unit price field in both the Products
table and the OrderDetails table. This is not denormalization. The
unit price field in the OrderDetails table stores historical unit price
values and the unit price field in the Products table stores the
current unit price of the product. When you change the current unit
price in the Products table, new order line items in the OrderDetails
table will reflect the new unit price, but the older order line items
using the historical unit price will be preserved.

You just need a second unit price field in your linking table. Explore
Northwind (sample database that ships with Access).

Best,

Bob

>From: "Lynn Morgenson" <lmorgenson@j...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Keep old value on table
>Date: Thu, 21 Nov 2002 20:23:05
>
>On a Purchase Order form I am creating, I pick an item from a combo box
>and the name and price of the item fill in on the form.  The combo box is
>looking at my tblItems table.  The problem is that when I change the price
>for the item in the table, all the purchase orders also change.  How do I
>get the price to stay at the value it was when the Purchase Order was
>created?  I think I'm missing something quite simple?


_________________________________________________________________
MSN 8 with e-mail virus protection service: 2 months FREE* 
http://join.msn.com/?page=features/virus

You have made a mistake in your analysis.  You started from the premise that
item has a one-to-one relationship with price.  This evidently is not true.
One item can have many prices.  You will need to split the table and place
the prices in a separate table with a one-to-many relationship.  Using the
item_id as a foreign key in your prices table each price should have a
dtmFrom and dtmTo.  Then you can analyse costs on an 'as at' basis

Steve K


-----Original Message-----
From: Lynn Morgenson [mailto:lmorgenson@j...]
Sent: 21 November 2002 20:23
To: Access
Subject: [access] Keep old value on table


On a Purchase Order form I am creating, I pick an item from a combo box
and the name and price of the item fill in on the form.  The combo box is
looking at my tblItems table.  The problem is that when I change the price
for the item in the table, all the purchase orders also change.  How do I
get the price to stay at the value it was when the Purchase Order was
created?  I think I'm missing something quite simple?