setting default values

[Jon D.]

I have a products table that has
ProductID,description,Price for each product. I also
have a table that contains order details:quantity
ordered,ProductID,Price. I want to set the default value
for price in the orderDetails based on the value in the
products table. I need to store the price data in the
order details table as well as the products table because
sometimes it needs to be edited without affecting the
standard price in the products table. Please Help! Thanks

 

[Allen Browne]

What you are doing makes perfect sense, and is illustrated in Northwind.

Open the sample database (Northwind).
Open the Orders Subform in design view.
Right-click ProductID, and choose Properties.
On the Event tab of the Properties box, choose After Update.
Click the Build button (...) beside this.

Access opens the code window, and shows how this event uses DLookup() to
read the current price from the table. For further help with DLookup(), see:
http://allenbrowne.com/casu-07.html

 

[Steve Schapel]

Jon,

How are you entering the ProductID into the OrderDetails? If, as is
commonly done, you are using a combobox, whose RowSource is the
Products table, you could include the Price as one of the columns
(hidden probably) in the combobox. Let's say it is the third column.
Then, in the AfterUpdate event of the combobox, you could use code
like this to set the OrderDetails Price...
Me.Price = Me.ProductID.Column(2)

- Steve Schapel, Microsoft Access MVP