Sum a field in a subform

[Tvanduzee]

Hello All

I have a form/subform setup.
In the subform, I have a field that records work done for the work order. There can of course be several items in the field associated with the work order.
I am trying to calculate the sum of the "Cost" field for that particular record (some can have 80 items listed in the subform which are all associated to the one work order). I can't seem to get anything except "error" in my unbound text box.


I set the control sorce to "=Sum( [qry_WO_Details Query subform1].Form![WO_Details_Cost] )" (no quotes). I'm trying to put it in the form footer. There are other fields I need to sum up as well.

Any suggestions?

Thank you
Terry

[Rod]

Hi Terry,

You can't do this by examining the sub form since the sub form has one and only one active record/line at a time. You need to go back to the table(s) on your database and use the DSum function. Read up on it and see if you can solve your own problem; otherwise get back to us.

[Tvanduzee]

Hey Rod
I'm not seeing how to use the dsum in a table, and I tried in a query, but I'm only summing 1 field (Cost). Maybe I'm having a blonde moment or something, but I cannot see how to use it for this situation.

Thanks
Terry

[Rod]

Hi Terry,

In summary the syntax for the DSum function is as follows:

DSum(expr, domain [, criteria] )

where expr is the name of the column/attribute that you wish to sum, domain is a table or non-parameter query and criteria (optional) are the selection rules for which rows/records you wish to be included in the sum. DSum may be used in two places: as the Control Source for a form field or in VBA code. For your immediate needs we'll use the Control Source; if you wish I'll explain later how to do the same thing via VBA.

(Tip: Sometimes the space alongside a property in the Property Sheet is too small to view everything at once. Press [Shift] +F2 and open up an editing box where you may see and edit everything at once.)

OK, you need to do the following.

1. Define a textbox somewhere appropriate on your form. I believe you have chosen the main form footer for this.
2. Decide on the best source for your individual costs, table or query. Usually there is only one source and hopefully this is a table. We'll assume a table for the purpose of this example that is called, tblWODetails.
3. In design view for the main form, select the new textbox, txtTotalCost, and open the Property Sheet if necessary.
4. Alongside the Control Source heading in the Data tab, enter =DSUM("WODetailsCost", "tblWODetails", "WONum = " & Me.WONum)

I have indicated in blue where you should use your own names. What this is doing, in English, is adding all occurrences of WODetailsCost on table, tblWPDetails, where the WONum is equal to the value in the field called, WONum, in my form.

As you browse from record to record, Access should automatically recalculate this total cost for you as Me.WONum changes. If the work order details table is large then you might take a performance hit.

If you are adding new details in the sub form (or even deleting details) then the total cost will not update. If you need this then get back to me.