sum subform to form

[Scott_Brasted via AccessMonster.com]

=Sum([Forms]![frmCamPledgeList].[frmCamPledgeListSub].[Amount Pledged])

Form (frmCamPledgeList) with subform frmCamPledgeListSub). Subform has a
field named Pledge Amount. I want to put a field on the form that gives me a
total of the subform's Amount Pledged. I have tried the above, but I get an
#error in the unbound text box.

Do you need any more info? Any thoughts on why this does not work?

Thanks in advance,
Scott

 

[Douglas J. Steele]

The proper syntax is

=Sum([Forms]![frmCamPledgeList]![frmCamPledgeListSub].Form![Amount Pledged])

(note the addition of .Form in there)

That assumes that the name of the subform control on form frmCamPledgeList
is frmCamPledgeListSub. Depending on how the subform was added, the name of
the subform control may be different than the name of the form being used as
a subform. It's the subform control name you must use.

 

[Scott_Brasted via AccessMonster.com]

Doug, I am getting #error in the control. So I assume I have the wrong
subform name.

In the property sheet for the form,
when I select the subform and get little squares around it, It says:
Name: frmCamPledgeListSub
Source Object: frmCamPledgeListSub

When I click in the blank area of the subform, it says:
Record Source: qryContributorPledges
Caption: frmCamPledgeListSub

When I open the subform directly and select the form, I get:
Record Source: qryContributorPledges
Caption: frmCamPledgeListSub

Every name seems the same to me. Is there somewhere else I should look?

Best,
Scott

The proper syntax is

=Sum([Forms]![frmCamPledgeList]![frmCamPledgeListSub].Form![Amount Pledged])

(note the addition of .Form in there)

That assumes that the name of the subform control on form frmCamPledgeList
is frmCamPledgeListSub. Depending on how the subform was added, the name of
the subform control may be different than the name of the form being used as
a subform. It's the subform control name you must use.

=Sum([Forms]![frmCamPledgeList].[frmCamPledgeListSub].[Amount Pledged])

[quoted text clipped - 9 lines]

Thanks in advance,
Scott

 

[Douglas J. Steele]

Yes, it does sound as though your subform control is named the same as the
form being used as a subform.

While the form's open, go to the Immediate Window (Ctrl-G), type the
following

?[Forms]![frmCamPledgeList]![frmCamPledgeListSub].Form.RecordSource

then hit Enter.

If you get qryContributorPledges, then the references are correct.

To be honest, I'm not certain whether you can actually use Sum like that: my
original response was more about how to refer to controls on subforms than
the entire problem. You may have to use DSum instead.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug, I am getting #error in the control. So I assume I have the wrong
subform name.

In the property sheet for the form,
when I select the subform and get little squares around it, It says:
Name: frmCamPledgeListSub
Source Object: frmCamPledgeListSub

When I click in the blank area of the subform, it says:
Record Source: qryContributorPledges
Caption: frmCamPledgeListSub

When I open the subform directly and select the form, I get:
Record Source: qryContributorPledges
Caption: frmCamPledgeListSub

Every name seems the same to me. Is there somewhere else I should look?

Best,
Scott

The proper syntax is

=Sum([Forms]![frmCamPledgeList]![frmCamPledgeListSub].Form![Amount
Pledged])

(note the addition of .Form in there)

That assumes that the name of the subform control on form frmCamPledgeList
is frmCamPledgeListSub. Depending on how the subform was added, the name
of
the subform control may be different than the name of the form being used
as
a subform. It's the subform control name you must use.

=Sum([Forms]![frmCamPledgeList].[frmCamPledgeListSub].[Amount Pledged])

[quoted text clipped - 9 lines]

Thanks in advance,
Scott

 

[Scott_Brasted via AccessMonster.com]

Doug,

Got it!. Field in subform footer: =sum([AmountPledged]). name control:
txtAmMountPledged. Control in form footer: =[frmCamPledgeListSub].[Form]!
[txtAmountPledged] works like a charm. I got it after I tried your imm. win.
experiment and it worked and your comment that it probably would not work as
an =sum control.

Many thanks,
Scott

Yes, it does sound as though your subform control is named the same as the
form being used as a subform.

While the form's open, go to the Immediate Window (Ctrl-G), type the
following

?[Forms]![frmCamPledgeList]![frmCamPledgeListSub].Form.RecordSource

then hit Enter.

If you get qryContributorPledges, then the references are correct.

To be honest, I'm not certain whether you can actually use Sum like that: my
original response was more about how to refer to controls on subforms than
the entire problem. You may have to use DSum instead.

Doug, I am getting #error in the control. So I assume I have the wrong
subform name.

[quoted text clipped - 36 lines]