Form calculation from a subform

[Ken]

Hi All

I have a form called "Visits" that has a subform in it.
The subform is set to continous forms and is called "SF-VisitDates" and has
a field called "G_Total" in the form.
In the Form footer there is a Text Box field called "SG_Total" with Control
Source as "=Sum([G_Total)"

I have a Text Box field in the main form called "SF-Total" with the Control
Source set as "=[SF-VisitDates].Form!SG_Total"
This then displays the summed results from the Subform.

If the Subform is closed, I still need the main form to be able to display
the summed results from the closed subform.

The main reason is because I need the main form set as continous and to be
able to grab the summed calculation from the closed or hidden or whatever
way is possible to get at the calculated data from the subform.

Regards
Ken

 

[Allen Browne]

Ken, I'm not clear what you mean by a "closed subform".

If you are clearing the SourceObject property of the subform control, then
clearly you cannot read from the non-existent subform, so perhaps you could
use DSum() to get the value directly from the subform's table. The Control
Source of the text box might be something like this:

=DSum("G_Total", "MySubformsTable", "ID = " & Nz([ID],0))

 

[Ken]

Allen
Thanks for speedy reply
The reason is that the subform does all of the summing calculations and I
need the main form in continous form so that it can display these results
without hving the subform embedded in it, so I assumed it would be closed>
Regards
Ken

Ken, I'm not clear what you mean by a "closed subform".

If you are clearing the SourceObject property of the subform control, then
clearly you cannot read from the non-existent subform, so perhaps you
could use DSum() to get the value directly from the subform's table. The
Control Source of the text box might be something like this:

=DSum("G_Total", "MySubformsTable", "ID = " & Nz([ID],0))

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

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

Hi All

I have a form called "Visits" that has a subform in it.
The subform is set to continous forms and is called "SF-VisitDates" and
has a field called "G_Total" in the form.
In the Form footer there is a Text Box field called "SG_Total" with
Control Source as "=Sum([G_Total)"

I have a Text Box field in the main form called "SF-Total" with the
Control Source set as "=[SF-VisitDates].Form!SG_Total"
This then displays the summed results from the Subform.

If the Subform is closed, I still need the main form to be able to
display the summed results from the closed subform.

The main reason is because I need the main form set as continous and to
be able to grab the summed calculation from the closed or hidden or
whatever way is possible to get at the calculated data from the subform.

Regards
Ken