Subform based on a qry

[wildexodus]

I have a subform on a form (frmOrder) based on a qry (qrySubform), and one of the text boxes on the subform needs to be looking up the unit price from tblProducts using the ProductID selected in the subform, but its not working. Can anyone see where ive gone wrong?

 

[WayneRyan]

wildexodus,

You are using the UnitPrice from the tblOrderDetails table
instead of tblProducts in your query.

Wayne

 

[wildexodus]

I know, when i change it to it being from the tblProducts it looks the value up fine but then it wont input it into the order details table.

 

[WayneRyan]

wildexodus,

Oh, I see what you are trying to do. This will allow you to change
the price for a particular order.

Keep the query pointing to tblOrderDetails.

Make your Product combo four column with the fourth being
the unit price. On the AfterUpdate event of the combo
move column 3 to UnitPrice.

Wayne

 

[wildexodus]

how do i refer to the 3rd column in vb?

 

[Jerry Stoner]

Hope this helps. You dont need unit cost in either table, its in Product table. Use queries to join dont store 3 times. Also dont store total for order. Calculate it.

 

[wildexodus]

I just cant see what uve dun. U havent got unit price in qrySubfrom in yours and yet somehow its in the list of available control sources.

Could you explain what you did to get it to work if you dont mind?

 

[Jerry Stoner]

Look at the record source for the subform, youll see unit price there.(BTW you may want to modify qrySubform to add Product table then field Unit cost then use the query as the record source instead of the SQL.) Used a join to tblProducts to get the price. Check out your tables. Unit price and Total are gone from order and order detail. Look at the queries to see how data is pulled from other tables(product) without storing them multiple times. When you are ready for reports use the same technique to display totals. Post back if you need more help.
Jerry