Form Universal Update

[dcroe05]

I'm working on a database to capture data from a very long application. On one form in particular I have about 200 calculations that need to be done, and these calculations need to be stored in fields. I've created a function to handle all the updates at once but...


Is there any way to have the function triggered each and every time ANY field is updated, without having to create an Event Procedure for each and every field?


Note: Before you tell me this is poor database design...I couldn't agree more, but I've already had and lost that fight with my supervisors.

 

[jrbarnett]

Call it from the Form_BeforeUpdate or Form_AfterUpdate event handler. That gets triggered every time a field is updated.

John

 

[jrbarnett]

Actually, don't use AfterUpdate as it will cause another update which could end up in a cycle - just use BeforeUpdate.

John

 

[dcroe05]

John,

Thanks for your comment. The problem with the Form_BeforeUpdate solution is that it will only update the record right before the changes are committed (i.e, right before you move to the next form).

I was really looking for a way to update the totals columns immediately after any other fields were updated.

Dale

 

[ProgramError]

have you tried on exit or on lost focus?

Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[Remou]

This will do it, but I have not tested the implications:

Private Sub Form_KeyUp(KeyCode As Integer, Shift As Integer)
'Note: KeyPreview must be Yes
    Me.Recalc
End Sub

 

[MajP]

If it was me I would build a custom class and custom collection and use "with events" to capture the event procedures. I have described this technique before on this site. This then allows you to build one event procedure in the custom class and no event procedures on the form itself. On the form all you would need is a short for loop to load up the controls. Each control will then react to that single coded event. The amount of code is very short, but a little involved if you have never done that before. I could post an example if interested or get your hands on Litwin and Getz's Access Desktop Developers Handbook to better understand this invaluable concept.

 

[MajP]

If interested in this concept I recently posted a example in thread705-1437513. However without the second part, the class collection, you really do not see the utility because you would have to dimension and set 200 variables. Once you build a very small collection class then you can load up all those control variables in a for next loop. If interested I can post an example custom collection, but you can demo the code I posted.