Not sure what I am doing incorrectly.... Any assistance would be appreciated
I am designing a "Service Orders" database which is mostly completed but I am having troubles getting a few of the key remaining but very important details completed. Hopefully my wording is not too confusing. PS: Running Access 2016.
I have a main form named [frmOrders] with an included subform named [frmLabor] within it. In the subform [frmLabor]'s control source table (named [tblLabor]) there is a calculated field named "SubtotalTime" that calculates the difference in time between 2 other fields ("StartTime" and "EndTime") which is displayed in decimal format. It's expression is: Round(([EndTime]-[StartTime])*24,4). This works fine and is displayed in the sub-form [frmLabor] for each of the main form's filtered records (only the labor records associated with the current [frmOrders]'s client name). Again, hopefully this is not confusing...
Within my main [frmOrders] form but outside of the [frmLabor] subform there is a text box named "TotalLaborTime" (formatted as General Number) that I am trying to display the SUM of all of the table [tblLabor]'s "SubtotalTime" field entries that are filtered for the current service order. I would like to have the sum result stored in the [tblOrders] table's "TotalLaborTime" field and not just display it on the form without saving it.
I have tried to go about this in a couple of different ways: I have tried entering the expression: =DSum("[tblLabor]![SubtotalTime]","[tlbLabor]") in the [frmOrder]'s "TotalLaborTime' text box's control source (which would only display the result sum but not store it in the [tblOrders]'s "TotalLaborTime" field). For simplicity I temporarily I left out the criteria section in the DSUM expression so that the sum would be of all of the [tblLabor]'s "SubtotalTime' fields and not just the sum of the current service order's "SubtotalTime"field. When viewing the form, the field displays "#ERROR" which flashes on and off rapidly. For what it's worth, from reading people's comments about this error, I don't believe that I have any other controls that have the same name.
I also tried entering the DSUM expression as the Default Value in the [frmOrders]'s "SubtotalTime" text box with its control source set to the [tblLabor]'s "SubtotalTime" field.There was no error shown in the text box but also there was no sum displayed.
Bottom line is that I need this total number of hours for all labor records for the current order # in order to calculate a total labor cost by multiplying the total hours by the labor rate. I would have attached my database with my post but the forum restricts the size of upload documents so much that I cannot even upload an extremely basic database with just a few sample records in it. Thanks again for anyone that can help!