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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chips - 12 Jul 2007 00:36 GMT

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

Reply to this Message

Allen Browne - 12 Jul 2007 03:03 GMT

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.)

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Using Access '03, I have a main form, with a subform1 and a subform2
> (nested in this order).
[quoted text clipped - 7 lines]
>
> Greg Chapp

Reply to this Message

Chips - 13 Jul 2007 06:18 GMT

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.)

Reply to this Message

Allen Browne - 13 Jul 2007 09:00 GMT

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 ...

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen,
>
[quoted text clipped - 20 lines]
>> on records - not even the new record row (i.e. you can't add new records
>> in the subform.)

Reply to this Message

Chips - 16 Jul 2007 02:12 GMT

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.
>
[quoted text clipped - 32 lines]
>>> on records - not even the new record row (i.e. you can't add new records
>>> in the subform.)

Reply to this Message

Allen Browne - 16 Jul 2007 11:26 GMT

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.)

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen,
>
[quoted text clipped - 54 lines]
>>>> records
>>>> in the subform.)

Reply to this Message