Summing Information from Subform

[swicklund]

I have a Form that is a list of dates, and a subForm that is all the projects worked on for a given date, with the hours for each.

I would like to have a field on my main form that is the total hours on a given date, and I would like this field updated whenever the hours for a project on that date is updated. I have tried some VBA code in the "After Update" box on the subForm, but that does not seam to be working.

Help??

 

[MajP]

One way to do this is to put a calculated field in the footer of you subform and make it invisible. Then reference this field on the main form. No code is needed.
thread702-1394345

 

[swicklund]

MajP,

Thank you for the advice. I just tried it out & works great! Now, how do I get this value stored in the table that my form is based on?
I know this is easy, and I should be able to figure it out, but it has been a long day, and my brain is mush....

Thanks,

 

[MajP]

The normal advice is that you do not store calculated values. Because if you can display it on a form, you can display it on a report or query just as easy so do not waste the space. Even more important is that a calculated value in a report, query, or form is real time. If you save this value in a table there is always a chance that you add records without running the procedure to update the table.

This is a real easy query to build. Go to the query builder and build a query with date, project, hours and hit the summation symbol (sigma). This will build an aggregate query. Choose "group by" for project and date, and "sum" for hours. Now you have a query that gives you the total hours, per date, per project.