Sum of sub-form/sub-report

  • Thread starter Thread starter QB
  • Start date Start date
Q

QB

I have a report which incorporate a form within it to give detail about each
project. I now need to add in the report footer a summation of the various
fields within the form. I have tried various method and have been unable to
generate proper values.

I created a controlsource in the report footer

=sum(formName.Form.FieldName)

but it does not return anything.

How can I return a sum of a sub-form on my main report.

QB
 
Open the subform in design view.

Add a text box to the Form Footer section, and give it properties like this:
Control Source =Sum([Amount])
Format Currency
Name txtTotal
Substitute your field name for Amount.
Use General Number for the Format if it's not money.

Now on the main form, add a text box with ControlSource:
=[Sub1].Form!txtTotal
Substitute the name of your subform control for Sub1.

More info about referring to a control in a subform:
http://allenbrowne.com/casu-04.html

While that works, in some cases it will show #Error. The solution is
different form forms, reports, and different versions of Accedss, so to fix
that also, see:
http://allenbrowne.com/RecordCountError.html
 
QB said:
I have a report which incorporate a form within it to give detail about each
project. I now need to add in the report footer a summation of the various
fields within the form. I have tried various method and have been unable to
generate proper values.

I created a controlsource in the report footer

=sum(formName.Form.FieldName)

but it does not return anything.

How can I return a sum of a sub-form on my main report.
Click to expand...


Each subreport must calculate its own total using
=Sum(somefield) in a text box (named txtTotal) in either the
subreport's header or footer section.

The main report can then use a text box **in the same
section** as the subreport to display the subreport's total
by using an expression like:
=subreportcontrol.Report.txtTotal
or if there is a chance that the subreport might not have
any records:

=IIf(subreportcontrol.Report.HasData,subreportcontrol.Report.txtTotal,0)

To get the main report to display the grand total across all
instances of the subreport, set the main report's text box's
RunningSum property to Over All and reference the text box
in another text box in the main report's report footer
section.
 
Allen,

I tried your solution, but I get #error.

What I find weird, is if I edit the controlsource when the form is open, it
works, but when I save the control source and open the form I get #error???
No logic to that.

Although there are record in the form, I even implemented the record count
check, and I still get #Error.

Any other ideas?

QB





Allen Browne said:
Open the subform in design view.

Add a text box to the Form Footer section, and give it properties like this:
Control Source =Sum([Amount])
Format Currency
Name txtTotal
Substitute your field name for Amount.
Use General Number for the Format if it's not money.

Now on the main form, add a text box with ControlSource:
=[Sub1].Form!txtTotal
Substitute the name of your subform control for Sub1.

More info about referring to a control in a subform:
http://allenbrowne.com/casu-04.html

While that works, in some cases it will show #Error. The solution is
different form forms, reports, and different versions of Accedss, so to fix
that also, see:
http://allenbrowne.com/RecordCountError.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

QB said:
I have a report which incorporate a form within it to give detail about
each
project. I now need to add in the report footer a summation of the
various
fields within the form. I have tried various method and have been unable
to
generate proper values.

I created a controlsource in the report footer

=sum(formName.Form.FieldName)

but it does not return anything.

How can I return a sum of a sub-form on my main report.

QB
Click to expand...
Click to expand...
 
Allen,

I tried your solution, but I get #error.

What I find weird, is if I edit the controlsource when the form is open, it
works, but when I save the control source and open the form I get #error???
No logic to that.

Although there are record in the form, I even implemented the record count
check, and I still get #Error.

Any other ideas?

QB





Allen Browne said:
Open the subform in design view.

Add a text box to the Form Footer section, and give it properties like this:
Control Source =Sum([Amount])
Format Currency
Name txtTotal
Substitute your field name for Amount.
Use General Number for the Format if it's not money.

Now on the main form, add a text box with ControlSource:
=[Sub1].Form!txtTotal
Substitute the name of your subform control for Sub1.

More info about referring to a control in a subform:
http://allenbrowne.com/casu-04.html

While that works, in some cases it will show #Error. The solution is
different form forms, reports, and different versions of Accedss, so to fix
that also, see:
http://allenbrowne.com/RecordCountError.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

QB said:
I have a report which incorporate a form within it to give detail about
each
project. I now need to add in the report footer a summation of the
various
fields within the form. I have tried various method and have been unable
to
generate proper values.

I created a controlsource in the report footer

=sum(formName.Form.FieldName)

but it does not return anything.

How can I return a sum of a sub-form on my main report.

QB
Click to expand...
Click to expand...
 
I wasn't 100% sure if I had explained the layout properly, so I thought I'd
briefly elaborate.

In the report which is linked to my project table, in the detail section I
list basic project data and within the detail section I also have the
sub-from. As such, for each project, the report delivers financial data for
that specific project. The sub-form is a Single Form (not continuous). I am
trying to at the very end of the report tabulate a total amount which is
simply the summation of the financial for each project in the main report
(but this is filtered by selections made by the user). So basically the
summation of each occurance of the sub-form in the main report.

QB

QB said:
Allen,

I tried your solution, but I get #error.

What I find weird, is if I edit the controlsource when the form is open, it
works, but when I save the control source and open the form I get #error???
No logic to that.

Although there are record in the form, I even implemented the record count
check, and I still get #Error.

Any other ideas?

QB





Allen Browne said:
Open the subform in design view.

Add a text box to the Form Footer section, and give it properties like this:
Control Source =Sum([Amount])
Format Currency
Name txtTotal
Substitute your field name for Amount.
Use General Number for the Format if it's not money.

Now on the main form, add a text box with ControlSource:
=[Sub1].Form!txtTotal
Substitute the name of your subform control for Sub1.

More info about referring to a control in a subform:
http://allenbrowne.com/casu-04.html

While that works, in some cases it will show #Error. The solution is
different form forms, reports, and different versions of Accedss, so to fix
that also, see:
http://allenbrowne.com/RecordCountError.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

QB said:
I have a report which incorporate a form within it to give detail about
each
project. I now need to add in the report footer a summation of the
various
fields within the form. I have tried various method and have been unable
to
generate proper values.

I created a controlsource in the report footer

=sum(formName.Form.FieldName)

but it does not return anything.

How can I return a sum of a sub-form on my main report.

QB
Click to expand...
Click to expand...
Click to expand...
 
I wasn't 100% sure if I had explained the layout properly, so I thought I'd
briefly elaborate.

In the report which is linked to my project table, in the detail section I
list basic project data and within the detail section I also have the
sub-from. As such, for each project, the report delivers financial data for
that specific project. The sub-form is a Single Form (not continuous). I am
trying to at the very end of the report tabulate a total amount which is
simply the summation of the financial for each project in the main report
(but this is filtered by selections made by the user). So basically the
summation of each occurance of the sub-form in the main report.

QB

QB said:
Allen,

I tried your solution, but I get #error.

What I find weird, is if I edit the controlsource when the form is open, it
works, but when I save the control source and open the form I get #error???
No logic to that.

Although there are record in the form, I even implemented the record count
check, and I still get #Error.

Any other ideas?

QB





Allen Browne said:
Open the subform in design view.

Add a text box to the Form Footer section, and give it properties like this:
Control Source =Sum([Amount])
Format Currency
Name txtTotal
Substitute your field name for Amount.
Use General Number for the Format if it's not money.

Now on the main form, add a text box with ControlSource:
=[Sub1].Form!txtTotal
Substitute the name of your subform control for Sub1.

More info about referring to a control in a subform:
http://allenbrowne.com/casu-04.html

While that works, in some cases it will show #Error. The solution is
different form forms, reports, and different versions of Accedss, so to fix
that also, see:
http://allenbrowne.com/RecordCountError.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

QB said:
I have a report which incorporate a form within it to give detail about
each
project. I now need to add in the report footer a summation of the
various
fields within the form. I have tried various method and have been unable
to
generate proper values.

I created a controlsource in the report footer

=sum(formName.Form.FieldName)

but it does not return anything.

How can I return a sum of a sub-form on my main report.

QB
Click to expand...
Click to expand...
Click to expand...
 
Sorry: I'm not with you here.

At first it's a form where you edit the controlsource of something.

Then its a report with a subform (in Form view???)

Then its a report where you are trying to accumulate a total (presumably
with some kind of Running Sum text box.)

I'm lost. Maybe someone else can help.
 
Sorry: I'm not with you here.

At first it's a form where you edit the controlsource of something.

Then its a report with a subform (in Form view???)

Then its a report where you are trying to accumulate a total (presumably
with some kind of Running Sum text box.)

I'm lost. Maybe someone else can help.
 
A report which has a sub-form. But the subform is called in the detail
section of the report , so it appear n number of times and I need to tabulate
the total sum of all the instances of the subform appearing.
 
A report which has a sub-form. But the subform is called in the detail
section of the report , so it appear n number of times and I need to tabulate
the total sum of all the instances of the subform appearing.
 
Back
Top