This is first part of two-part series discussing about maximizing code reuse. Part two is here.

One coding technique that I feel is frequently overlooked in Access application is the fact that we can assign multiple event handlers to the same object event and/or share the same event handler among different objects’ events. To provide an example, let’s consider how we could improve the continuous form. One nice thing about continuous form over datasheet view is that we have more control in how we can format the layout. However, one glaring omission is that you can’t navigate controls easily with keyboard as you could with a datasheet view.

MS Access Report Generator

Now, this opens up a can of questions –
1) how would we add the code needed to support key navigation to all existing datasheet forms?
2) how would we merge it with any existing event handlers?
3) how would we do this in minimum of steps?

The answer to all 3 questions is probably best expressed in a class module. With a KeyNavigator class, we could then add only four lines to each continuous form (not counting blank lines & procedure stub) for where we want to enable key navigation:

How could those 4 lines provide all the functionality? Didn’t we have to attach event handlers? How does it know when we’ve pressed key and moved to another record? At end of blog, I’ll post the full code but for now, let’s look at the Init procedure as well as module variables for the class KeyNavigator to see how we wire up the events to be subscribed in the class module.

Note how we have a variable declared at class level, with keyword “WithEvents” which let VBA knows that we want to be able to subscribe to this object’s events. We then, of course, assign the Init’s SourceForm to the internal WithEvents frm variable. But this is not sufficient in itself to subscribe to the form’s events.

Think back to how you usually design event handlers. In a form’s design view, you would have to go to an object’s Events tab, add “[Event Procedure]” to the event you wante to work and it would auto-generate a stub in the module behind the form. The process is still the same but the steps are now different for a class module with a WithEvents tab. We assign “[Event Procedure]” to one of form’s properties such as OnKeyDown which actually correspond to the “On Key Down” event property shown on the Event tab. (In fact, several properties starting with “On” are actually event properties, but note that some don’t have “On” prefix; “AfterUpdate” and “BeforeUpdate” being notable example). By doing so, we are declaring that we want this event to be handled. Otherwise, the form objects will think that nobody is listening and won’t bother telling anybody else “hey, I’m done with this event, you want to do something?”

The manual assignment in the code also is necessary in case when the class is passed a form that has no event handlers whatsoever – so this approach works whether the target form already has or doesn’t have an event handler for the same event. The only downside is that if we use function instead of event handler, the function would get stomped on. For example, if you entered instead “=MyFunction()” in the event property, the above code would change the behavior and the function would be no longer called. In our projects we don’t use the function approach, preferring to use event handlers everywhere so that’s not a concern but for different projects, this is something we need to look out for. This also can be handled by detecting whether a function exists and calling it with Eval but that’s beyond scope of the article.

Now, with assignment of object to a WithEvents variable and setting the event handler to [Event Procedure] we now can add new event handlers. Because we gave the variable name of “frm”, it also becomes the prefix, so instead of “Form_KeyDown” as we would see in the code behind form, we get “frm_KeyDown”, but they are handling the same event for same object.

If you place the cursor inside the body of frm_KeyDown procedure, note the VBA editor showing “frm” on the lefthand dropdown and “KeyDown” on the righthand dropdown. If you open the righthand dropdown, you’ll see the same listing of events for a Form and selecting any one of them will generate a empty event stub for you, just like when you add an Event Procedure and click “…” button in the design view.

So, hopefully, you can see how four lines in the original form’s module can be used to add far more details and keep the code clean. If you find a bug in KeyNavigator, you go to KeyNavigator’s module, fix it and pesto! All form subscribing to this class get fixed, too! New functionality? Ditto; go to the KeyNavigator’s module, add it, and all form get to enjoy the new functionality.

More importantly the code for handling key navigation is kept separate from the code that is specific to the form; we don’t have to worry about mixing two different functionality in same event handler. So for a form that uses KeyDown event for other purposes, you still can run the form-specific code in Form_KeyDown procedure and both Form_KeyDown in form’s module and frm_KeyDown in KeyNavigator’s module will react to the same events.

With this in mind, there are few caveats that needs to be addressed.

1) The order of which Event handler fires order are not guaranteed
As far as I know, there is no explicit documentation on the subject and informal testing suggests that the order of which event handlers are fired is usually the same order they are attached to the object. Because the form’s module will load first, it usually means the event handlers within form’s module will be handled before any other event handlers somewhere. However, I strongly recommend against relying on the assumption that one event handler will fire before other. It’s probably best that we write the event handler in such way that it won’t matter what the order is – think of each event handler as an island with only bridge back to the mainland (the object firing the event) but no bridge to other islands.

2) Don’t try to modify event parameters between event handlers
This is corollary to #1 but very important one. Let’s use BeforeUpdate event as an example because this has a Cancel parameter:

When we set Cancel = True, the BeforeUpdate event will be cancelled. However, this does not mean that the event handler cancelling the event will actually stop the object from notifying the other event handlers. One way to ensure that all other event handlers don’t do anything is to wrap the body with a check:

It’s reasonable enough to read the parameter to see if a previous event handler may have attempted to cancel and follow along, but it is not good idea to have something like this:

This effectively countermand the previous event handler’s attempt to cancel, allowing the event to proceed. This can be quite confusing and as explained, we should not rely on certain ordering of event handlers’ firing. Otherwise, this could produce unexpected results where sometime the countermanding actually happens or doesn’t happen. While the sample is a bit absurd it should illustrate why we should not design our event handlers to try and reach into other event handlers’ but simply accept the defaults and only change the event parameter because it needs to (e.g. it wants to cancel the event in actuality and not merely to undo the effect of other event handler or attempt to modify behavior of other handlers).

3) Avoid DoCmd commands where possible when writing non-interactive code
When writing code that works with other object variables, we don’t always have full knowledge about the context the form is in. If you look at the full sample code, you’ll note that there is no reference to DoCmd anywhere and for a good reason. DoCmd methods are inherently interactive – they mimics the action that would happen when an user click on something and thus work with active object but we don’t have guarantee that the active object is the same object we want to work with. Take DoCmd.RunCommand acCmdRecordGoToNew for example. There is no parameter for us to specify which form this action should be performed upon. DoCmd.GoToRecord gives us a parameter for entering form name but this does not work if the form is a subform; it must be a top-level form. Writing robust code usually necessitates identifying and using methods that do not depend on being interactive. There are times when this cannot be avoided. For example, DoCmd.OpenForm / DoCmd.Close is the only way to add/remove a form from Forms collection.

Stay tuned for the second part to see how we can quickly add those 4 lines to 100s of continuous forms!

Here’s the full code for the KeyNavigator class: