Populate a value related to a lookup field. - STUMPED!

[KB]

I'm making a quick little app for the wife's home business. Here is
where I'm stumped.

Tables:

Products:
record_id autonumber (indexed)
sku text
Description text
Price currency


OrderDetail
record_id autonumber (indexed)
order_ID number
Qty number
Product lookup (stores record ID of products,

displays

Description)
Price Currency
Sale Price currency

Ok great, not to hard. So an order form, contains a subform with my
orderdetail in datasheet view. You enter qty, select product from
drop list (the lookup). Now, I want, once you select a product I fill
in the price (sale price is for over-rides).

This is where I'm having trouble. Is there a easy way, ie, put a SQL
statement in default value price textbox to plug in the the record_id
from product and query up the record I need for the price?

I worked with using code, opening a query selecting the products
record by record_id, but had problems with getting a proper value out
of the product lookup field (always got value of 0). Other annoyance.
This will be used a lot, so I'd have to open the query on form load,
and then requery each time a product is picked, else I'd be
opening/closing the query constantly.

Any ideas.... I'm still working on it.. but figured I'd ask. Using
Access 2000.

TIA!

 

[GVaught]

You may want to take a look at the sample database that comes with Office
called Northwind.mdb. This will help ensure you setup your tables correctly.
Who knows you may want to make a copy and dump the data that is in there and
use it for your wife's business.

As for looking up values from another table, you could use the VB function:
DLookup. If you open your database into the code editor window for writing
VBA code and type in DLookup, highlight the word and press F1, Access should
open up VB help. This is assuming it was installed when Office was
installed. You may have to install this portion from your Office CD if it is
not available.