Unanswered: Getting the sum value from a query into feild of form
I've created a query which sums a colume and gives me one total value when its run. How do I go about running this query from a button on a form and then getting that one result into a feilds control on the same form.
What I suggest is having the control field on the form relate to the query to show your answer. Include a button or command to run your query. At the end of the Vb code put in an extra line to say ControlField.requery
This should refresh your control field and display the latest query result
Even when I use query bulider to create the query "query1" I cant get the following simple bit of code to work.
Private Sub set_No_Of_Rolls()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("query1", dbOpenTable)
Me.Rolls_Supplied = rs![Total]
SQL of "query1"
SELECT Sum([Box_Quantity].[Rolls_in_Box]) AS Total
FROM OrderItems INNER JOIN (Boxed INNER JOIN Box_Quantity ON [Boxed].[Box_Type]=[Box_Quantity].[Box_Type]) ON [OrderItems].[OrderItemID]=[Boxed].[OrderItemID]
HAVING (((OrderItems.OrderItemID)=[Forms]![OrderAllocation]![ChildOrderItemAllocation]![OrderItemID]) AND ((Box_Quantity.Product_Code)=[OrderItems].[Product_Code]));