Showing a query output after form text box update

I

Iowa Joe

I have a form which is used to release specific items from hold in a
manufacturing setting. I have a table for the holds and a table for the
releases. The main fields from each table are the holds quantity and the
release quantity. I have a query that finds how many holds there are
currently for each Hold ID. This query works great. It sums up any releases
for each Hold ID and subtracts it from the initial hold quantity. I would
like to show this value (CurrentQty) on a form after a user selects a HoldID
from a combo box. The SQL for my query is as follows:

SELECT tbl_Holds.HoldID, tbl_Holds!Quantity-NZ(Sum(tbl_Releases!Quantity))
AS CurrentHold
FROM tbl_Holds LEFT JOIN tbl_Releases ON tbl_Holds.HoldID =
tbl_Releases.HoldID
WHERE (((tbl_Holds.HoldID)=[Forms]![frm_Releases]![combo_HoldID]))
GROUP BY tbl_Holds.HoldID, tbl_Holds.Quantity
ORDER BY tbl_Holds!Quantity-NZ(Sum(tbl_Releases!Quantity)) DESC;

Thank you very much for any help.
 
I

Iowa Joe

I have been trying different code all morning and can't figure it out. I am
using access 2002 and this is what I have for the AfterUpdate for my combobox


Private Sub combo_HoldID_AfterUpdate()

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("qry_HoldFormQty", dbOpenDynaset)
rs.MoveFirst

Me.txtCurrentQty = rs.Fields("CurrentHold").Value

rs.Close
Set rs = Nothing

End Sub

I'm pretty much lost right now as I am trying to do recordsets which is new
ground for me. Any help on this would be great. Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top