Calculated control on subform based on current record

Question

I have the following: main form "customer" from a "customer" table. subform "invoices" with fields "invoice date", "invoice amount" "customer id" etc. from a table "invoices"

whenever user clicks or goes to a record in the "invoices" sub form. I would like a "total so far" control to calculate the sum of the "invoices amount" up until the date of the current record being "clicked" or selected.

i.e. for customer microsoft with invoices: 1) may 2 09, $150 2) may 3 09, $200 3) may 4 09, $500

If user clicks on record 2), "total so far" should show $350 If user clicks on record 1), "total so far" should show $150 If user clicks on record 3), "total so far" should show $850

Currently, I am using DSum function on an event "OnCurrent" in the subform "invoices" to set the "total so far" value. Is this method slow, inefficient?

Any other simpler,cleaner,more elegant,faster, efficient method using ms access features?

I want the "invoices" subform to show ALL the invoices for this customer no matter which record is clicked.

DJ. DJ. · Accepted Answer · 2009-05-22T15:43:44

If the DSum method works for you then use it.

If it's too slow then another way is to use a recordsetclone and loop through the records. This is more code but it's more efficient since it doesn't have to hit the database. You do need a unique key.

Private Sub Form_Current()

  Dim rst As DAO.Recordset
  Dim subTotal As Currency
  Dim rec_id As Long

  'get clone of current records in subform'
  Set rst = Me.RecordsetClone

  'save current record id'
  rec_id = Me.rec_id

  rst.MoveFirst

  'loop and total until current is reached'
  Do Until rst![rec_id] = rec_id
    subTotal = subTotal + rst![InvoiceAmt]
    rst.MoveNext
  Loop

  'add last amount on current record' 
  subTotal = subTotal + rst![InvoiceAmt]

  Set rst = Nothing

  'set text box with subtotal'
  Me.Text2 = subTotal

End Sub

The other way is to build a sql query with a sum() but that takes even more code and hits the database again.

Calculated control on subform based on current record

2 Answers