SUMMING FRUSTRATION

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

HI;

HAVE BEEN TRYING OFF AND ON FOR ABOUT A MONTH TO SUM A CONTROL IN A
SUBFORM'S FORM FOOTER, THEN REFER TO THAT CONTROL IN THE MAIN FORM AND
DISPLAY THE ANSWER.

Today I decided to get it done! Been at it for an hour and a half. Here's
what I tried:

In the subform. (sfrmProductsSold), placed a control in the "Form Footer",
also tried it in the "Page Footer", named the control: txtTotalExtended.
It's "Control Source" is: =Sum([ExtendedPrice])

Then in the main form added a control in the body of the form. It's
"Control Source" is:
=[sfrmProductsSold].Form![txtTotalExtended]

Recieved #Error.

Then tried:
=Forms!MainForm!sfrmProductsSold![txtTotalExtended]
Got either #Error or #Name. Don't remember now, but both are incorrect.

Also tried:
=Forms.MainForm.sfrmProductsSold.[txtTotalExtended]
=sfrmProductsSold.Form.[txtTotalExtended]

And a few others. Then displayed the subform in Form View. Noticed that
the control in the Sub Forms footer, txtTotalExtended, was showing #Error.

What is the cure?

Andy
 
Is sfrmProductsSold the name of the subform control (the control that holds
the subform)? You must use that name, which may or may not be the same name
as the form that is being used as the subform.
 
If you get the syntax absolutely correct, you may still get an error #Error
appearing in the main form control if it should happen that the subform is
completely empty.

The syntax that works for me in the mainform control is:
Controlsource: =[Form]![sfrmTransactions].[Form]![txtTotPay]
which is virtually identical to your
Controlsource: =[sfrmProductsSold].Form![txtTotalExtended]

In my situation, that allows the subform's totalling control (which is
located in the form footer) to be echoed to the main form, with the
exception of the case I mentioned, which you can handle otherwise.
 
Hi;

RE: Summing Frustration

Have discovered that the control in the sub form's footer is NOT summing.
It returns #Error, that must be the reason the control on the main form
echoes the same.

After trying dozens of code combinations using the original tables and
forms. Got a thought that one of them might be corrupt, so created brand
new Table/subTable Form/subForm. No go. Same #Error.

The following is the code being used:
The SubForm, (sfrmForm1Sub), contains 4 controls:
txtUnitPrice, txtQtyBought, txtExtendedPrice, (all in Detail), and
txtSExtPrc. (in Form Footer).
txtExtendedPrice =[txtUnitPrice]*[txtQtyBought]
txtSExtPrc =Sum([txtExtendedPrice]) (Returns #Error when viewed in "Form
View"). Even tried using "Build Wizard", returns #Error

Main Form, (frmForm1), contains 2 controls, txtName and txtRunningTotal in
the detail.
txtRunningTotal =sfrmForm1Sub!Forms!txtSExtPrc. (Believe it's echoing
#error from SubForms footer control.

Have also tried:
txtRunningTotal =sfrmForm1Sub.Forms.txtSExtPrc.
txtRunningTotal =sfrmForm1Sub!Forms![txtSExtPrc]
txtRunningTotal =sfrmForm1Sub.Forms.[txtSExtPrc]
txtRunningTotal =Forms!frmForm1!sfrmForm1Sub!Forms!txtSExtPrc.
txtRunningTotal =Forms!frmForm1!sfrmForm1Sub!txtSExtPrc.
txtRunningTotal =Forms.frmForm1.sfrmForm1Sub.Forms.txtSExtPrc.
txtRunningTotal =Forms.frmForm1.sfrmForm1Sub.Forms!txtSExtPrc.
txtRunningTotal =Forms![frmForm1]![sfrmForm1Sub]!Forms!txtSExtPrc.

All echoing: txtSExtPrc's #Error

Anybody have any idea how to overcome this obstacle?

Andy

Chadlon said:
If you get the syntax absolutely correct, you may still get an error #Error
appearing in the main form control if it should happen that the subform is
completely empty.

The syntax that works for me in the mainform control is:
Controlsource: =[Form]![sfrmTransactions].[Form]![txtTotPay]
which is virtually identical to your
Controlsource: =[sfrmProductsSold].Form![txtTotalExtended]

In my situation, that allows the subform's totalling control (which is
located in the form footer) to be echoed to the main form, with the
exception of the case I mentioned, which you can handle otherwise.

Andy said:
HI;

HAVE BEEN TRYING OFF AND ON FOR ABOUT A MONTH TO SUM A CONTROL IN A
SUBFORM'S FORM FOOTER, THEN REFER TO THAT CONTROL IN THE MAIN FORM AND
DISPLAY THE ANSWER.

Today I decided to get it done! Been at it for an hour and a half. Here's
what I tried:

In the subform. (sfrmProductsSold), placed a control in the "Form Footer",
also tried it in the "Page Footer", named the control: txtTotalExtended.
It's "Control Source" is: =Sum([ExtendedPrice])

Then in the main form added a control in the body of the form. It's
"Control Source" is:
=[sfrmProductsSold].Form![txtTotalExtended]

Recieved #Error.

Then tried:
=Forms!MainForm!sfrmProductsSold![txtTotalExtended]
Got either #Error or #Name. Don't remember now, but both are incorrect.

Also tried:
=Forms.MainForm.sfrmProductsSold.[txtTotalExtended]
=sfrmProductsSold.Form.[txtTotalExtended]

And a few others. Then displayed the subform in Form View. Noticed that
the control in the Sub Forms footer, txtTotalExtended, was showing #Error.

What is the cure?

Andy
Click to expand...
Click to expand...
 
When you use the Sum function, you must use the field name, not the control
name, of what you want to be summed.

Thus, this expression:
txtSExtPrc =Sum([txtExtendedPrice])

Needs to be changed to this:
txtSExtPrc =Sum([NameOfFieldToWhichtxtExtendedPriceIsBound])


--
Ken Snell
<MS ACCESS MVP>

Andy said:
Hi;

RE: Summing Frustration

Have discovered that the control in the sub form's footer is NOT summing.
It returns #Error, that must be the reason the control on the main form
echoes the same.

After trying dozens of code combinations using the original tables and
forms. Got a thought that one of them might be corrupt, so created brand
new Table/subTable Form/subForm. No go. Same #Error.

The following is the code being used:
The SubForm, (sfrmForm1Sub), contains 4 controls:
txtUnitPrice, txtQtyBought, txtExtendedPrice, (all in Detail), and
txtSExtPrc. (in Form Footer).
txtExtendedPrice =[txtUnitPrice]*[txtQtyBought]
txtSExtPrc =Sum([txtExtendedPrice]) (Returns #Error when viewed in "Form
View"). Even tried using "Build Wizard", returns #Error

Main Form, (frmForm1), contains 2 controls, txtName and txtRunningTotal in
the detail.
txtRunningTotal =sfrmForm1Sub!Forms!txtSExtPrc. (Believe it's echoing
#error from SubForms footer control.

Have also tried:
txtRunningTotal =sfrmForm1Sub.Forms.txtSExtPrc.
txtRunningTotal =sfrmForm1Sub!Forms![txtSExtPrc]
txtRunningTotal =sfrmForm1Sub.Forms.[txtSExtPrc]
txtRunningTotal =Forms!frmForm1!sfrmForm1Sub!Forms!txtSExtPrc.
txtRunningTotal =Forms!frmForm1!sfrmForm1Sub!txtSExtPrc.
txtRunningTotal =Forms.frmForm1.sfrmForm1Sub.Forms.txtSExtPrc.
txtRunningTotal =Forms.frmForm1.sfrmForm1Sub.Forms!txtSExtPrc.
txtRunningTotal =Forms![frmForm1]![sfrmForm1Sub]!Forms!txtSExtPrc.

All echoing: txtSExtPrc's #Error

Anybody have any idea how to overcome this obstacle?

Andy

Chadlon said:
If you get the syntax absolutely correct, you may still get an error #Error
appearing in the main form control if it should happen that the subform is
completely empty.

The syntax that works for me in the mainform control is:
Controlsource: =[Form]![sfrmTransactions].[Form]![txtTotPay]
which is virtually identical to your
Controlsource: =[sfrmProductsSold].Form![txtTotalExtended]

In my situation, that allows the subform's totalling control (which is
located in the form footer) to be echoed to the main form, with the
exception of the case I mentioned, which you can handle otherwise.

Andy said:
HI;

HAVE BEEN TRYING OFF AND ON FOR ABOUT A MONTH TO SUM A CONTROL IN A
SUBFORM'S FORM FOOTER, THEN REFER TO THAT CONTROL IN THE MAIN FORM AND
DISPLAY THE ANSWER.

Today I decided to get it done! Been at it for an hour and a half. Here's
what I tried:

In the subform. (sfrmProductsSold), placed a control in the "Form Footer",
also tried it in the "Page Footer", named the control: txtTotalExtended.
It's "Control Source" is: =Sum([ExtendedPrice])

Then in the main form added a control in the body of the form. It's
"Control Source" is:
=[sfrmProductsSold].Form![txtTotalExtended]

Recieved #Error.

Then tried:
=Forms!MainForm!sfrmProductsSold![txtTotalExtended]
Got either #Error or #Name. Don't remember now, but both are incorrect.

Also tried:
=Forms.MainForm.sfrmProductsSold.[txtTotalExtended]
=sfrmProductsSold.Form.[txtTotalExtended]

And a few others. Then displayed the subform in Form View. Noticed that
the control in the Sub Forms footer, txtTotalExtended, was showing #Error.

What is the cure?

Andy
Click to expand...
Click to expand...
Click to expand...
 
Ken;

Thank you. Your solution worked.

Instead of:
txtSExtPrc =Sum([txtExtendedPrice])

Used:
txtSExtPrc =Sum([ExtendedPrice])

Works perfectly.

Thank You.

Andy

Ken Snell said:
When you use the Sum function, you must use the field name, not the control
name, of what you want to be summed.

Thus, this expression:
txtSExtPrc =Sum([txtExtendedPrice])

Needs to be changed to this:
txtSExtPrc =Sum([NameOfFieldToWhichtxtExtendedPriceIsBound])


--
Ken Snell
<MS ACCESS MVP>

Andy said:
Hi;

RE: Summing Frustration

Have discovered that the control in the sub form's footer is NOT summing.
It returns #Error, that must be the reason the control on the main form
echoes the same.

After trying dozens of code combinations using the original tables and
forms. Got a thought that one of them might be corrupt, so created brand
new Table/subTable Form/subForm. No go. Same #Error.

The following is the code being used:
The SubForm, (sfrmForm1Sub), contains 4 controls:
txtUnitPrice, txtQtyBought, txtExtendedPrice, (all in Detail), and
txtSExtPrc. (in Form Footer).
txtExtendedPrice =[txtUnitPrice]*[txtQtyBought]
txtSExtPrc =Sum([txtExtendedPrice]) (Returns #Error when viewed in "Form
View"). Even tried using "Build Wizard", returns #Error

Main Form, (frmForm1), contains 2 controls, txtName and txtRunningTotal in
the detail.
txtRunningTotal =sfrmForm1Sub!Forms!txtSExtPrc. (Believe it's echoing
#error from SubForms footer control.

Have also tried:
txtRunningTotal =sfrmForm1Sub.Forms.txtSExtPrc.
txtRunningTotal =sfrmForm1Sub!Forms![txtSExtPrc]
txtRunningTotal =sfrmForm1Sub.Forms.[txtSExtPrc]
txtRunningTotal =Forms!frmForm1!sfrmForm1Sub!Forms!txtSExtPrc.
txtRunningTotal =Forms!frmForm1!sfrmForm1Sub!txtSExtPrc.
txtRunningTotal =Forms.frmForm1.sfrmForm1Sub.Forms.txtSExtPrc.
txtRunningTotal =Forms.frmForm1.sfrmForm1Sub.Forms!txtSExtPrc.
txtRunningTotal =Forms![frmForm1]![sfrmForm1Sub]!Forms!txtSExtPrc.

All echoing: txtSExtPrc's #Error

Anybody have any idea how to overcome this obstacle?

Andy

Chadlon said:
If you get the syntax absolutely correct, you may still get an error #Error
appearing in the main form control if it should happen that the
Click to expand...
Click to expand...
subform
is
completely empty.

The syntax that works for me in the mainform control is:
Controlsource: =[Form]![sfrmTransactions].[Form]![txtTotPay]
which is virtually identical to your
Controlsource: =[sfrmProductsSold].Form![txtTotalExtended]

In my situation, that allows the subform's totalling control (which is
located in the form footer) to be echoed to the main form, with the
exception of the case I mentioned, which you can handle otherwise.

HI;

HAVE BEEN TRYING OFF AND ON FOR ABOUT A MONTH TO SUM A CONTROL IN A
SUBFORM'S FORM FOOTER, THEN REFER TO THAT CONTROL IN THE MAIN FORM AND
DISPLAY THE ANSWER.

Today I decided to get it done! Been at it for an hour and a half.
Here's
what I tried:

In the subform. (sfrmProductsSold), placed a control in the "Form Footer",
also tried it in the "Page Footer", named the control: txtTotalExtended.
It's "Control Source" is: =Sum([ExtendedPrice])

Then in the main form added a control in the body of the form. It's
"Control Source" is:
=[sfrmProductsSold].Form![txtTotalExtended]

Recieved #Error.

Then tried:
=Forms!MainForm!sfrmProductsSold![txtTotalExtended]
Got either #Error or #Name. Don't remember now, but both are incorrect.

Also tried:
=Forms.MainForm.sfrmProductsSold.[txtTotalExtended]
=sfrmProductsSold.Form.[txtTotalExtended]

And a few others. Then displayed the subform in Form View. Noticed that
the control in the Sub Forms footer, txtTotalExtended, was showing #Error.

What is the cure?

Andy
Click to expand...
Click to expand...
Click to expand...
 
You're welcome.

Andy said:
Ken;

Thank you. Your solution worked.

Instead of:
txtSExtPrc =Sum([txtExtendedPrice])

Used:
txtSExtPrc =Sum([ExtendedPrice])

Works perfectly.

Thank You.

Andy

Ken Snell said:
When you use the Sum function, you must use the field name, not the control
name, of what you want to be summed.

Thus, this expression:
txtSExtPrc =Sum([txtExtendedPrice])

Needs to be changed to this:
txtSExtPrc =Sum([NameOfFieldToWhichtxtExtendedPriceIsBound])


--
Ken Snell
<MS ACCESS MVP>

Andy said:
Hi;

RE: Summing Frustration

Have discovered that the control in the sub form's footer is NOT summing.
It returns #Error, that must be the reason the control on the main form
echoes the same.

After trying dozens of code combinations using the original tables and
forms. Got a thought that one of them might be corrupt, so created brand
new Table/subTable Form/subForm. No go. Same #Error.

The following is the code being used:
The SubForm, (sfrmForm1Sub), contains 4 controls:
txtUnitPrice, txtQtyBought, txtExtendedPrice, (all in Detail), and
txtSExtPrc. (in Form Footer).
txtExtendedPrice =[txtUnitPrice]*[txtQtyBought]
txtSExtPrc =Sum([txtExtendedPrice]) (Returns #Error when viewed in "Form
View"). Even tried using "Build Wizard", returns #Error

Main Form, (frmForm1), contains 2 controls, txtName and
Click to expand...
Click to expand...
txtRunningTotal
in
the detail.
txtRunningTotal =sfrmForm1Sub!Forms!txtSExtPrc. (Believe it's echoing
#error from SubForms footer control.

Have also tried:
txtRunningTotal =sfrmForm1Sub.Forms.txtSExtPrc.
txtRunningTotal =sfrmForm1Sub!Forms![txtSExtPrc]
txtRunningTotal =sfrmForm1Sub.Forms.[txtSExtPrc]
txtRunningTotal =Forms!frmForm1!sfrmForm1Sub!Forms!txtSExtPrc.
txtRunningTotal =Forms!frmForm1!sfrmForm1Sub!txtSExtPrc.
txtRunningTotal =Forms.frmForm1.sfrmForm1Sub.Forms.txtSExtPrc.
txtRunningTotal =Forms.frmForm1.sfrmForm1Sub.Forms!txtSExtPrc.
txtRunningTotal =Forms![frmForm1]![sfrmForm1Sub]!Forms!txtSExtPrc.

All echoing: txtSExtPrc's #Error

Anybody have any idea how to overcome this obstacle?

Andy

If you get the syntax absolutely correct, you may still get an error
#Error
appearing in the main form control if it should happen that the
Click to expand...
subform
is
completely empty.

The syntax that works for me in the mainform control is:
Controlsource: =[Form]![sfrmTransactions].[Form]![txtTotPay]
which is virtually identical to your
Controlsource: =[sfrmProductsSold].Form![txtTotalExtended]

In my situation, that allows the subform's totalling control (which is
located in the form footer) to be echoed to the main form, with the
exception of the case I mentioned, which you can handle otherwise.

HI;

HAVE BEEN TRYING OFF AND ON FOR ABOUT A MONTH TO SUM A CONTROL IN A
SUBFORM'S FORM FOOTER, THEN REFER TO THAT CONTROL IN THE MAIN FORM AND
DISPLAY THE ANSWER.

Today I decided to get it done! Been at it for an hour and a half.
Here's
what I tried:

In the subform. (sfrmProductsSold), placed a control in the "Form
Footer",
also tried it in the "Page Footer", named the control: txtTotalExtended.
It's "Control Source" is: =Sum([ExtendedPrice])

Then in the main form added a control in the body of the form. It's
"Control Source" is:
=[sfrmProductsSold].Form![txtTotalExtended]

Recieved #Error.

Then tried:
=Forms!MainForm!sfrmProductsSold![txtTotalExtended]
Got either #Error or #Name. Don't remember now, but both are incorrect.

Also tried:
=Forms.MainForm.sfrmProductsSold.[txtTotalExtended]
=sfrmProductsSold.Form.[txtTotalExtended]

And a few others. Then displayed the subform in Form View. Noticed
that
the control in the Sub Forms footer, txtTotalExtended, was showing
#Error.

What is the cure?

Andy
Click to expand...
Click to expand...
Click to expand...
 
Back
Top