Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    69

    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.
    Last edited by ISLMAN1975; 10-21-04 at 05:34.

  2. #2
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    I hope I can help,

    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

  3. #3
    Join Date
    Mar 2004
    Posts
    69
    I'm trying the below method but the open recordset line brings an error to few parameters. What am I doing wrong.

    Private Sub Find_No_Of_Rolls()

    Dim db As Database
    Dim rs As Recordset
    Dim qdf As QueryDef


    Set db = CurrentDb
    With db

    ' Create temporary QueryDef.
    Set qdf = .CreateQueryDef("", "SELECT Sum(Box_Quantity.Rolls_in_Box) AS Total " & _
    "FROM ((Box_Types INNER JOIN OrderItems ON Box_Types.Box_Type = OrderItems.Box_Type) " & _
    "INNER JOIN Box_Quantity ON Box_Types.Box_Type = Box_Quantity.Box_Type) " & _
    "INNER JOIN Boxed ON (OrderItems.OrderItemID = Boxed.OrderItemID) AND " & _
    "(Box_Types.Box_Type = Boxed.Box_Type) " & _
    "HAVING (((OrderItems.OrderItemID)=[Forms]![OrderAllocation]![ChildOrderItemAllocation]![OrderItemID]) " & _
    "AND ((Box_Quantity.Product_Code)=[OrderItems].[Product_Code])); ")

    ' Open Recordset from QueryDef.
    Set rs = qdf.OpenRecordset(dbOpenDynaset)
    Me.Rolls_Supplied = rs!Total


    End With


    End Sub
    Last edited by ISLMAN1975; 10-21-04 at 06:51.

  4. #4
    Join Date
    Mar 2004
    Posts
    69
    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)
    rs.MoveFirst
    Me.Rolls_Supplied = rs![Total]

    End Sub


    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]));
    Last edited by ISLMAN1975; 10-21-04 at 08:21.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •