4

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.
  • 16,045
  • 3
  • 42
  • 46

2 Answers2

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.

DJ.
  • 16,045
  • 3
  • 42
  • 46
  • If DSum() is slow, surely looping through all the records is going to be slower? – David-W-Fenton May 22 '09 at 16:38
  • No - the recordsetclone is an in-memory copy. DSUM does a query on the database. – DJ. May 22 '09 at 17:00
  • And it's a copy of the already filtered and retrieved records in the sub-form so it's not going to be a lot of records – DJ. May 22 '09 at 17:07
  • ok thanks. I'll try that. Based on what I see, it should be MUCH faster. Thanks. –  May 23 '09 at 10:19
0

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.

iDevlop
  • 24,841
  • 11
  • 90
  • 149