Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2009
    Posts
    23

    Unanswered: run query without display

    Hi Guys, pls help

    I have a saved query that calculates SUM.I want to run the query without displaying the datasheet view, how to do it?

    Thnx

  2. #2
    Join Date
    Nov 2009
    Posts
    49
    The problem is you are "Opening" the query to run it.

    Do you need to use a query to sum? In a textbox you can write =Sum([whatever field you want])

    This will sum all the values in a certain field of your choosing. That way no query is needed. If your form is a bit more complicated than this, could you write a bit more about your database?

    Thanks
    Phil

  3. #3
    Join Date
    Sep 2009
    Posts
    23
    Hi Phil,thnx of ur help

    I tried running a code instead of using a saved query and this is the code:

    strRowSource = "SELECT TInvDetail.DeptID, TInvDetail.PartID, Sum(TInvDetail.Qty) AS SumOfQty, TInvMaster.Posted FROM TInvMaster INNER JOIN TInvDetail ON TInvMaster.InvNo = TInvDetail.InvNo GROUP BY TInvDetail.DeptID, TInvDetail.PartID, TInvMaster.Posted HAVING TInvDetail.DeptID = '" & Me.cboDeptID & "' AND TInvDetail.PartID = '" & cboPartID & "' AND TInvMaster.Posted = No"

    It returns one row with the SUM of rows qty and I am interested of assigning the value of SUM to a textbox. How am I going to assign it?

  4. #4
    Join Date
    Nov 2009
    Posts
    49
    The easiest way is to just play with the control source of the textbox (once you click on control source there is a button with "..." on it) click that and essentially remake the query in this query maker. Thats it.

    You could also put an "On Click" event on the textboxes and the 'No' thing, that refreshes that textbox and also checks if the other criteria have been filled.

  5. #5
    Join Date
    Sep 2009
    Posts
    23
    hi phil,
    Thnx for the replies and i manage to find the solution,here it is:

    strRowSource = "SELECT TInvDetail.DeptID, TInvDetail.PartID, Sum(TInvDetail.Qty) AS SumOfQty, TInvMaster.Posted FROM TInvMaster INNER JOIN TInvDetail ON TInvMaster.InvNo = TInvDetail.InvNo GROUP BY TInvDetail.DeptID, TInvDetail.PartID, TInvMaster.Posted HAVING TInvDetail.DeptID = '" & Me.cboDeptID & "' AND TInvDetail.PartID = '" & cboPartID & "' AND TInvMaster.Posted = No"

    'Create the temporary recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strRowSource, dbOpenSnapshot)
    If rs.EOF Then
    SumOfQty.Value = 0
    Else
    If Not IsNull(rs!SumOfQty.Value) Then
    With rs!SumOfQty
    SumOfQty.Value = rs!SumOfQty.Value
    End With
    Else
    'do nothing
    End If
    End If

    'Close the temporary recordset as it is no longer needed
    rs.Close
    db.Close

Posting Permissions

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