Running Sum

[Prufrock]

I have checked out some of the posts here but not clear on the simplest way to achieve what I am after.
I have a Rental payment form with a sub form and I want a running total of any amounts owing when tenants do not pay the full amount of rent when due.

The main form (frmRentPayments) has the Tenant ID,name etc and allows scrolling through every tenant record.
The subform holds the fortnightly rental payments information.

The subform is based on a query.
This subform contains:
TenantID
DateRentPaid
RentAmount
RentAmountPaid
NextDueDate (calculated field)
AmountOwing (calculated field)

I have put a text field on the subform and put the following after numerous attempts in the control source:

TotalOwing:=DSUM("AmountOwing","frmsubformqryRentPayments", "Forms","frmRentPayments","TenantID")

As I change the Tenant ID on the main form the sub form shows me the records of payment for that tenant. I need the Total Owing to be a running sum of Amount Owing field.

I currently get #Name. Can anyone see where I am slipping up?

 

[ThomasLafferty]

Not sure if I read your Data source correctly for the calculated text box you have on your subform, but it looks like you have used 5 arguments for a function which only requires three:

First Argument:
and expression which returns the numeric amounts for summation - in your case AmountOwing try using "[AmountPaid]-[AmountOwing]" since it appears that AmountOwing is NOT a field in either your table or your query.

Second Argument:
The table or query which contains those values to sum, in your case I take it you have a query named frmsubformqryRentPayments.

Third Argument:
Criteria to determine what gets summed
"[TenantID]=Forms!NameofMainForm!NameofSubForm.Form![NameofSubformControlEquivalenttoTenantID]"

Anyway, see this FAQ:
faq702-5248

Hope this helps

Tom



Live once die twice; live twice die once.