Updatable dynasets and running sums

[ednz]

I am using subqueries to calculate running sums, but find that their presence makes the query not updatable, especially if the query is based on two queries with a one to many relationship.

I'd like to hear what people do to overcome this problem.

I'm generally interested in how to create subforms with calculated fields, or based on multiple joins, but where some other fields can be updated.

 

[DanJR]

I've never had to deal with this problem, but...

-what about using an 'external' expression to calculate the running sum, i.e. a public function, rather than in the query.

say, set the control source to:
=fGetRunningSum([Row Identifier])

and

Public function fGetRunningSum(ID as long) as double
'code to calc running sum from recordset.
'or pass the form's recordset to this function
end function

-may incur performance problem though
-control may also need updating via code when data changes
Cheers,
Dan

 

[ednz]

Thanks DanJR

Using subquery as recordsource makes dynaset not updatable.

I'll try your idea.

I've resolved it sort of by using a separate subform to alter data, and then code to requery the non-updatable query's subform.

I'm trying (not yet successful) to use code from keypress event on non-updatable subform to edit table , and then requery subform.

It'd be really good to find out the underlying rules, so as to avoid this need for trial and error!

Regards

Ed