Question in regards to form -> record source

[ssian]

Hi, i'm pretty new so i have found one template which works out what i need. The problem i'm facing is something which i'm not sure how to fix it.

1) I have a main form which is called the frm_Order and this form is tagged to a query; qryOrder which consists of two tables; tbl_Order & tbl_Cashier.
The main form has no problem, so basically what it does it to create a PO# after one selects a cashier name

2) There's a sub form within the main form which is frm_OrderDetails and this form is tagged to a query; qryOrderDetails which consists of two tables; tbl_OrderDetails & tbl_Products. This query helps me to retrieve the Unit Price and calculation of total amount after the cashier selects the Product.

Problem:
Main Form: frm_Order record source is qryOrder
Sub Form: frm_OrderDeatils record source is qryOrderDetails

Because my main purpose is to know the inventory status of the product on hand and there's this query qryStockOnHand which shows the output of the inventory currently on hand for a product_ID.
*I wish to show this qryStockOnHand within the frm_OrderDetails, is it possible?
Meaning after the user selected the cashier, OrderNumber and OrderDate auto created, when the user select the combobox for a particular product, another column beside the Quantity to enter would have a new column call the "OnHand" inventory.

I have tried to edit the qryOrderDetails to include the "OnHand" from the qryStockOnHand, but after i have saved the query, the frm_OrderDetails does not work anymore.

 

[arnelgp]

remove the OnHand from the original qryOrderDetails.
add another calculated column to qryOrderDetails that will show the "OnHand" quantity.

 

[ssian]

The problem seems to be on the qryOrderDetails
When i tried to enable the Total to have the Sum function, it doesn't allow me to add new orders when creating new order.
I have to disable the Sum Function in order for the qryOrderDetails to work.

Ok i think i do the other way round.
First user goes to product to check qty on hand then place order for as sub form so whenever user wishes to check onhand, they use the main form frmProduct to check while add order in the frmOrder & frmOrderDetails