Get Sum of Column X of Subform in Main Form

[adnanebrahimi]

Hi Expert,

i Have a Main Form called Customer Details, in this form i put Transaction Subform that contains Transaction Details of all Costumer

i Liked these forms with Customer ID so if i see Customer X it means i see Customer X Transaction Details

i want summerized Payment Columns of Transaction of Customer X in Textbox, see this code:
=Sum(Nz([Transaction subform].[Form]![Payment],0))

This Expression returns Error!

and i don't know why

Thanks

[alansidman]

Have you tried putting your expression in the footer or header of the subform?

[adnanebrahimi]

Yes, error still remaining

[adnanebrahimi]

Is anybody here to solve this issue...?

[Missinglinq]

First off, when using Functions like Sum() you must use the Field name, not the Textbox name! If they're both the same (as Access names them when a Field is dragged onto a Form) it will work, but if the two names are different, you must use the name of the Control's underlying Field.

To Calculate a Total, in Form Design View, in the SubForm's Footer, place a Unbound Textbox.

To simply total a Single Field, the Control Source of this new Textbox would be

=Sum([Field1])

If the Field is a Calculated Value you must Sum() the Calculation, not the Field itself!

So, if

MySubTotal = Field1 + Field2

You cannot use

=Sum([MySubTotal])

Instead, you must use

= Sum([Field1[ + [Field2])

Now, to place the totals on the Main Form:

Create an Unbound Textbox, and use this in its Control Source

=SubformName.Form!SFTotalTextboxName

Note that SubformName has to be the name of the Subform Control, and not the name of the Form the Subform is based on, unless the two are the same.

SFTotalTextboxName is the name of the Textbox on the Subform where the calculation is done.

Linq ;0)>

[adnanebrahimi]

First off, when using Functions like Sum() you must use the Field name, not the Textbox name! If they're both the same (as Access names them when a Field is dragged onto a Form) it will work, but if the two names are different, you must use the name of the Control's underlying Field.

To Calculate a Total, in Form Design View, in the SubForm's Footer, place a Unbound Textbox.

To simply total a Single Field, the Control Source of this new Textbox would be

=Sum([Field1])

If the Field is a Calculated Value you must Sum() the Calculation, not the Field itself!

So, if

MySubTotal = Field1 + Field2

You cannot use

=Sum([MySubTotal])

Instead, you must use

= Sum([Field1[ + [Field2])

Now, to place the totals on the Main Form:

Create an Unbound Textbox, and use this in its Control Source

=SubformName.Form!SFTotalTextboxName

Note that SubformName has to be the name of the Subform Control, and not the name of the Form the Subform is based on, unless the two are the same.

SFTotalTextboxName is the name of the Textbox on the Subform where the calculation is done.

Linq ;0)>

Wow! Big Thanks for your complete answer:thumbup:

[Missinglinq]

Glad we could help!

Linq ;0)>

[smithse]

Hi all,

I also find that sometimes the Nz() function returns an error! I now no longer use this function at all!!
I have had exactly the same experience and requirement while trying to get the value of a control on a subform, and resorted to writing a separate function to get the value.

Glad you solved your problem though!

[Missinglinq]

...I also find that sometimes the Nz() function returns an error! I now no longer use this function at all!!

In seven years of trolling half-a-dozen Access forums, 8-10 hours a day, I've never seen a report of Nz() returning an error! I have, however, seen many instances when it was applied incorrectly!

There are workarounds, of course.

If (Me.ControlName & vbNullString) = "" Then

comes to mind, and there are probably others!

And writing a UDF to replace two Controls Sources

=Sum([Field1])

=SubformName.Form!SFTotalTextboxName

seems a bit of overkill, but as John Lennon once wrote, "Whatever gets you through the night!"

Linq ;0)>

[smithse]

In seven years of trolling half-a-dozen Access forums, 8-10 hours a day, I've never seen a report of Nz() returning an error! I have, however, seen many instances when it was applied incorrectly!

There are workarounds, of course.

If (Me.ControlName & vbNullString) = "" Then

comes to mind, and there are probably others!

And writing a UDF to replace two Controls Sources

=Sum([Field1])

=SubformName.Form!SFTotalTextboxName

seems a bit of overkill, but as John Lennon once wrote, "Whatever gets you through the night!"

Linq ;0)>

Hi Missinglinq,

I was able to generate a runtime error with the Nz() function in a db built in Access 2010 on a Windows 7 box, and running in the Access Runtime 2010 on WinXP and Windows Vista. I got rid of the Nz() function which I was using in several forms and queries and my problem went away.

LOL, I have seen suggestions of using 2 controls like that.... Pretty ugly, but as you say, whatever works. Oh, and no, I didn't have to resort to that! I simply excluded Nulls in my query.