How to show a total on a main form from a sub form. 3

[Mindy343]

I found instuctions for this if the sub form is a datasheet but the sub form I'm working with is a continuous form. The main form loads based on the input of a login form and shows employee info. The sub form is a timesheet showing each project as a row with fields for each day and is summed at the end of the row in a text field. There may be as many as a dozen projects. What I need to do is total those sums and show them on the main form as a weekly total of hours....anyone know how?

 

[Remou]

The usual way is to sum the data in the subform footer and then reference the control on the main form. You can hide the footer, if you wish.

You may wish to read:

http://www.mvps.org/access/forms/frm0031.htm

 

[TheAceMan1]

How are ya Mindy343 . . .

Have a look here:

How to Bring a Subtotal from a Subform to a Main Form

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Mindy343]

That's how I would do it normally but since the sub form is a continuous form and not a datasheet the subform total field will show after every record.

 

[Remou]

Perhaps you missed my post? Use the subform footer to create the sum control.

 

[TheAceMan1]

Mindy343 . . .

In parallel with Remou, the faq I provided specifically states:

[blue] . . . you can [purple]sum the subform field by using a calculated control in the subform's form footer.[/purple] You can then reference the calculated control from the main form to display the summed value.[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Mindy343]

I was wrong and right...Remember I said I was working with a continuous form, not a datasheet; I was right that the total does show but only after the last record and I can size and hide it so the text box does not show on the main form. The problem I have now is trying to get the thing to calculate without an error. I have the following fields on my subform linked by EmployeeID to the main form:

HoursID, ProjectID, TaskID, EmployeeID, Day1, Day2, Day3, Day4, Day5, Day6, Day7, Text30, BillingWeek.

Text30 is a calculated field that adds the hours entered(Day1-Day7)for each record. What I need to do is to total the Text30 values and carry that to the main form.

 

[PHV]

In the subform's footer create a textbox with the following ControlSource:
=Nz(Sum([Day1]),0)+Nz(Sum([Day2]),0)+Nz(Sum([Day3]),0)+Nz(Sum([Day4]),0)+Nz(Sum([Day5]),0)+Nz(Sum([Day6]),0)+Nz(Sum([Day7]),0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Mindy343]

Thanks to all...I was offline for awhile...our company was taken over by a much larger company and we had a little trouble transferring resources. I ended up doing it the microsoft way and used formatting and properties to hide the totals on the subform. I later tried PHV's solution and it worked great as well. Thanks again!