Sum in a Subform

[Guest]

OK, I have a main form based on a table call manifesttbl and a subform based
on a table called chemicaltbl. On the subform I have a calculated field which
calculates the total tons of each chemical (field name is totaltons)
=([Gallons]*0.00417+[Pounds]/2000+[Tons]+[AsbTons]*0.84), this calculation is
working fine. I then have a field (field name - totalsum) in the form footer
that calculates the sum,
=Sum([Gallons]*0.00417+[Pounds]/2000+[Tons]+[AsbTons]*0.84). This also works
fine.

Here is my problem, in the main form, I have a field in the form footer, I
have put this in the control source, =[Forms]![Chemical subform]![totalsum].
But all it says is #NAME? I cannot for the life of me figure out why. Can
anyone help?

 

[Dirk Goldgar]

OK, I have a main form based on a table call manifesttbl and a
subform based on a table called chemicaltbl. On the subform I have a
calculated field which calculates the total tons of each chemical
(field name is totaltons)
=([Gallons]*0.00417+[Pounds]/2000+[Tons]+[AsbTons]*0.84), this
calculation is working fine. I then have a field (field name -
totalsum) in the form footer that calculates the sum,
=Sum([Gallons]*0.00417+[Pounds]/2000+[Tons]+[AsbTons]*0.84). This
also works fine.

Here is my problem, in the main form, I have a field in the form
footer, I have put this in the control source, =[Forms]![Chemical
subform]![totalsum]. But all it says is #NAME? I cannot for the life
of me figure out why. Can anyone help?

The subform isn't a member of the Forms collection. You have to refer
to it by way of the subform control on the main form. Try this:

=[Chemical subform]![totalsum]

If that doesn't work, it's probably because the name of the subform
control (on the main form) isn't actually "Chemical subform". Use the
actual name of the subform control (not necessarily the same as its
Source Object) instead.

If all the names are right, and it still doesn't work, try fully
expanding the reference:

=[Chemical subform].[Form]![totalsum]

 

[Guest]

You are the man Dirk Goldgar! It finally works!!

OK, I have a main form based on a table call manifesttbl and a
subform based on a table called chemicaltbl. On the subform I have a
calculated field which calculates the total tons of each chemical
(field name is totaltons)
=([Gallons]*0.00417+[Pounds]/2000+[Tons]+[AsbTons]*0.84), this
calculation is working fine. I then have a field (field name -
totalsum) in the form footer that calculates the sum,
=Sum([Gallons]*0.00417+[Pounds]/2000+[Tons]+[AsbTons]*0.84). This
also works fine.

Here is my problem, in the main form, I have a field in the form
footer, I have put this in the control source, =[Forms]![Chemical
subform]![totalsum]. But all it says is #NAME? I cannot for the life
of me figure out why. Can anyone help?

The subform isn't a member of the Forms collection. You have to refer
to it by way of the subform control on the main form. Try this:

=[Chemical subform]![totalsum]

If that doesn't work, it's probably because the name of the subform
control (on the main form) isn't actually "Chemical subform". Use the
actual name of the subform control (not necessarily the same as its
Source Object) instead.

If all the names are right, and it still doesn't work, try fully
expanding the reference:

=[Chemical subform].[Form]![totalsum]

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

OK, I spoke too soon, I changed it to =[Chemical subform].[Form]![totalsum],
and I got all excited because it seemed to work, but now I see that it keeps
the sum the same for every record, it is not individual to each record in the
main form. Do you know why?

OK, I have a main form based on a table call manifesttbl and a
subform based on a table called chemicaltbl. On the subform I have a
calculated field which calculates the total tons of each chemical
(field name is totaltons)
=([Gallons]*0.00417+[Pounds]/2000+[Tons]+[AsbTons]*0.84), this
calculation is working fine. I then have a field (field name -
totalsum) in the form footer that calculates the sum,
=Sum([Gallons]*0.00417+[Pounds]/2000+[Tons]+[AsbTons]*0.84). This
also works fine.

Here is my problem, in the main form, I have a field in the form
footer, I have put this in the control source, =[Forms]![Chemical
subform]![totalsum]. But all it says is #NAME? I cannot for the life
of me figure out why. Can anyone help?

The subform isn't a member of the Forms collection. You have to refer
to it by way of the subform control on the main form. Try this:

=[Chemical subform]![totalsum]

If that doesn't work, it's probably because the name of the subform
control (on the main form) isn't actually "Chemical subform". Use the
actual name of the subform control (not necessarily the same as its
Source Object) instead.

If all the names are right, and it still doesn't work, try fully
expanding the reference:

=[Chemical subform].[Form]![totalsum]

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

OK, I spoke too soon, I changed it to =[Chemical
subform].[Form]![totalsum], and I got all excited because it seemed
to work, but now I see that it keeps the sum the same for every
record, it is not individual to each record in the main form. Do you
know why?

Is your subform related to your main form via the Link Master Fields and
Link Child Fields properties? As you move from record to record on the
main form, does the set of records displayed by the subform change?

 

[Guest]

Yes, they are linked by ManifestNo. When I change records it says either 1 of
1 or 2 of 2. Whenever I change something in one record, it makes the other
record identical.

OK, I spoke too soon, I changed it to =[Chemical
subform].[Form]![totalsum], and I got all excited because it seemed
to work, but now I see that it keeps the sum the same for every
record, it is not individual to each record in the main form. Do you
know why?

Is your subform related to your main form via the Link Master Fields and
Link Child Fields properties? As you move from record to record on the
main form, does the set of records displayed by the subform change?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Yes, they are linked by ManifestNo. When I change records it says
either 1 of 1 or 2 of 2. Whenever I change something in one record,
it makes the other record identical.

?? That sounds a bit odd. I wonder if your form and subform are set up
properly.

Please check and tell me again, what are ...

1. The recordsource of the main form?

2. The recordsource of the subform?

3. The Link Master Fields property of the subform control?

4. The Link Child Fields property of the subform control?

5. The name of the summing control in the subform's form footer?

6. The controlsource of that control in the subform's form footer?

7. The name of the control on the main form that is supposed to show the
sum from the subform?

8. The controlsource of that control?

When you move from record to record on the main form, do you see the
summing text box on the subform change values, while the main-form text
box doesn't? Or does neither of them change?

The setup I described works for me, on a simple test form I just checked
and on more elaborate forms I've used in the past.

 

[Guest]

Hi Jennie,

Here is a tutorial I wrote on this subject that you might find useful. There
is a sample download file available (295 KB) to go with this article:

Displaying Subtotals and a Grand Total on a Form
That Displays a One-to-Many Relationship
http://www.access.qbuilt.com/html/subtotals.html


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________