Grand Total (Live) (1 Viewer)

[ezfriend]

Hi guys,

I've pulling my hair on this issue for a day now.

I have a subform that can be filter using date ranges. When a date ranges is select, I get a subtotal on the subform; however, I want to keep a running Grand Total on the main form regardless if the subform is filtered by the date range.

When a user add/subtract a quantity on the filtered subform, I want the main form to reflect the overall quantity left( LIVE).

How do I do that? I haven't touch Access for the last many months and forgot how to do this.

Please help me out.

Thanks.

Ezfriend.

------------ updated 4/9/2011 (today) 8:03 PM----------------

After 19 views of this thread and no one knows the answer either?

 

[ezfriend]

I found a solution. It may not be the best, but will work for this purpose.

In the SUB form AfterUpdate event.

Private Sub Form_AfterUpdate()
    'call a function that calculate the new Grand total
    DisplayGrandTotal
    'or requery the subform, if you use another subform to display the grand total
    Form_frmMain.vwTotalExpense_subform.Requery
End Sub

Private Sub DisplayGrandTotal()

    dim rs as recordset
    dim sql as string

    sql = "select sum(qty) from tblData"
    set rs = currentdb.openrecordset(sql)

    if rs.eof = false then
       form_frmMain.txtGrandTotal = rs(0)
    end if

    rs.close
    set rs = nothing

End Sub

 

[DCrake]

Simply place an unbound control on your form and use DSUM() to get the grand total