Subform total on a Main Form

[Guest]

I have tried everything! I have read every discussion topic regarding using
totals of a subform on a main form. I have even gone to Northwind.mdb and
tried to copy what it uses to bring the total of the subform to the footer in
the main form! Where am I going wrong!

I have FINALLY created a subform that allows me to add products to a
purchase order (the main form). In the subform footer, I have created a text
field for txtTotal:Sum([LineTotal]). It is showing correctly in the footer
of the subform. I then went to the main form and in its footer created a
text field with: =[subformName]![SubformTextBoxName]. I have tried making a
group(?) query qrytxtTotal that has the qryPurchaseOrderDetail
"PurchaseOrderID" (grouped by: Ascending) and "txtTotal:LineTotal" (Sum) and
then using that in the form as the text box. I am out of ideas! Any help is
certainly appreciated.

 

[Al Camp]

Sharon,
If the totals field in your subform is named txtTotal, then an unbound
text control on the main form with a ControlSource of ...
= [frmYourSubformName].[Form]![txtTotal]

will display the value of txtTotal on the main form.
hth
Al Camp

 

[Graham Mandeno]

Hi Sharon

You are certainly on the right track

Check the properties of the textbox in the footer of your subform. They
should be:
Name: txtOrderTotal (or something else meaningful)
ControlSource: =Sum([LineTotal])
(Note that LineTotal must be a *field* in the recordsource on which your
subform is based)

You can also set the Visible property of the textbox to No, but leave it
visible until you get it working.

Now, the textbox on the main form should have its ControlSource set as
follows:
=[sbfOrderDetails].Form![txtOrderTotal]

Here, txtOrderTotal is the Name of the textbox on the subform, and
sbfOrderDetails is the name of the *subform control* that contains your
subform. Note that this is not necessarily the name of the form object
which is contained by the control. If you double-click in the border of the
subform in design view, the properties window should show:
Name: NameOfSubformControl
SourceObject: NameOfContainedForm

 

[Guest]

I don't know what I am doing wrong, but it is not working. I believe that I
am confused by the "NameofSubformControl" and "NameOfContainedForm". Could
you please clarify. When I do as you suggested by double-clicking in the
border of the subform and the properties window showed: Name:
tblPurchaseOrderDetails and the SourceObject: subfrmPurchaseOrderDetail. The
record source for the subform is qryPurchaseOrderDetail. I know that I am
sooooooooooooo close. I appreciate your patience and help. Thanks.
--
S

Hi Sharon

You are certainly on the right track

Check the properties of the textbox in the footer of your subform. They
should be:
Name: txtOrderTotal (or something else meaningful)
ControlSource: =Sum([LineTotal])
(Note that LineTotal must be a *field* in the recordsource on which your
subform is based)

You can also set the Visible property of the textbox to No, but leave it
visible until you get it working.

Now, the textbox on the main form should have its ControlSource set as
follows:
=[sbfOrderDetails].Form![txtOrderTotal]

Here, txtOrderTotal is the Name of the textbox on the subform, and
sbfOrderDetails is the name of the *subform control* that contains your
subform. Note that this is not necessarily the name of the form object
which is contained by the control. If you double-click in the border of the
subform in design view, the properties window should show:
Name: NameOfSubformControl
SourceObject: NameOfContainedForm

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have tried everything! I have read every discussion topic regarding
using
totals of a subform on a main form. I have even gone to Northwind.mdb and
tried to copy what it uses to bring the total of the subform to the footer
in
the main form! Where am I going wrong!

I have FINALLY created a subform that allows me to add products to a
purchase order (the main form). In the subform footer, I have created a
text
field for txtTotal:Sum([LineTotal]). It is showing correctly in the
footer
of the subform. I then went to the main form and in its footer created a
text field with: =[subformName]![SubformTextBoxName]. I have tried
making a
group(?) query qrytxtTotal that has the qryPurchaseOrderDetail
"PurchaseOrderID" (grouped by: Ascending) and "txtTotal:LineTotal" (Sum)
and
then using that in the form as the text box. I am out of ideas! Any help
is
certainly appreciated.

 

[Al Camp]

Sharon,
We don't know the names of all your forms and controls, so we use
"example" terms in our responses. You have to substitute your names for
ours.
My previous response was...

If the totals field in your subform is named txtTotal, then an unbound
text control on the main form with a ControlSource of ...
= [frmYourSubformName].[Form]![txtTotal]

You have to place the name of "your" subform and the name of "your" field
into the "template example".
Leave [Form] just the way it is, but replace the other control names with
yours.

Like so...
=[PutTheNameofYourSubformHere].[Form]![PutTheNameOfYourSubformTotalsFieldHere]

hth
Al Camp

I don't know what I am doing wrong, but it is not working. I believe that
I
am confused by the "NameofSubformControl" and "NameOfContainedForm".
Could
you please clarify. When I do as you suggested by double-clicking in the
border of the subform and the properties window showed: Name:
tblPurchaseOrderDetails and the SourceObject: subfrmPurchaseOrderDetail.
The
record source for the subform is qryPurchaseOrderDetail. I know that I am
sooooooooooooo close. I appreciate your patience and help. Thanks.
--
S

Hi Sharon

You are certainly on the right track

Check the properties of the textbox in the footer of your subform. They
should be:
Name: txtOrderTotal (or something else meaningful)
ControlSource: =Sum([LineTotal])
(Note that LineTotal must be a *field* in the recordsource on which your
subform is based)

You can also set the Visible property of the textbox to No, but leave it
visible until you get it working.

Now, the textbox on the main form should have its ControlSource set as
follows:
=[sbfOrderDetails].Form![txtOrderTotal]

Here, txtOrderTotal is the Name of the textbox on the subform, and
sbfOrderDetails is the name of the *subform control* that contains your
subform. Note that this is not necessarily the name of the form object
which is contained by the control. If you double-click in the border of
the
subform in design view, the properties window should show:
Name: NameOfSubformControl
SourceObject: NameOfContainedForm

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have tried everything! I have read every discussion topic regarding
using
totals of a subform on a main form. I have even gone to Northwind.mdb
and
tried to copy what it uses to bring the total of the subform to the
footer
in
the main form! Where am I going wrong!

I have FINALLY created a subform that allows me to add products to a
purchase order (the main form). In the subform footer, I have created
a
text
field for txtTotal:Sum([LineTotal]). It is showing correctly in the
footer
of the subform. I then went to the main form and in its footer created
a
text field with: =[subformName]![SubformTextBoxName]. I have tried
making a
group(?) query qrytxtTotal that has the qryPurchaseOrderDetail
"PurchaseOrderID" (grouped by: Ascending) and "txtTotal:LineTotal"
(Sum)
and
then using that in the form as the text box. I am out of ideas! Any
help
is
certainly appreciated.

 

[Graham Mandeno]

Hi Sharon

It appears that the name of your subform control (for some inscrutible
reason!) is "tblPurchaseOrderDetails".

I suggest you rename it to the more logical and meaningful
"sbfPurchaseOrderDetails", by simply editing the contents of the Name cell
in the property sheet.

Then, set the ControlSource of the textbox on your main form to:
=[sbfPurchaseOrderDetails].Form![txtOrderTotal]
(replace "txtOrderTotal" by the name of the textbox on your subform)

Your confusion about the difference between "NameofSubformControl" and
"NameOfContainedForm" is understandable and not uncommon.

"NameofSubformControl" is the name of the box on your main form. It is the
Name property of the subform control.

"NameOfContainedForm" is the name of the thing which is inside the box. It
is the SourceObject property of the subform control.

They are often the same, but not always - hence the confusion.

I suspect what happened is that you originally created the subform by
dragging your table (tblPurchaseOrderDetails) onto the main form. The
wizard would have created the subform control with both name and
SourceObject set to "tblPurchaseOrderDetails".

Then, some time later, I guess you decided you wanted better formatting and
flexibility, so you created a form "subfrmPurchaseOrderDetail" and put that
into the "box" instead of the table. However, the name of the box didn't
change.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I don't know what I am doing wrong, but it is not working. I believe that
I
am confused by the "NameofSubformControl" and "NameOfContainedForm".
Could
you please clarify. When I do as you suggested by double-clicking in the
border of the subform and the properties window showed: Name:
tblPurchaseOrderDetails and the SourceObject: subfrmPurchaseOrderDetail.
The
record source for the subform is qryPurchaseOrderDetail. I know that I am
sooooooooooooo close. I appreciate your patience and help. Thanks.
--
S

Hi Sharon

You are certainly on the right track

Check the properties of the textbox in the footer of your subform. They
should be:
Name: txtOrderTotal (or something else meaningful)
ControlSource: =Sum([LineTotal])
(Note that LineTotal must be a *field* in the recordsource on which your
subform is based)

You can also set the Visible property of the textbox to No, but leave it
visible until you get it working.

Now, the textbox on the main form should have its ControlSource set as
follows:
=[sbfOrderDetails].Form![txtOrderTotal]

Here, txtOrderTotal is the Name of the textbox on the subform, and
sbfOrderDetails is the name of the *subform control* that contains your
subform. Note that this is not necessarily the name of the form object
which is contained by the control. If you double-click in the border of
the
subform in design view, the properties window should show:
Name: NameOfSubformControl
SourceObject: NameOfContainedForm

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have tried everything! I have read every discussion topic regarding
using
totals of a subform on a main form. I have even gone to Northwind.mdb
and
tried to copy what it uses to bring the total of the subform to the
footer
in
the main form! Where am I going wrong!

I have FINALLY created a subform that allows me to add products to a
purchase order (the main form). In the subform footer, I have created
a
text
field for txtTotal:Sum([LineTotal]). It is showing correctly in the
footer
of the subform. I then went to the main form and in its footer created
a
text field with: =[subformName]![SubformTextBoxName]. I have tried
making a
group(?) query qrytxtTotal that has the qryPurchaseOrderDetail
"PurchaseOrderID" (grouped by: Ascending) and "txtTotal:LineTotal"
(Sum)
and
then using that in the form as the text box. I am out of ideas! Any
help
is
certainly appreciated.