DSum on main-form from subform (1 Viewer)

[bevc]

Hi all,

I'm hoping that this question will have a simple answer, I've searched the forum but can't find what I'm looking for (or maybe I have and don't know it). I have a subform from which I want to calculate values into a text box on the main form, to keep a running total of weeks for individuals.

Using the expression builder to just add the fields, I only get the total for the current sub-form record. If another record is added to the subform, the total reverts to zero, and then it takes that record's input as the total. Which makes logical sense.

I think I need to do a 'DSum' from the subform/table, but I'm not sure how to sum for just the current ID/individual. I have tried to bodge it myself with the expression builder, but it tells me that 'the function contains the wrong number of arguments'.

Could anyone help me please? Thanks.

 

[Ranman256]

the sub form is based on a query... qsSubRecs (using main form ID?)
put a text box on the main form to sum it...

=Dsum("[amt]","qsSubRecs")

if qsSubRecs does not use ID in the criteria, then you must put it in the DSUM cmd.
=Dsum("[amt]","qsSubRecs","[id]=" & txtBoxID)

 

[Minty]

There are two ways to normally achieve this; create the sum on the sub-form hidden and reference it on the main form, or correctly format the DSum on the main form.

Can you post up your example so we can see what you've attempted?

 

[bevc]

There are two ways to normally achieve this; create the sum on the sub-form hidden and reference it on the main form, or correctly format the DSum on the main form.

Can you post up your example so we can see what you've attempted?

Thanks very much for responses, a hidden sum on the sub-form might be a great solution if I can make it work. Screengrabs of relationship (the mainform is the 'Apprentices' table and the subform is the 'Line Claim' table, I'm looking to sum 'NoOfYr1Weeks' and 'NoOfYr2Weeks' on the main form), and my failed attempt at expression building attached.

 

[Minty]

Okay - you have to add them together as 2 separate calculations.
DSum(1YearWeeks) + DSum(2yrWeeks)
Your syntax looked pretty solid. I would do one then add the second one once you know you have it correct,

 

[bevc]

Okay - you have to add them together as 2 separate calculations.
DSum(1YearWeeks) + DSum(2yrWeeks)
Your syntax looked pretty solid. I would do one then add the second one once you know you have it correct,

Hmmm, that doesn't work either, if I use an expression to DSum just one of the fields (as attached) I still get a message saying 'expression has a function containing the wrong number of arguments'. If I try and type it 'manually', Access tells me I have invalid syntax. I thought it might even be because my field names have a number in them, so I tried without, and that didn't help either.

I'd be very grateful for any thoughts/suggestions/insults. Thanks.

 

[Minty]

Actually - I misread the screen shot first time round your syntax isn't correct.
The correct syntax is
DSum("The Field you are trying to sum", "The Table or query it comes from", "Optional Criteria Goes here")

Optional criteria can use current form or sub form values, fixed text or values.

Rather than pictures of text, just cut an paste the names / text from the forms/ queries you are having an issue with, or try and create a query that give you what you need for all records then look up the matching one.

http://www.techonthenet.com/access/functions/domain/dsum.php