Calculating Total

[bsdixon]

I have a couple questions.

How do I calculate the total of a field on a form? I also want the total to show up on a page footer on a report. Should I calculate this in a quey?

Also, I have a button on a subform that opens another form. This form is based on a query that has a filter based on a value on the subform. When I click on the button, the form does not open because it cannot find the the value on the subform. The button only works if I open the subform separately. How can I fix this? Do I need to add code to open the subform automatically?

Thanks.

 

[Pat Hartman]

How do I calculate the total of a field on a form?

- Add a form footer with a control. In the ControlSource of the control:
=Sum(YourField)

I also want the total to show up on a page footer on a report

- You need to sum it again in the report. However, you cannot use aggregate functions in Page footers. You can only use them in group footers and in the report footer. If you really want a sum at the end of every page, you'll need to calculate it yourself. Search here and in the Microsoft knowledgebase for sample code.

I also want the total to show up on a page footer on a report

- You can't. The query wouldn't have any way of knowing what was on any given report page.

The button only works if I open the subform separately. How can I fix this?

- You are not referencing the control properly. When a control is on a subform, you need to include the subform in the control's name:
Forms!YourMainForm!YourSubform.Form!YourControl

 

[bsdixon]

Pat:

Thanks for the help.

I am still having trouble with one more part.
I am trying to calculate the total of a field on a subform

I added the following code in a text box on my Main form footer.

=Sum([ScheduleSubForm].Form![Total Bushels])

I get a #Error message.

Do you have any idea what I am doing wrong?

 

[Pat Hartman]

If you are summing a field from the subform, the expression needs to go in a control on the subform's footer. So the expression would be:

=Sum([Total Bushels])

BTW, it is poor practice to use embedded spaces or special characters in your object names.