Grand total from subforms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 3 subforms in my form, one below the other. They are 2B Deleted, 2B
Added and 2B Modified. There are several items which are common between
either 2 or 3 of the subforms, i.e., New Cost. I want to display the totals
of these common items below the subforms on the main form. The help built
into access(95) is as clear as mud! Also, how can I have these totals
refresh as info is added in the subforms?
 
Bryan,

The approach is to place the subtotal for each subform within the subform's
footer, and then refer to it on the main form. I usually set the Visible
property of the subtotal control on the subform to No.

For example, let's say a control on a subform named OrderDetail named
SumofExtd totals the extended price of all records in the subform, having the
Control Source set to =Sum([Extd])

To set a control on the main form to this value, the syntax is:

=Me!OrderDetail.Form!SumofExtd

HTH
Sprinks
 
So far so good. I got the totals on the subforms and can total from 1
subform on the main form, but still can't pull my total from more than one at
a time. I need to grand total from 2 subforms with the same info. Also, I
could not get the syntax, "=Me!OrderDetail.Form!SumofExtd" to work until I
removed the "me!"

Thanks for your help!
Bryan

Sprinks said:
Bryan,

The approach is to place the subtotal for each subform within the subform's
footer, and then refer to it on the main form. I usually set the Visible
property of the subtotal control on the subform to No.

For example, let's say a control on a subform named OrderDetail named
SumofExtd totals the extended price of all records in the subform, having the
Control Source set to =Sum([Extd])

To set a control on the main form to this value, the syntax is:

=Me!OrderDetail.Form!SumofExtd

HTH
Sprinks

Bryan said:
I have 3 subforms in my form, one below the other. They are 2B Deleted, 2B
Added and 2B Modified. There are several items which are common between
either 2 or 3 of the subforms, i.e., New Cost. I want to display the totals
of these common items below the subforms on the main form. The help built
into access(95) is as clear as mud! Also, how can I have these totals
refresh as info is added in the subforms?
Click to expand...
Click to expand...
 
You should be able to reference more than one subtotal on the main form, so I
think there must be something amiss in the Control Source formula. The
syntax, in general, is:

=NameofSubformControlInMainForm.Form!SubformControlName

Remember that a subform on a main form is a control itself, whose name can
be different than the subform's name. I would check the following:

1. That each subform control on the main form has the name you think it has
2. That the controls on each subform that contain the total are named what
you think they are, and not Text1, etc.
3. That your references to them in your control(s) on the main form follow
the above syntax.

HTH
Sprinks
 
I don't think I am being very clear. I can get the total from any one
subform to work, but the problem I can't resolve is:
"2B Added" Subform and "2B Modified" subform both have a column named "NRE".
I have totals of these columns in each subform, and I need the grand total
of both of these to appear on my main form. So if I have a subtotal of
$25.25 in 2B Added and a subtotal of $24.75 in 2B Modified, then $50.00 needs
to appear on my main form.
Thanks for your patience!!
Bryan
 
Bryan, I hope I'm not missing anything, but it seems all you need to do is
add them. Assuming the name of the control that holds each subform total is
SumofNRE, the formula for the grand total textbox on your main form should be:

=[2B Added].Form!SumofNRE + [2B Modified].Form!SumofNRE

Hope that solves it.
Sprinks
 
Perfect!! I was trying to sum it and kept getting #Name?
Thanks so much!!

Sprinks said:
Bryan, I hope I'm not missing anything, but it seems all you need to do is
add them. Assuming the name of the control that holds each subform total is
SumofNRE, the formula for the grand total textbox on your main form should be:

=[2B Added].Form!SumofNRE + [2B Modified].Form!SumofNRE

Hope that solves it.
Sprinks

Bryan said:
I don't think I am being very clear. I can get the total from any one
subform to work, but the problem I can't resolve is:
"2B Added" Subform and "2B Modified" subform both have a column named "NRE".
I have totals of these columns in each subform, and I need the grand total
of both of these to appear on my main form. So if I have a subtotal of
$25.25 in 2B Added and a subtotal of $24.75 in 2B Modified, then $50.00 needs
to appear on my main form.
Thanks for your patience!!
Bryan
Click to expand...
Click to expand...
 
Whoops, celebrated too soon. I didn't realize that the "+" makes it an "and"
type statement. If I have either no modified parts or no added parts, then I
get a blank. It totals only if there is data in both both subforms. Should
I not be able to add $1.25 + $0 and still get $1.25?

Sprinks said:
Bryan, I hope I'm not missing anything, but it seems all you need to do is
add them. Assuming the name of the control that holds each subform total is
SumofNRE, the formula for the grand total textbox on your main form should be:

=[2B Added].Form!SumofNRE + [2B Modified].Form!SumofNRE

Hope that solves it.
Sprinks

Bryan said:
I don't think I am being very clear. I can get the total from any one
subform to work, but the problem I can't resolve is:
"2B Added" Subform and "2B Modified" subform both have a column named "NRE".
I have totals of these columns in each subform, and I need the grand total
of both of these to appear on my main form. So if I have a subtotal of
$25.25 in 2B Added and a subtotal of $24.75 in 2B Modified, then $50.00 needs
to appear on my main form.
Thanks for your patience!!
Bryan
Click to expand...
Click to expand...
 
Sorry, Brian. I'm not positive, but I'm pretty sure that the results you're
getting is because the Sum function is returning a Null. Try converting both
values to zero before adding them with the Nz function (Null to Zero). See
VBA Help as a reference.

=Nz([2B Added].Form!SumofNRE) + Nz([2B Modified].Form!SumofNRE)

Sprinks

Bryan said:
Whoops, celebrated too soon. I didn't realize that the "+" makes it an "and"
type statement. If I have either no modified parts or no added parts, then I
get a blank. It totals only if there is data in both both subforms. Should
I not be able to add $1.25 + $0 and still get $1.25?

Sprinks said:
Bryan, I hope I'm not missing anything, but it seems all you need to do is
add them. Assuming the name of the control that holds each subform total is
SumofNRE, the formula for the grand total textbox on your main form should be:

=[2B Added].Form!SumofNRE + [2B Modified].Form!SumofNRE

Hope that solves it.
Sprinks

Bryan said:
I don't think I am being very clear. I can get the total from any one
subform to work, but the problem I can't resolve is:
"2B Added" Subform and "2B Modified" subform both have a column named "NRE".
I have totals of these columns in each subform, and I need the grand total
of both of these to appear on my main form. So if I have a subtotal of
$25.25 in 2B Added and a subtotal of $24.75 in 2B Modified, then $50.00 needs
to appear on my main form.
Thanks for your patience!!
Bryan

:

You should be able to reference more than one subtotal on the main form, so I
think there must be something amiss in the Control Source formula. The
syntax, in general, is:

=NameofSubformControlInMainForm.Form!SubformControlName

Remember that a subform on a main form is a control itself, whose name can
be different than the subform's name. I would check the following:

1. That each subform control on the main form has the name you think it has
2. That the controls on each subform that contain the total are named what
you think they are, and not Text1, etc.
3. That your references to them in your control(s) on the main form follow
the above syntax.

HTH
Sprinks



:

So far so good. I got the totals on the subforms and can total from 1
subform on the main form, but still can't pull my total from more than one at
a time. I need to grand total from 2 subforms with the same info. Also, I
could not get the syntax, "=Me!OrderDetail.Form!SumofExtd" to work until I
removed the "me!"

Thanks for your help!
Bryan
Click to expand...
Click to expand...
Click to expand...
 
That was it!!! Thanks again!!

Sprinks said:
Sorry, Brian. I'm not positive, but I'm pretty sure that the results you're
getting is because the Sum function is returning a Null. Try converting both
values to zero before adding them with the Nz function (Null to Zero). See
VBA Help as a reference.

=Nz([2B Added].Form!SumofNRE) + Nz([2B Modified].Form!SumofNRE)

Sprinks

Bryan said:
Whoops, celebrated too soon. I didn't realize that the "+" makes it an "and"
type statement. If I have either no modified parts or no added parts, then I
get a blank. It totals only if there is data in both both subforms. Should
I not be able to add $1.25 + $0 and still get $1.25?

Sprinks said:
Bryan, I hope I'm not missing anything, but it seems all you need to do is
add them. Assuming the name of the control that holds each subform total is
SumofNRE, the formula for the grand total textbox on your main form should be:

=[2B Added].Form!SumofNRE + [2B Modified].Form!SumofNRE

Hope that solves it.
Sprinks

:

I don't think I am being very clear. I can get the total from any one
subform to work, but the problem I can't resolve is:
"2B Added" Subform and "2B Modified" subform both have a column named "NRE".
I have totals of these columns in each subform, and I need the grand total
of both of these to appear on my main form. So if I have a subtotal of
$25.25 in 2B Added and a subtotal of $24.75 in 2B Modified, then $50.00 needs
to appear on my main form.
Thanks for your patience!!
Bryan

:

You should be able to reference more than one subtotal on the main form, so I
think there must be something amiss in the Control Source formula. The
syntax, in general, is:

=NameofSubformControlInMainForm.Form!SubformControlName

Remember that a subform on a main form is a control itself, whose name can
be different than the subform's name. I would check the following:

1. That each subform control on the main form has the name you think it has
2. That the controls on each subform that contain the total are named what
you think they are, and not Text1, etc.
3. That your references to them in your control(s) on the main form follow
the above syntax.

HTH
Sprinks



:

So far so good. I got the totals on the subforms and can total from 1
subform on the main form, but still can't pull my total from more than one at
a time. I need to grand total from 2 subforms with the same info. Also, I
could not get the syntax, "=Me!OrderDetail.Form!SumofExtd" to work until I
removed the "me!"

Thanks for your help!
Bryan
Click to expand...
Click to expand...
Click to expand...
 
Back
Top