Creating the Form event “On Recalculate”

This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event.

A form does not expose it's calculation dependencies chain. It exists — you can see calculated fields and controls being updated automatically — but this doesn't trigger any event. When we want to run some code (for example conditionally enabling a command button), we often need to trap one or several `after update´ events.

This article shows that a user defined function can be called automatically whenever Access decides to recalculate a control, in effect hooking into the dependencies chain, and thus creating an “on recalculate” event handler.

The Dependencies Chain

When a form is opened, Access will examine all calculated controls and build a hierarchy, specifying how controls depend on one another. This way, when you change the value of Text1, it will know that the control with “=Text1*2” needs to be updated, and possibly others depending in turn on that control.

It is relatively easy to obfuscate an expression so that Access will not catch the dependency. If you create a simple form (called Form1) with three text boxes (the first being named Text0) try these expressions in the next two text boxes:
Notice that the second control will be updated immediately, while the third requires you to press F9 to display the new value of Text0. This is because it isn't included in the dependencies chain. A more realistic example, retrieving the title of the employee selected in cboEmployee:
The difference is subtle, but Access will not examine the content of strings passed to functions, so it cannot know, in the first example, that the result should be updated when a different employee is selected. It could however be recalculated from the combo's `after update´ event.

The same principle applies to aggregate functions in the form footer. Any field or expression based on fields can be totalled using Sum(), and this result will be updated every time a record is updated, inserted, or deleted, and even when the form is filtered. To simulate this sum would require trapping at least four events.

The dependencies chain is thus an essential mechanism of the user interface to maintain the integrity of all calculations present in the form (and in the underlying query), without performing too many unnecessary calculations and screen refreshes.

Creating a Trigger Function

Once the mechanism outlined above is understood, the solution is quite trivial. Let's simply rewrite an expression we want to monitor in such a way that it calls a user defined function, as side effect so to speak.
I prefer the first version, but the second works just as well, provided it returns zero or Empty. The function can be in a global module, but its logical place is in the form module:

Public Function Trigger(Value)
                          ' perform something here, as an example:
                          Me.Caption = Timer
                          ' return the value (for the first version)
                          Trigger = Value
                      End Function
                      

Select all Open in new window

If you have created the form from the previous section, add the function above to its module and replace the control source of the second text box with:
Observe that the caption changes when you update the value of Text0. It also changes when you requery or recalculate the form (Shift+F9 and F9, respectively).

Uses for a Trigger Function

We can for example image an Order form, for a special offer limited to 20 items per customer. The trigger function could issue a warning or disable the [Confirm Offer] button when the total number of items is too high. In that case, the value passed to the function can actually be used within the function, and not just passed along.

More generally, it can be used whenever any property of the form or a control depends on a calculation. Some properties can be set through conditional formatting, or if a label caption depends on an expression it can be replaced by a text box, but some properties need to be set through code. Examples include button labels, metrics and visibility of controls, selection of a tab in a tab control, and formatting of controls that do not support conditional formatting.


The same idea can be used to manipulate the dependencies chain.

If a form contains a function that performs some complicated calculations, but which isn't refreshed automatically, you can force it into the chain by supplying an additional dummy trigger argument. For example if a function returns the result of a query, using only the main form's ID as argument, and it needs to be updated when data in a subform is changed, you could supply an additional, unused argument for the sole purpose of forcing the recalculation.

In other words, there might be existing functions called from a form which could be turned into trigger functions. Simply add arguments for every field or control on which the function really depends, even if they are not needed in the function, forcing Access to include your function in its dependencies chain.


A totally different usage of a trigger function is as a delayed Current event handler.

Some forms end up having a long Current event handler. This is the case when the form makes many adjustments depending on the current record, for example if some records are read-only and others are editable. Others can take a long time to execute, for example if an image is loaded in an image control.

When surfing through records becomes painfully slow, it makes sense to replace all or most of the Current event by a trigger function, typically using the source's key field, although any field would work.
This text box can be hidden (hidden controls are not excluded from the dependencies chain); it will call the function, but only once the form becomes idle. In other words, it will not be triggered for every record when scrolling or browsing quickly between records. This can greatly improve the so called “user experience” and make your form seem more responsive.

Conclusion

Although (or perhaps because) it isn't a frequent question, I know that many Access experts would not think to create a function specifically as trigger, even if they immediately understand the concept when they see it, of course. The first ideas are often to find a combination of existing events, to use the form timer, or to write a waiting loop (perform DoEvent's until the control has been updated).

I hope you will remember that you can in fact create an `on recalculate´ event, should you ever need one!


Markus G Fischer
(°v°)

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
This article is part of the EE-book unrestricted Access.