Sum updating late in a continuous subform

[bentheimmigrant]

I have a subform with a textbox (Total_K_for_pipe) whose control source is

=Sum([Total_K_for_type])

(don't ask me, I didn't name these things)

Total_K_for_type is a calculated field in the back end, based on two other inputs on the same subform.

I have set it up so that AfterUpdate for either of those fields, I update the contents of a field on the parent:

Me.Requery
Me.Parent!K_all = Me.Total_K_for_pipe

Now, the subform works fine: when I change either of the fields, it updates the sum. However, the sum is blank for a brief moment, and during that time, the K_all box is filled (with nothing). After that, the sum appears.

Is there anything I can do to make sure the sum is calculated before the controls are updated? I can't get AfterUpdate, OnChange, or Dirty to be called for the sum box, so it looks like I can't put any triggers on it directly.

I've tried adding Me.Repaint before it sends the value to K_all, but that didn't help. I've also added "msgbox Me.Total_K_for_pipe" to see when things happen, and sure enough, that comes in blank, and the sum box stays blank until after I dismiss the msgbox.

One thing of note: when the parent form is open, the VBE tells me that there is code running. I can't work out what code that is. Do calculated values in controls run VBA constantly? I've looked at all the code for the two forms, and there are no loops, and nothing that makes sense as a continuous trigger.

 

[arnelgp]

when you Requery your subform it retrieved current data from the table. so it will take time to fill the recordset, specially on split db.
i don't think you need to requery every time you change or add new record. just a simple me.parent.refresh is enough. it doesn't fetch current data, it just update the values of your main form.

 

[bentheimmigrant]

The trouble is, I need it to update the back-end.

If a user updates a control on the subform, then clicks save on the main form, I need it to update the table, recalculate the Total_K_for_type (which is in the back-end), then sum the subform, before writing that to K_all.

I think I'll just have to abandon Sum, and do it myself in VBA.

 

[arnelgp]

' Call the Idle method to release unneeded locks, force
' pending writes, and refresh the memory with the current
' data in the .mdb/accdb file.
DBEngine.Idle dbRefreshCache

 

[bentheimmigrant]

Thanks, I'll definitely file that one away for future use.

As it is, I simply called up the recordset and summed it separately in VBA to write to the main form.