The sample Northwind database illustrates another way of doing this by
looking up the unit price from the Products table by means of the DLookup
function. The code for this is in the AfterUpdate event procedure of the
ProductID control on the Order Details subform. In earlier versions this is
done directly in the event procedure, but in Access 2007 then code calls the
GetListPrice function, which in turn calls the DLookupNumberWrapper function.
Note that the Northwind database has Orders and Order Details tables, the
latter modelling the many-to-many relationship between orders and Products.
This allows each order to include more than one product. In your case you
are allowing only one product per order, which may always be the case in your
business model. If not you should decompose your Orders table along the
lines of those in Northwind. Otherwise your Orders table is not fully
normalized as it would contain redundancies in the event of one order
covering more than one product.
I have a db for clients and their purchases. In my attempt to further
normalize my tables, I have created a new table to list the products clients
can purchase. So now i have 3 tables. One has the client info, the 2nd has
the individual orders (ID, client foreign key, product (stores combo box info)
, order date, quantity ordered and a price number field to hold the info this
question is about) and the 3rd has the list of products and the price of each
I have a form to enter the client info and a subform to enter each client's
order info. The order subform has a combo box to choose the product ordered.
When I choose the product,I would like the price from the product table to go
into a field in the subform and be recored in the order table. Is this
Message posted via http://www.accessmonster.com