Reporting in main form total of subform field (in continuous forms

[Guest]

I have a table, "Clients", a form "Input", a second table "Payments", and a
subform "Payments". The field desires to be totaled is "Amount" in the
subform "Payments". I wish to report this total in an unbound field
("Balance") on the main form. The subform is always open.

I have tried
=Sum(Forms!Payments!Amount) - Result: #Name

I have also tried
=Forms!Payments!Subtotal , in which "Subtotal" is a field in the
subform "Payments" with the value "=Sum([amount])" The "Subtotal" field
works fine, but the "Balance" field reports #Error.

I would very much appreciate the simplest way to display the total on the
main form. I've searched and seen several variations of this already on the
board, but nothing I was able to understand well enough to apply to my
situation.

Thank you.

 

[Michel Walsh]

Hi,




why not


=DSum( "Amount", "TableNameHere", "criteriaHere" )




Vanderghast, Access MVP

 

[Guest]

Looks great. What's the "CriteriaHere" ?

 

[Michel Walsh]

Hi,


It is a where condition, without the word WHERE, that indicates to keep only
the records that satisfy the condition into account when making the sum.


DSUM("Amount", "tableNameHere", "ClientID=" & 57 )


will sum the amounts, but only for those where clientId=57


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Excellent! =DSum("Amount","Payments","ClientsKey = " & [key]) gave me the
total I wanted, totalling every value in the field "Amount" in the subform
"Payments" whose "ClientsKey" matched the "Key" active in the main form. (The
relating fields between the two forms were Key (Main) and ClientsKey (Sub).

My next question:
How do I set this field to recalculate each time a change is made in the
related subfield? I tried changing my numbers, and it still gives me the
same total.


Thank you very much for all your help!

Lea

 

[Michel Walsh]

Hi,


Me.Recalc


would recalc all these expressions in the form that has the code (which is
what Me stands for).


So, in the AfterUpdate event of the control, of the subform, maybe something
like:


Forms!FormNameOfTheParentHere.Recalc


could do the trick ?




Hoping it may help,
Vanderghast, Access MVP

Excellent! =DSum("Amount","Payments","ClientsKey = " & [key]) gave me
the
total I wanted, totalling every value in the field "Amount" in the subform
"Payments" whose "ClientsKey" matched the "Key" active in the main form.
(The
relating fields between the two forms were Key (Main) and ClientsKey
(Sub).

My next question:
How do I set this field to recalculate each time a change is made in the
related subfield? I tried changing my numbers, and it still gives me
the
same total.


Thank you very much for all your help!

Lea

Hi,


It is a where condition, without the word WHERE, that indicates to keep
only
the records that satisfy the condition into account when making the sum.


DSUM("Amount", "tableNameHere", "ClientID=" & 57 )


will sum the amounts, but only for those where clientId=57


Hoping it may help,
Vanderghast, Access MVP