1

Sorry if this question is simple but I have googled and haven't found a satisfactory answer.

I'm creating an engineering cost estimator. I have a form that takes inputs as ISO/Drawing, and for each drawing number are many subforms where you can input ComponentDesc and should spit back out a TotalHours to complete number. I've included a picture (sorry for bad quality)

From the image, the table on the bottom is a subform in datasheet mode (which is usually hidden and located in the footer) which will calculate appropriate Total MH (manhours) for the ComponentDesc inputted into the subform on the right. I would like the small (and incomplete) textboxes to the left (below the title "MH Totals for ISO/Drawings") to display the aggregate total from the subform on the bottom.

I've been trying to use DSUM() to define Control Source for the textbox but it keeps coming back with #ERROR as seen in the textbox to the left. Right now what I have typed out is:

=DSum("[Total MH]","frm-PipingHandleMH")

in the expression builder. [Total MH] being my field and frm-PipingHandleMH being the subform on the bottom. I've tried to put brackets around everything but it didn't work (even though I'm not exactly sure what brackets usually do). Any advice?

FriendlyNeighborhoodNSA
  • 29
  • 4

1 Answers1

0

DSum (and all domain aggregate functions) acts on a table or query. If you want to use that approach you need to refer to the source of that form and use a filter parameter to limit to appropriate records the I.E. if the form's datasource is qry-PipingHandles and if the form you are trying to sum on currently is showing handles for widget 4 then it would be something like:

=dsum("[Total MH]","qry-PipingHandles","[widgetID] = 4")

Note that if that 4 was the currrent state of form then you need to pass it in, so something like:

=dsum("[Total MH]","qry-PipingHandles","[widgetID] = " & [frm-PipingHandlesMH]![WidgetID].Value)

Where you reference the field in the form and append it onto the string that is applied as a filter to the source for Dsum.

Another approach is to put a subtotal in the footer of the form (iirc you don't actually need to show the footer) and then reference that footer control from the parent form.

Brackets are needed to demarcate names that include spaces or other odd characters, they also can be used (e.g. in query design view) to force Access to treat something as a name rather than a string literal.

elc
  • 1,962
  • 20
  • 24
  • Thanks for the response elc. Would you happen to know the syntax for the criteria if I wanted query filtered based on the **ISO/Drawing** criteria in the upper left of my master form? And I'm guessing that **[widgetID]** is referring to a field in **qry-PipingHandles** that I would filter to get the current **ISO/Drawing** list of manhours? – FriendlyNeighborhoodNSA Jun 01 '17 at 17:48
  • Sorry don't mean to spam you but I'm using:`=DSum("[Total MH]","qry-PipingHandleMH",[ISO/Drawing]=[Text_ISOdrawing])` now and for every new **ISO/Drawing** record on the master form, it still just gives me the aggregate sum of the entire field in **qry-PipingHandleMH** without any filtering in the **ISO/Drawing** field. **Text_ISOdrawing** is the textbox control in the upper left of master form. – FriendlyNeighborhoodNSA Jun 01 '17 at 18:06
  • Thanks! Turns out I needed to add ".value" to the back of widget ID. And I forgot to put it in quotations. I appreciate your input nonetheless. – FriendlyNeighborhoodNSA Jun 02 '17 at 18:12
  • Doh, sorry for omission. Answer edited accordingly. – elc Jun 06 '17 at 17:21