If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Showing a query output after form text box update
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. |
#2
|
|||
|
|||
Showing a query output after form text box update
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 "Iowa Joe" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|