Adding a cumulative total to a form containing a subform

  • Thread starter Thread starter k_georgiadis
  • Start date Start date
K

k_georgiadis

I have a form (named 'Donor Screen') containing personal
information on supporters of a charitable organization
(name, address, phone numbers, etc,) The form contains a
subform in a one-to-many relationship (named 'Donations
Form') that lists all the donations made by these
supporters over time. The information is presented in
datasheet format and lists date of contribution, check
number and Amount of each contribution.

I want to have a cumulative total of all contributions
made by each contributor over time. Because the subform
is in datasheet format, it does not have a footer. I saw
a mention somewhere that it is possible to add the
cumulative total oin the main form header or footer but,
whatever I try I get an error message.

Can someone help me with the procedure and formula/syntax?

Thanks!
 
You can place a subform in the footer of your datasheet subform to collect
the total. It does not show up, of course, but you then place a text box on
your main form and read the total from the (invisible) text box from the
Form Footer of the subform.

For an example, open the Northwind sample database.

Open the Orders Subform in design view.
See the OrderSubtotal text box in the Form Footer. Its ControlSource is:
=Sum([ExtendedPrice])

Close Orders Subform, and open the Orders form.
See the SubTotal text box, with a Control Source of:
=[Orders Subform].Form!OrderSubtotal
 
thanks for responding Allen Browne but, as you can see
below, your message is somehow "unavailable." Could you
kindly post again?
 
You can place a subform in the footer of your datasheet subform to collect
the total. It does not show up, of course, but you then place a text box on
your main form and read the total from the (invisible) text box from the
Form Footer of the subform.

For an example, open the Northwind sample database.

Open the Orders Subform in design view.
See the OrderSubtotal text box in the Form Footer. Its ControlSource is:
=Sum([ExtendedPrice])

Close Orders Subform, and open the Orders form.
See the SubTotal text box, with a Control Source of:
=[Orders Subform].Form!OrderSubtotal
 
When I try the Northwind syntax as given in the Order
form, Access adds brackets to the components on the
expression. Whether it is connected to the addition of
brackets or not, I get a #Name# error. Any ideas where I
may be messing up?
-----Original Message-----
You can place a subform in the footer of your datasheet subform to collect
the total. It does not show up, of course, but you then place a text box on
your main form and read the total from the (invisible) text box from the
Form Footer of the subform.

For an example, open the Northwind sample database.

Open the Orders Subform in design view.
See the OrderSubtotal text box in the Form Footer. Its ControlSource is:
=Sum([ExtendedPrice])

Close Orders Subform, and open the Orders form.
See the SubTotal text box, with a Control Source of:
=[Orders Subform].Form!OrderSubtotal

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

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

I have a form (named 'Donor Screen') containing personal
information on supporters of a charitable organization
(name, address, phone numbers, etc,) The form contains a
subform in a one-to-many relationship (named 'Donations
Form') that lists all the donations made by these
supporters over time. The information is presented in
datasheet format and lists date of contribution, check
number and Amount of each contribution.

I want to have a cumulative total of all contributions
made by each contributor over time. Because the subform
is in datasheet format, it does not have a footer. I saw
a mention somewhere that it is possible to add the
cumulative total oin the main form header or footer but,
whatever I try I get an error message.

Can someone help me with the procedure and formula/syntax?

Thanks!
Click to expand...


.
Click to expand...
 
The square brackets are fine. (They are required around names with strange
characters, including spaces, but work around any names.)

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

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

K. Georgiadis said:
When I try the Northwind syntax as given in the Order
form, Access adds brackets to the components on the
expression. Whether it is connected to the addition of
brackets or not, I get a #Name# error. Any ideas where I
may be messing up?
-----Original Message-----
You can place a subform in the footer of your datasheet subform to collect
the total. It does not show up, of course, but you then place a text box on
your main form and read the total from the (invisible) text box from the
Form Footer of the subform.

For an example, open the Northwind sample database.

Open the Orders Subform in design view.
See the OrderSubtotal text box in the Form Footer. Its ControlSource is:
=Sum([ExtendedPrice])

Close Orders Subform, and open the Orders form.
See the SubTotal text box, with a Control Source of:
=[Orders Subform].Form!OrderSubtotal

I have a form (named 'Donor Screen') containing personal
information on supporters of a charitable organization
(name, address, phone numbers, etc,) The form contains a
subform in a one-to-many relationship (named 'Donations
Form') that lists all the donations made by these
supporters over time. The information is presented in
datasheet format and lists date of contribution, check
number and Amount of each contribution.

I want to have a cumulative total of all contributions
made by each contributor over time. Because the subform
is in datasheet format, it does not have a footer. I saw
a mention somewhere that it is possible to add the
cumulative total oin the main form header or footer but,
whatever I try I get an error message.

Can someone help me with the procedure and formula/syntax?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top