Using a total from a sub-form in a sum in the main form.

[mbpaul]

Hey I am trying to create a time sheet that enables my workers to fill out separate work orders and the hours from those Work orders are auto transferred to a sub form within their daily time sheet. I have gotten all this done and I have the total hours transferring to the main form but I need to use that transferred number in a new sum on the main form. I know that this cannot be easily done just by using the expression builder. I am wondering if anyone knows a fix for this. I will attach a Pdf of the form to give you a bit of an idea of what i'm trying to accomplish.

 

[CJ_London]

and I have the total hours transferring to the main form

This is in a control called perhaps ttlHrs?

but I need to use that transferred number in a new sum on the main form

in the new sum you would refer to the transferred number as me.ttlHrs if named this

 

[mbpaul]

I have a Text Box in the subform footer called 'Total Hours 2' the control source is- 'sum([Time Spent])' then in the main form the control source for the text box Work Order Total is '=[Work Orders subform2]![Total Hours 2]' then the control source for the text box 'total Hours' in the main form is '=Sum([Travel HRS]+[Additional HRS]+[Work Order Total])'

so it should be =Sum([Travel HRS]+[Additional HRS]+[me.Work Order Total])' ?

 

[CJ_London]

No, just

=[Travel HRS]+[Additional HRS]+[Work Order Total]

I strongly advise you do not use spaces in your control and field names, you will spend hours of frustration trying to figure it out when something doesn't work as expected

 

[mbpaul]

Thank you I got it too work. That solution was almost too simple I can't believe I didn't try that to begin with

 

[mbpaul]

Ok so I got it to work but now I'm seeing that those calculated totals are not showing up in my data sheet so I can't generate reports based on them. Any Ideas on how to fix this. Sorry if it's a simple fix that I'm missing I very new at using access.

 

[CJ_London]

Not quite sure what you mean - but sounds like you need to use the nz funtion to counter nulls. e.g

=nz([Travel HRS])+nz([Additional HRS])+nz([Work Order Total])

 

[mbpaul]

I tried that it still is not working. The table where the information is stored is not getting the information I think it has something to do with my control source. It seems to be, from what I can see, that I can either set up my control source for my coding or I can set it up to link it to the table. I'm not sure how to make it do both. In my form all my text boxes are linked to my table. I think I deleted that link when I changed the control sources from 'Work Orders' to '=[Work Orders subform2]![Total Hours 2]' and from 'total Hours' to '=[Text19]+[Travel HRS]+[Additional HRS].' I tried using the null function with the code.

 

[CJ_London]

In my form all my text boxes are linked to my table

Does ths include the ones that now has the controlsource=[Work Orders subform2]![Total Hours 2] and =[Text19]+[Travel HRS]+[Additional HRS]?

If so, change the name of those controls to something other than the name of the field - i.e. prefix with 'txt'. It is not necessary to store the value of calculated values since they can be calculated anytime so you should remove them from your table

 

[mbpaul]

Ok I may be going about this in the wrong way but I do want to store the calculated values in my tables. That is what I can't seem to do. I am trying to make it so that our mechanics can fill out the work orders with the time spent during the day then at the end of the day they can fill out a time sheet with any travel time and additional time and it will auto calculate the total hours based on the work orders they complete through the day. I need a way to save this form and all the data (including those calculated values) so they can be turned into a proper report.

 

[CJ_London]

Still don't see the point - they can be calculated as and when required in your report but if that is what you want

To do this, do the following:

1. Ensure all of your controls with calculations in them are not named the same as fields in your table (e.g. prefix with txt or calc as previously suggested)
2. In the before update event of the main form put the following code - changing names as required

ttlhrs=me.calcTtlHrs
anotherfield=me.calcAnotherFld
etc