Using data from one table to populate another

[jfrasier]

I have a products table with item and price. In a form, when I choose an item I have it set up so that the price automatically is entered into the table. However if later I change the price in the products table, the price changes in the purchase order table so later I don't really know how much I paid for the item.

How do I fix this?

Jane

 

[Rohdem]

How are your relationships and primary / foreign keys set up?
Mike Rohde
rohdem@marshallengines.com

 

[jfrasier]

I created this long ago so I might have done it differently now.

The TProduct table's key is the ProductID. TItems table used ProductID and PO_Num for the primary key. They are related by Product ID.

Jane

 

[Rohdem]

Go to the relationships window and right click on the line connecting Product ID in the two tables. Make sure that cascading updates is NOT checked. When it is checked, if you change the value in one table, it is updated in the other table.
Mike Rohde
rohdem@marshallengines.com

 

[jfrasier]

Thanks for the tip. However I tried it and it didn't work. From what I read in "Help" cascading updates has to do with changing primary key values. In this case the price is changing which is not a primary key.

Jane

 

[Telsa]

Just a thought, rather than use the ID to populate the price field, use Dlookup to get the price and have the actual price in the field for your order table. Then it won't be affected by changes in the product table.

Mary

 

[jfrasier]

Maybe I am doing something wrong, but it still changes if I change the price. It seems like I need to lookup the price in TPRoducts and then place it is a field that then is left alone but I can't figure out how to do that.

Thanks for the idea.
Jane

 

[Rohdem]

In order for the data from one table to be affecting data in another table, you must have a relationship somewhere that is causing the problem. Try removing all the relationships and see if the problem still occurs
Mike Rohde
rohdem@marshallengines.com