Form problem

[Rodney]

I am having a problem with a database I'm setting up.
It's designed to enter purchases from suppliers, track
inventory, and track orders to customers. I have a
subform for the purchase order form that assigns the date,
pulls the product name and I have to enter the quantity
ordered and the unit price. It totals the extended price
for me. I have a products table that contains the product
name and the unit price. I need the subform to
automatically enter the price from the products table when
a product is selected. If I try to make it lookup the
value, it gives me the error that I already have a field
named ProductID. ProductID is the primary key for the
products table. How can I get the subform to
automatically enter the price of the product from the
products table?

 

[Allen Browne]

There is an example of how to do this in the Northwind sample database.

Open Northwind.
Open the Relationships window (Tools menu).
You should see the Orders table, with Order Details for line items.
The Order Details has a ProductID field, linked to Products table.
If you have a setup like that, you will be able to do what you ask.

Still in Northwind, click the Forms tab of the Database window, and open
"Orders Subform" in design view. There is a very wide combo for ProductID.
Right-click it, and choose Properties. On the Event tab of the Properties
box, click in AfterUpdate where you see "[Event Procedure]". Now click the
Build button (...) beside this. Access opens the code window.

Follow the example you see there for how to look up the price.