Sum of field in record of subform

[apestaart]

I have the following problem (uppertunity).
I have a mainform with a combobox to select a month.
In a subform I have info about persons per month.
Mainform and subform are linked via a monthID.
So I can select a month and via a select query I have per person ours worked, ours vacation etc. What I want to do is add a field with the sum to date of vacation ours.

I tried to solve that with defining a function in my query, but then I get runtime error 3262, telling me that it is not possible to have a function looking at the same collection of data.

Is it possible to do this in an other way?

regards apestaart

 

[genomon]

Check out Access help on the DSum() function.

Outside of a dog, a book is man's best friend. Inside of a dog it's too dark to read.

 

[apestaart]

Thanks genomon, this gives me a direction. But I have problems to define the where portion of the function.
What I need is per record per RelId the sum to date.
I tried this :

 cummTVT: DSum("[Urencontract]-[Gewerkte uren]";"Tabgewerkte uren";"[RelID]= Forms![Selektie voor Excel].[Uitvoer naar excell].Form![RelID]")

I have put this in the query for the subform "Uitvoer naar excell", but it does not work.
When I replace "Forms![Selektie voor Excel].[Uitvoer naar excell].Form![RelID]" by a number (an existing RelId) then it works , but all the records have ofcause the same value.
What should I do?
Regards,
Apestaart

 

[tanolalano]

Try this:

Forms![Formname]![ctlname] = DSum("[nameFieldtosum]", "[tbloforigin]", "[RelID]= Forms![formname]![RelID]")

The first part is the control in the form in which you want displayed the sum (Forms![Formname]![ctlname]).
Then [nameFieldtosum], is the field where it does the sum.
[tbloforigin] is the table that store [nameFieldtosum].
The criteria of sum: [RelID] is the field in the [tbloforigin] where is equal to the control on the form tha store the ReiID, i.e. Forms![formname]![RelID]
Hope this helpf you

 

[apestaart]

Thank you Genomon and tanolalano, it finally works.
It seems that the function is very sensative for extra paces between " " . For "Forms" I had an extra space and that caused the problem in defining the where part of the function.
Best regards Apestaart