How to calculate a running total on a form?

[Amit]

Hi,

I'm having a tough time trying to figure out how to
calculate a running total on my form.

I have a form Invoice (frmInvoice, InvoiceID = PK), and
each form has one or more items. There is a subform
Invoice Items (sfrmInvoiceItem, InvoiceItemID = PK,
InvoiceID = FK) to enter the hours and payrate. I'm able
to calculate the total amount for each item on the
subform, but am totally at sea when trying to calculate
the running total for a particular invoice, which would
involve SUM(hrs_worked*payrate) for all items for that
invoice.

Any help will be much appreciated.

Thanks!

-Amit

 

[Jonathan Parminter]

-----Original Message-----
Hi,

I'm having a tough time trying to figure out how to
calculate a running total on my form.

I have a form Invoice (frmInvoice, InvoiceID = PK), and
each form has one or more items. There is a subform
Invoice Items (sfrmInvoiceItem, InvoiceItemID = PK,
InvoiceID = FK) to enter the hours and payrate. I'm able
to calculate the total amount for each item on the
subform, but am totally at sea when trying to calculate
the running total for a particular invoice, which would
involve SUM(hrs_worked*payrate) for all items for that
invoice.

Any help will be much appreciated.

Thanks!

-Amit
.

Hi Amit,
your options include:
1. Show sub form footer. Place in subform footer a textbox
that calculates sum total. Example assumes total
calculated in record source query as field named Total.
=Sum([Total])

2. Do step 1 above but set subform footer visible to
false. Have a control on main form that references the sum
total control
=[subformname]![txtSumTotalControlName]

3. Use DSum in textbox on main form
=dsum("[Total]","[subform record source table/query
name]","[InvoiceItemID]=forms![main form name]!
[InvoiceItemID]")

Luck
Jonathan