Sum subForm via dsum

[stormin_norm]

I am getting a "delay" in calculating a sum because the dsum is performing the requery before the new data is stored in the database.
Is there a way to get the requery to run AFTER the data is written to the DB? Should I throw in another requery on the form, not just the field level?

background:
Transfer student has records in 2 table: College and Course which contain the name of the colleges the student attended and the courses they transferred from each. The user can enter how many credits to transfer into "english", "Math", etc...

So when you want to put in three credits for a course it will total on the subform correct, but on the main form it does not dynamically sum.

My main form is linked to the college and subform to the courses taken at that college.
On the main form is a field for totaling ALL credits for ALL colleges attended (dsum).

subform field: [ENGhours] afterupdate=[Forms]![frmCollegeCourse]![ENGtotal].Requery
subform field: [ENGsubtotal] =Sum([ENG hours])

main form field: [ENGtotal] controlsource=DSum("[ENG hours]","qryCourse","[qryCourse.StudentID] = " & [StudentID])

 

[pono1]

AfterUpdate Event

Norm,

I'm not sure I follow completely, but it sounds like a quick and easy short-cut would be to use code. As you know, Access alerts you a rec has just been saved with the AfterUpdate event, so in the AfterUpdate Event of the subform, experiment with something like this:

Me.Parent.EngTotal = DSum("[ENG hours]", _
"qryCourse","[qryCourse.StudentID] = " & [StudentID])

Regards,
Tim

 

[stormin_norm]

Works GREAT! Thanks! Better response/no screen flicker vs. the requery I was using before.

Attached is a word doc with screen shot. Sorry not a jpg, the image editing software I have installed clips the jpg and it is taking too long to figure out vs. paste in word.

Notes on pic: Barney Rubble has attended several colleges. In the screen shot, college 1 is displayed with sum of transferred credits for college1 on the subform and sum of ALL colleges on the main form.

 

[stormin_norm]

I TAKE IT BACK. It does not work fine.

The Sum([field]) on the subform will update once I leave that row (continuous form). And the dsum (total for all colleges) on the main form still does not update immediately with the AfterUpdate event.
Anyone have a suggestion??? Is there a better event to use? Or force an additional requery/refresh somewhere else?

Thanks!
Norm.

 

[pono1]

Norm,

I would also look at the On Current event, putting similar code there to fill the textbox on your main form.

Regards,
Tim