subform? or combine query or table

[Guest]

I'm not sure what I need. I am building an inventory data base and also with
it is a salemen list (tblsalesmen) and the shows (auto adds a unique number
to tbl showorder) they go to. The DB with the queries and forms will show how
much inventory is in the warehouse and how much is at the show. I have
another tblproducts that joins to another tblsuppliers to show where we get
the goods from and how much we pay for each. When the salemen come back to
the warehouse, from a show, we count their inventory and take the amount
checked out minus what was checked in and we get what they would have sold
(to find out if they stole/lost any and took the right commission). Now, I am
doing OK except I want my form the secretary uses to input the show name the
product being checked out, etc., to look up the price of the item (that we
pay for it (stored in the product ID table)) to auto fill when the Product ID
has been typed in. I think I need a subform to be able to link multilple
orders to the one show/salesman order... So, I type in a show name and it is
autoassigned a unique number. I pick the salesman from the look up, and I put
in the show date, pick up date etc. I move to the subform and type in the
date the product was requested, choose the product with the unique product id
number and (want the value to pop in next to it) then I have a caculated
field of qty of product x value. It works if I type in a value, but I want
the value to fill in on its own. Thanks for any help.

 

[Guest]

It sounds like all you need to do is look up the price of the product from
the Products table and assign it to the relevant bound control on your
subform. You'll find an example of this in the orders subform of the sample
Northwind database, but essentially its just a question of putting code along
these lines in the AfterUpdate event procedure of the control in which the
product is selected, which for this example we'll assume is a combo box
called cboProduct:

Me.UnitPrice = DLookup("UnitPrice", "tblProducts", "ProductID = " &
Me.cboProduct)

This assumes ProductID is a number; if it were text you'd wrap the value in
quote characters:

Me.UnitPrice = DLookup("UnitPrice", "tblProducts", "ProductID = " & Chr(34)
& Me.cboProduct & Chr(34))