Have control in main form sum up values from control in subform2

[Chips]

Using Access '03, I have a main form, with a subform1 and a subform2 (nested
in this order).

I want to make a control on the main form sum up the values in a control on
subform2.

I have not been able to figure out how to do this. Is there a way?

Thanks,

Greg Chapp

 

[Allen Browne]

1. Open your subform in design view.

2. If you don't see a Form Footer section, click Form Header/Footer on the
View menu.

3. Add a text box to the Form Footer section, and set these properties:
Control Source =Sum([Amount])
Name txtSumAmount
(Substitute your field name for Amount.)

4. Save and close the subform.

5. Open the main form in design view.

6. Add a text box, and set this Control Source:
=[MySub].Form![txtSumAmount]
Substitute the name of your subform control for MySub.

For an example of how this is done, open the Northwind sample database that
installs with Access. Open the Orders Subform in design view. You will see
the text box in the form footer. Then open the Orders form in design view.
You will see the text box that reads the value from the subform.

There are a few things we may need to clear up in this process:

a) You don't store the total in the main form's table.
Doing so violates basic rules of data normalization.

b) The Name of the subform control may not be the same as the name of the
form loaded into it (its SourceObject.)

c) The expression may produce #Error or #Name if the subform contains on
records - not even the new record row (i.e. you can't add new records in the
subform.)

 

[Chips]

Allen,

I have the control in the footer (I also tried the header, which is where I
really want it). I did the naming of the controls so there was no conflict.

I am getting the #Name? error.

I don't quite understand your sentence below. But it may explain what I
need.
My subform is nested two deep from the main form (It's Subform2 inside
Subform1).
The summing control in the subform itself works fine in the header and in
the footer.
It's the control that refers to it in the main form that gets the #Name?
error.

My subform does add new records, but in it's own table. It does not affect
the main form's table.

Thanks,
Greg

 

[Allen Browne]

Form Header section should work just as well as the Form Footer.

Since it works correctly in the subform, the problem must be with the
ControlSource of the text box that attempts to show the result on the main
form. I take it you have nested subforms, so the expression would be like
this:
=[Subform1].Form![Subform2].Form![Text17]


Sorry: the word "no" is transposed in the unclear sentence, i.e.:
c) The expression may produce #Error or #Name if the subform contains
NO records ...

 

[Chips]

Allen,

I still get a #Name error with

=[FrmWkItemEntrySb1].[Form]![FrmWkItemEntrySb2].[Form]![SumGWages]

Access is adding the [] on "Form".
And the main form, subform1 and subform2 do contain records.
The names of the controls do not match each other nor do they match actual
field names from the underlying tables or queries.

Anyway, I made a header for Subform1, put a summing control in it, and that
worked. And it displays the number I was looking for. (I put a couple
calculating fields in the query for subform1 so it could pull the data from
the query.)

But again, when I try to refer to that new control in the main form I get a
#name error. I guess I haven't figured out how to make this work yet.

GC

Form Header section should work just as well as the Form Footer.

Since it works correctly in the subform, the problem must be with the
ControlSource of the text box that attempts to show the result on the main
form. I take it you have nested subforms, so the expression would be like
this:
=[Subform1].Form![Subform2].Form![Text17]


Sorry: the word "no" is transposed in the unclear sentence, i.e.:
c) The expression may produce #Error or #Name if the subform contains
NO records ...

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

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

Allen,

I have the control in the footer (I also tried the header, which is where
I really want it). I did the naming of the controls so there was no
conflict.

I am getting the #Name? error.

I don't quite understand your sentence below. But it may explain
what I need. My subform is nested two deep from the main form (It's
Subform2 inside Subform1).
The summing control in the subform itself works fine in the header
and in the footer. It's the control that refers to it in the main form
that gets the #Name? error.

My subform does add new records, but in it's own table. It does
not affect the main form's table.

Thanks,
Greg

 

[Allen Browne]

Okay, you'll need to take this one step at a time, until you get it working.

Open the form in design view.
Open the Immediate Window (Ctrl+G) and enter:
? Forms![Forms1].Name
replacing "Form1" with the name of your main form.

Once that's working, try:
? Forms![Forms1]![FrmWkItemEntrySb1].Form.Name

Once that's workring, try:
? Forms![Forms1]![FrmWkItemEntrySb1].Form![FrmWkItemEntrySb2].Form.Name

Finally:
?
Forms![Forms1]![FrmWkItemEntrySb1].Form![FrmWkItemEntrySb2].Form![SumGWages].Name

At some point, it will stop working. That's the point where you have the
Name wrong.

Note that the Name of the subform *control* many not be the same as the name
of the form loaded into it (i.e. its Source Object.)

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

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

Allen,

I still get a #Name error with

=[FrmWkItemEntrySb1].[Form]![FrmWkItemEntrySb2].[Form]![SumGWages]

Access is adding the [] on "Form".
And the main form, subform1 and subform2 do contain records.
The names of the controls do not match each other nor do they match actual
field names from the underlying tables or queries.

Anyway, I made a header for Subform1, put a summing control in it, and
that worked. And it displays the number I was looking for. (I put a couple
calculating fields in the query for subform1 so it could pull the data
from the query.)

But again, when I try to refer to that new control in the main form I get
a #name error. I guess I haven't figured out how to make this work yet.

GC

Form Header section should work just as well as the Form Footer.

Since it works correctly in the subform, the problem must be with the
ControlSource of the text box that attempts to show the result on the
main form. I take it you have nested subforms, so the expression would be
like this:
=[Subform1].Form![Subform2].Form![Text17]


Sorry: the word "no" is transposed in the unclear sentence, i.e.:
c) The expression may produce #Error or #Name if the subform contains
NO records ...

Allen,

I have the control in the footer (I also tried the header, which is
where I really want it). I did the naming of the controls so there was
no conflict.

I am getting the #Name? error.

I don't quite understand your sentence below. But it may explain
what I need. My subform is nested two deep from the main form (It's
Subform2 inside Subform1).
The summing control in the subform itself works fine in the header
and in the footer. It's the control that refers to it in the main form
that gets the #Name? error.

My subform does add new records, but in it's own table. It does
not affect the main form's table.

Thanks,
Greg

c) The expression may produce #Error or #Name if the subform contains
on records - not even the new record row (i.e. you can't add new
records
in the subform.)