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.

Answer 1

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.

Answer 2

Answer deleted as I didn't read the question thoroughly.

Answer 3

You could put a hidden control with a Dsum in the footer of the subform, and then refer to that one from the main form. The Dsum would have its 3rd argument like "InvoiceId <= " & InvoiceId

No need for any VBA/event in that case.

Answer 4

DJ... the code doesn't work... or I don't know how to make it work, rather. I get an error that the method or data member has not been found... on

'save current record id' rec_id = Me.rec_id

Can you explain a little more so I can try to do it.

I'm also trying to get a sum out from the subform and put it in the main form on current event... but if I try just the sum from the subform footer, it gives 0, like it loads it after the on current event.

Please help.