Sum Field in Subform

[swanner]

My subform is the detail portion of a purchase order form and I want to
show in the footer the running total cost of the purchase order as I
add new items to the subform. I cannot get it to work. I added an
unbound text field and in the properties, control source typed in =Sum
Forms![PODtlSbFrm]![ExtCost], which did not work. I also tried many
other expressions using expression builder, also no success.

Please help if you would be so kind.

Thanks,
Jim

 

[Allen Browne]

In the Form Footer section of your subform (not Page Footer), your text box
needs just these properties:
Control Source =Sum([ExtCost])
Format Currency

If you are trying to bring the total back from the subform onto the main
form, there is an example in the Nortwind database, on the Orders form.

 

[swanner]

Allen,

Thank you for the quick reply. I input it as you instructed into the
subform footer and it does not work. When I run the form the field
displays #Error. In the PO Form I created a text field called ExtCost,
in which I typed the expression =[OrdQty]*[POCost], which returns the
correct extended cost. I want to see the sum the contents of this
ExtCost field for all the records entered into the subform.

Can you help?

Thanks,
Jim

 

[Allen Browne]

What is the Name of this text box? Try changing its Name (Other tab of
Properties box.) Access gets confused if a control has the same name as a
field, but it is bound to something else. For example, it must not be named
ExtCost, OrdQty, or POCost.

Also, double check that this control is in the Form Footer section, not the
Page Footer section.

The approach you are taking makes perfect sense, so it can work. The ExtCost
field in the query is the best way to do it. It is also possible to use:
=Sum([OrdQty]*[POCost])

 

[swanner]

It works. Thanks again!
Jim

 

[Guest]

Allen,
I've done this, but I get #Error in the subform footer text element (where
the SUM operation is). So far, I've checked that the control is uniquely
named, I've tried several different ways of creating the Control Source line
(hand coded, using the Expression Builder, etc.) but it continues to return
"#Error" in the footer when used.

I've also noticed that I do not have some of the functions that are
referenced elsewhere (i.e. RecordsetClone), is it possible that I need to
include some extension or add-on to get this to work?

I am using MSAccess 2003 (11.6355.6408) SP1. I have a subform similar to
what Jim described above and want to sum the line elements of the subform
(and eventually display them in the main form)

Thanks.
-b

In the Form Footer section of your subform (not Page Footer), your text box
needs just these properties:
Control Source =Sum([ExtCost])
Format Currency

If you are trying to bring the total back from the subform onto the main
form, there is an example in the Nortwind database, on the Orders form.

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

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

My subform is the detail portion of a purchase order form and I want to
show in the footer the running total cost of the purchase order as I
add new items to the subform. I cannot get it to work. I added an
unbound text field and in the properties, control source typed in =Sum
Forms![PODtlSbFrm]![ExtCost], which did not work. I also tried many
other expressions using expression builder, also no success.

 

[Allen Browne]

Which section did you use? the Form Footer or the Page Footer section?
Form Footer should work.

If that does not solve the problem, what is in the Control Source of the
text box in the Form Footer section? And tell us what kinds of fields are in
the expression (e.g. number, currency, calculated query field, ...)

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

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

Allen,
I've done this, but I get #Error in the subform footer text element (where
the SUM operation is). So far, I've checked that the control is uniquely
named, I've tried several different ways of creating the Control Source
line
(hand coded, using the Expression Builder, etc.) but it continues to
return
"#Error" in the footer when used.

I've also noticed that I do not have some of the functions that are
referenced elsewhere (i.e. RecordsetClone), is it possible that I need to
include some extension or add-on to get this to work?

I am using MSAccess 2003 (11.6355.6408) SP1. I have a subform similar to
what Jim described above and want to sum the line elements of the subform
(and eventually display them in the main form)

Thanks.
-b

In the Form Footer section of your subform (not Page Footer), your text
box
needs just these properties:
Control Source =Sum([ExtCost])
Format Currency

If you are trying to bring the total back from the subform onto the main
form, there is an example in the Nortwind database, on the Orders form.

My subform is the detail portion of a purchase order form and I want to
show in the footer the running total cost of the purchase order as I
add new items to the subform. I cannot get it to work. I added an
unbound text field and in the properties, control source typed in =Sum
Forms![PODtlSbFrm]![ExtCost], which did not work. I also tried many
other expressions using expression builder, also no success.

 

[Guest]

I added a textbox element (TotalSum) to the subform footer section (below the
subform footer bar). TotalSum has the Control Source '=Sum([Total]). The
rest of the form contains a simple order invoice set of controls, including
the following:
* Product - combobox containing data for table column using query to define
row source
* Quantity - textbox containing data for table column
* UnitPrice - textbox containing data for table column
* Total - textbox with calculated content; i.e. '=[Quantity]*[UnitPrice]'

Based on everything I'm reading this should work, but I am new to Access and
there are a few things that make me wonder.
* the expression builder does not have some of the form functions that have
been referenced (i.e. ReportselCount, etc.)
* I'm not sure if there are config settings that can be set on a subform
that might disable these type of operations.
-b

Which section did you use? the Form Footer or the Page Footer section?
Form Footer should work.

If that does not solve the problem, what is in the Control Source of the
text box in the Form Footer section? And tell us what kinds of fields are in
the expression (e.g. number, currency, calculated query field, ...)

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

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

Allen,
I've done this, but I get #Error in the subform footer text element (where
the SUM operation is). So far, I've checked that the control is uniquely
named, I've tried several different ways of creating the Control Source
line
(hand coded, using the Expression Builder, etc.) but it continues to
return
"#Error" in the footer when used.

I've also noticed that I do not have some of the functions that are
referenced elsewhere (i.e. RecordsetClone), is it possible that I need to
include some extension or add-on to get this to work?

I am using MSAccess 2003 (11.6355.6408) SP1. I have a subform similar to
what Jim described above and want to sum the line elements of the subform
(and eventually display them in the main form)

Thanks.
-b

In the Form Footer section of your subform (not Page Footer), your text
box
needs just these properties:
Control Source =Sum([ExtCost])
Format Currency

If you are trying to bring the total back from the subform onto the main
form, there is an example in the Nortwind database, on the Orders form.

My subform is the detail portion of a purchase order form and I want to
show in the footer the running total cost of the purchase order as I
add new items to the subform. I cannot get it to work. I added an
unbound text field and in the properties, control source typed in =Sum
Forms![PODtlSbFrm]![ExtCost], which did not work. I also tried many
other expressions using expression builder, also no success.

 

[Allen Browne]

No, you cannot sum a calculated control like that.

Try:
=Sum([Quantity]*[UnitPrice])

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

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

I added a textbox element (TotalSum) to the subform footer section (below
the
subform footer bar). TotalSum has the Control Source '=Sum([Total]). The
rest of the form contains a simple order invoice set of controls,
including
the following:
* Product - combobox containing data for table column using query to
define
row source
* Quantity - textbox containing data for table column
* UnitPrice - textbox containing data for table column
* Total - textbox with calculated content; i.e. '=[Quantity]*[UnitPrice]'

Based on everything I'm reading this should work, but I am new to Access
and
there are a few things that make me wonder.
* the expression builder does not have some of the form functions that
have
been referenced (i.e. ReportselCount, etc.)
* I'm not sure if there are config settings that can be set on a subform
that might disable these type of operations.
-b

Which section did you use? the Form Footer or the Page Footer section?
Form Footer should work.

If that does not solve the problem, what is in the Control Source of the
text box in the Form Footer section? And tell us what kinds of fields are
in
the expression (e.g. number, currency, calculated query field, ...)

Allen,
I've done this, but I get #Error in the subform footer text element
(where
the SUM operation is). So far, I've checked that the control is
uniquely
named, I've tried several different ways of creating the Control Source
line
(hand coded, using the Expression Builder, etc.) but it continues to
return
"#Error" in the footer when used.

I've also noticed that I do not have some of the functions that are
referenced elsewhere (i.e. RecordsetClone), is it possible that I need
to
include some extension or add-on to get this to work?

I am using MSAccess 2003 (11.6355.6408) SP1. I have a subform similar
to
what Jim described above and want to sum the line elements of the
subform
(and eventually display them in the main form)

Thanks.
-b

:

In the Form Footer section of your subform (not Page Footer), your
text
box
needs just these properties:
Control Source =Sum([ExtCost])
Format Currency

If you are trying to bring the total back from the subform onto the
main
form, there is an example in the Nortwind database, on the Orders
form.

My subform is the detail portion of a purchase order form and I want
to
show in the footer the running total cost of the purchase order as I
add new items to the subform. I cannot get it to work. I added an
unbound text field and in the properties, control source typed in
=Sum
Forms![PODtlSbFrm]![ExtCost], which did not work. I also tried many
other expressions using expression builder, also no success.

 

[Guest]

That's it. Thanks.

No, you cannot sum a calculated control like that.

Try:
=Sum([Quantity]*[UnitPrice])

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

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

I added a textbox element (TotalSum) to the subform footer section (below
the
subform footer bar). TotalSum has the Control Source '=Sum([Total]). The
rest of the form contains a simple order invoice set of controls,
including
the following:
* Product - combobox containing data for table column using query to
define
row source
* Quantity - textbox containing data for table column
* UnitPrice - textbox containing data for table column
* Total - textbox with calculated content; i.e. '=[Quantity]*[UnitPrice]'

Based on everything I'm reading this should work, but I am new to Access
and
there are a few things that make me wonder.
* the expression builder does not have some of the form functions that
have
been referenced (i.e. ReportselCount, etc.)
* I'm not sure if there are config settings that can be set on a subform
that might disable these type of operations.
-b

Which section did you use? the Form Footer or the Page Footer section?
Form Footer should work.

If that does not solve the problem, what is in the Control Source of the
text box in the Form Footer section? And tell us what kinds of fields are
in
the expression (e.g. number, currency, calculated query field, ...)

Allen,
I've done this, but I get #Error in the subform footer text element
(where
the SUM operation is). So far, I've checked that the control is
uniquely
named, I've tried several different ways of creating the Control Source
line
(hand coded, using the Expression Builder, etc.) but it continues to
return
"#Error" in the footer when used.

I've also noticed that I do not have some of the functions that are
referenced elsewhere (i.e. RecordsetClone), is it possible that I need
to
include some extension or add-on to get this to work?

I am using MSAccess 2003 (11.6355.6408) SP1. I have a subform similar
to
what Jim described above and want to sum the line elements of the
subform
(and eventually display them in the main form)

Thanks.
-b

:

In the Form Footer section of your subform (not Page Footer), your
text
box
needs just these properties:
Control Source =Sum([ExtCost])
Format Currency

If you are trying to bring the total back from the subform onto the
main
form, there is an example in the Nortwind database, on the Orders
form.

My subform is the detail portion of a purchase order form and I want
to
show in the footer the running total cost of the purchase order as I
add new items to the subform. I cannot get it to work. I added an
unbound text field and in the properties, control source typed in
=Sum
Forms![PODtlSbFrm]![ExtCost], which did not work. I also tried many
other expressions using expression builder, also no success.