Summing from Sub Forms set to Datasheet View

[Guest]

To make large amounts of data more easilly viewed I have put several subforms
in the main account form on my project. Each subform is set to datasheet
view so that multiple records can be shown at the same time. Each subform is
linked to a query that applies the account number to filter out all other
account numbers.

My problem is that I want to create a text box that displays the sum of the
charges and payments field in the filtered and displayed subforms. I have
tried creating a text box that sums that field, one that links to an
(attempted) calculation field in each query (failed since every time I put
the Table name in the query it popped up an error that said I had too many
close parthesis). I am just about at my wits end. This started as a fiarly
simple idea, and has turned into a maze.

Anyhow, the expression that I currently have is: =Sum([Forms]![Account
Form > Charges Query subform]![Charge Amount]) That is in the control
source. If this expression is correct, perhaps it is in the wrong place. I
am only starting out on these more complex databases, so any help would be
greatly beneficial.

Cory

 

[Guest]

Hi Cory

Put a calulated field in the footer of each subform [CalculatedField]

Then create a text box on your main form to total the calculated fields.

ControlSource:
=[Subform1].[Form]![CaulculatedField1]+[Subform2].[Form]![CaulculatedField2]+[Subform3].[Form]![CaulculatedField3]

Hope this helps

 

[Guest]

OK Tried that. Now it gives me "#Name?"
The two calculated fields in the subforms have control source something like
"=Sum([Account Form > Payment Query]![Payment ".

At the moment, I have the query to which that points set up so that the
criteria takes the Account number value out of the main form to execute the
query.

If that query requires information from the main form Account Number) to
execute, will it still be able to update the subforms (Payments & Charges)
and thereby the main form after retrieving the indicated records? Am I doing
this in a stupid way? Have I missed an easier and less convoluted way of
doing it?

Cory

Hi Cory

Put a calulated field in the footer of each subform [CalculatedField]

Then create a text box on your main form to total the calculated fields.

ControlSource:
=[Subform1].[Form]![CaulculatedField1]+[Subform2].[Form]![CaulculatedField2]+[Subform3].[Form]![CaulculatedField3]

Hope this helps
--
Wayne
Manchester, England.
Not an expert
Enjoy whatever it is you do

To make large amounts of data more easilly viewed I have put several subforms
in the main account form on my project. Each subform is set to datasheet
view so that multiple records can be shown at the same time. Each subform is
linked to a query that applies the account number to filter out all other
account numbers.

My problem is that I want to create a text box that displays the sum of the
charges and payments field in the filtered and displayed subforms. I have
tried creating a text box that sums that field, one that links to an
(attempted) calculation field in each query (failed since every time I put
the Table name in the query it popped up an error that said I had too many
close parthesis). I am just about at my wits end. This started as a fiarly
simple idea, and has turned into a maze.

Anyhow, the expression that I currently have is: =Sum([Forms]![Account
Form > Charges Query subform]![Charge Amount]) That is in the control
source. If this expression is correct, perhaps it is in the wrong place. I
am only starting out on these more complex databases, so any help would be
greatly beneficial.

Cory

 

[Guest]

Hello. I'm having the same problem. Did you get any conclusion?

Marco

OK Tried that. Now it gives me "#Name?"
The two calculated fields in the subforms have control source something like
"=Sum([Account Form > Payment Query]![Payment ".

At the moment, I have the query to which that points set up so that the
criteria takes the Account number value out of the main form to execute the
query.

If that query requires information from the main form Account Number) to
execute, will it still be able to update the subforms (Payments & Charges)
and thereby the main form after retrieving the indicated records? Am I doing
this in a stupid way? Have I missed an easier and less convoluted way of
doing it?

Cory

Hi Cory

Put a calulated field in the footer of each subform [CalculatedField]

Then create a text box on your main form to total the calculated fields.

ControlSource:
=[Subform1].[Form]![CaulculatedField1]+[Subform2].[Form]![CaulculatedField2]+[Subform3].[Form]![CaulculatedField3]

Hope this helps
--
Wayne
Manchester, England.
Not an expert
Enjoy whatever it is you do

To make large amounts of data more easilly viewed I have put several subforms
in the main account form on my project. Each subform is set to datasheet
view so that multiple records can be shown at the same time. Each subform is
linked to a query that applies the account number to filter out all other
account numbers.

My problem is that I want to create a text box that displays the sum of the
charges and payments field in the filtered and displayed subforms. I have
tried creating a text box that sums that field, one that links to an
(attempted) calculation field in each query (failed since every time I put
the Table name in the query it popped up an error that said I had too many
close parthesis). I am just about at my wits end. This started as a fiarly
simple idea, and has turned into a maze.

Anyhow, the expression that I currently have is: =Sum([Forms]![Account
Form > Charges Query subform]![Charge Amount]) That is in the control
source. If this expression is correct, perhaps it is in the wrong place. I
am only starting out on these more complex databases, so any help would be
greatly beneficial.

Cory