Computed subform control based on current record

Question

I have the following: the main client form from the client table. sub-form of "invoice" with the fields "invoice date", "invoice amount", "customer ID", etc. from the invoice table

whenever the user clicks or goes to the entry in the "invoice" subfile. I would like the “total so far” control to calculate the amount of the “total of bills” until it is “pressed” or the date of the current record is selected.

i.e. for a microsoft client with invoices: 1) may 2 09, 150 US dollars 2) may 3 09, 200 US dollars 3) may 4 09, 500 US dollars

If the user clicks on the entry 2), the “total so far” should show 350 US dollars. If the user clicks on the entry 1), “the total so far” should show 150 US dollars. If the user clicks on the record 3), “the total so far” should show $ 850

I am currently using the DSum function in the "OnCurrent" event in the "invoice" subform to set the value to "total until". Is this method slow, inefficient?

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

I want the invoices sub-organization to display ALL invoices for this customer no matter which record is clicked.

Answer 1

DSum , .

, . , , . .

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

SQL- sum(), .

Answer 2

Dsum , . Dsum , "InvoiceId <= " & InvoiceId

- VBA/.