Calc total subtotal on subform with continuous form

[Flank]

Hi all,

I have a project with a parent form called Projects. Inside projects there are multiple tabs with continuous subforms.

On subform has a field in place to total the other fields.

So I have some code that totals all these fields on a button click.

Sub Admintotal_Click()
Me.Total = Me.Week1 + Me.Week2 + Me.Week3 + Me.Week4
End Sub

I then call that from the Projects form.

Private Sub Command57_Click()
Call Me.Admin1.Form.Admintotal_Click
End Sub

Now the problem I have is since this is a continuous form, I have to go into each record and activate it for the calc to work from the parent form.

Is there is a way to loop through all the records activate them then run the total?

Thanks

 

[bob fitz]

Why not have:
= Me.Week1 + Me.Week2 + Me.Week3 + Me.Week4
as the Control Source property of the control called "Total" instead of doing the calculation in the click event of a button.

 

[Flank]

I tried what you said but I am getting an error. I am using Access 2007 btw.

I changed the Control Source of the field "Total" and it shows "#Name?" when I switch to form view.

Also if I have a formula in the control source, how will it be stored in the table?

 

[Flank]

Ok I got it by changing it a little to this.
=[Week1]+[Week2]+[week3]+[week4]

But I still need it to be stored in the total field.

If I put [total]=[Week1]+[Week2]+[week3]+[week4]
I get an error

Also
me!total=[Week1]+[Week2]+[week3]+[week4]
gets an error

Gotta be something simple I am missing

 

[bob fitz]

But I still need it to be stored in the total field.

Are you sure? Calculations should NOT normally be saved to a table, but calculated as and when wherever they are needed.

 

[boblarson]

If you have the Week values that are stored in the table. DO NOT STORE THE CALCULATED VALUE UNLESS you are using a 2010 Web database.

Otherwise, you can use a query to get the totals in 99% of the places a table can be used.

 

[boblarson]

Oh, and one more thing. You should encapsulate each of the Week Totals by using the NZ function so a null won't make the total for the month ZERO. Now it isn't likely going to happen that you'd have a week with no values, but best to set it up now and not have to worry about it.

=Nz([Week1],0) + Nz([Week2],0)...etc.

 

[Flank]

Ok that makes sense. I don't need to store it in the table but I have to then tally up all the sub totals.

So the continuous form has 5 records in it for 5 different employees.

I set the total field to:
=[Week1]+[Week2]+[week3]+[week4]

This gives me the total for each record.

I then need a grand total for all 5 records in the continuous subform.

How do i specify the individual total fields from each record?

Thank

 

[bob fitz]

I then need a grand total for all 5 records in the continuous subform/QUOTE]
Create a text box in the Footer section of the form. Set its Control Source to:
=Sum(Nz([Week1],0)+Nz([Week2],0)+Nz([week3],0)+Nz([week4],0))

As Bob Larson said, it be wise to include the Nz() function in the expression that is used in the text box in the Detail Section.