Recordset values within recordset

[stormin_norm]

I am going crazy trying to find a solution to this problem. I believe I will begin to create some monster code. There MUST be a better way!

I have a form with recordset of colleges and subform with recordset of courses taken at each college.

I need to total ALL the credits on my mainform.

something like:
for each college
total=total+sum subform.courses
next college

naturally this is dynamic, if a course is modified the total must update immediately. Do I need to create a recordset clone and then loop through each?

 

[FoFa]

Can't you use a dsum with the same criteria you use to link the subform to the main form?

 

[stormin_norm]

Isn't the Dsum running a query against the data stored in the tables?
I would need this to be dynamic.
So that when a course is modified, i.e. change credits from null/blank/zero to 3 then the subform total changes to 12 and the main form total changes to 32 (or whatever the numbers will be).

Which brings up another point. in the subform afterupdate, it will trigger a main form event. mmm.

 

[FoFa]

Just requery the control with the dsum after an update......

 

[stormin_norm]

Works fantastic! I was hung up by not having quotes around every parameter.
It is a bit slow- you can see the screen blink when its doing the computation... If I get user complaints, I'll put it in vba code to see if it helps.

Thanks FoFa!! Remeber the Alamo!
sorry, had to throw that in there.
I fell in love with Austin when I was at a conference. GREAT BBQ & nightlife!

 

[stormin_norm]

Correction/clarity- the screen update can be noticed because of the recordset update, not the calculation.