Go to Top

Maximizing Code Reuse: Multiple Event Handlers

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 Consulting

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:

17 Responses to "Maximizing Code Reuse: Multiple Event Handlers"

  • RGB
    January 7, 2016 - 5:59 pm Reply

    Hi Ben – this is fabulously helpful. Thank you for writing this.

    I’m trying to implement this with a report. I can get the report’s main events to work (eg GotFocus) but can’t make the Class code fire when events such as Detail_Format and PageHeaderSection_Format. I’ve tried prefixing with the sub’s name with my WithEvents variable name (eg rpt_PageHeaderSection_Format), with and with the underscore. Can you help?

  • RGB
    January 7, 2016 - 5:05 pm Reply

    Hi Ben – this is fabulously helpful. Thank you for writing this.

    My scenario is that I have a report that needs to be printed out on several different printers (including custom label and standard laser) with a one button click (ie they are outputted directly to specific printers). AFAIK there is no way to cleanly change the printer at runtime without user intervention. So I have three different versions with the three assigned printers. They are not quite identical copies as controls have been moved and paper sizes changed to accommodate the printers—but the code is identical.

    I haven’t implemented your code yet – but that’s the plan!

  • Maxmax.wanadoo@gmail.com
    September 10, 2013 - 2:27 am Reply

    Ben, this is really good, particularly as I am now trying to understand the WithEvents stuff.

    Do you have a version based on a “normal” form and not a “Continuous” form so that I can start off slowly.

    I am so pleased that I found your article – fantastic stuff.


    • Ben Clothier
      September 10, 2013 - 6:09 pm Reply


      I am sorry, but I don’t have a version that uses single-record form as the example. However, you should be able to use the same code on a single record form, which should still work but albeit a bit confusing because when you use either up/down arrow, it advances the record, even if there are other controls above or below the current control. The point is that it has nothing to do with form being continuous or not because both by design have only one “active” record so that the same code will work on either mode.

      I hope that helps and that you can get further with the WithEvents. Best of luck!

      • Max
        September 10, 2013 - 6:28 pm Reply

        Hi Ben, thanks for the reply.
        I have “sort of” got it working on one Form and then replicated the calling code on a second form and although it runs through the initialising stage ok, it wont run the code within.
        What i am trying to do it:
        1. When I change a control contents it sets a value in a hidden text control of the ID of the record being changed.
        2. Then it runs code in another class (not your, one that I got from elsewhere) that re-sequences the OrderSeq of the forms (which the user uses to set the order in which they want the records displayed – it is a long). BUT before it can do that it needs to set the record to not dirty, otherwise the resequencing will take place on the previous OrderSeq contents and not the updated value. This FAILS because I cannot find anywhere in your code to save the record. I have tried frm_dirty which works on one of my forms (the first one I tried your code on, but it is not called on the second form.

        I guess all that is not going to make much sense.

        I was wondering if you had a WithEvents that gave me access to both the Form Events AND the Control Events.


        • Ben Clothier
          September 11, 2013 - 12:07 am Reply

          Max, without seeing your code, it would be hard to give meaningful help. Consider posting at one of Access forums such as UtterAccess for help with the event sinking.

          BTW, to get both form and control events, you need two object variables like so:

          Private WithEvents frm As Access.Form
          Private WithEvents ctl As Access.Textbox

          You can then sink events for those two objects and run code in the same class module. Hope that helps.

          • Max
            September 11, 2013 - 12:28 am

            Yes, Ben. I will try those suggestions. Many thanks

  • Werner
    January 16, 2013 - 4:00 pm Reply

    Hi, Ben

    Very interesting stuff.
    Thank you for sharing it.

    But I just have a problem with it:

    How could I implement an Extended event-handler for a section in your class, i.e. MouseMove for the detail-Area?

    In my german version the section is named “Detailbereich” (by Access).

    Trying to catch the event in your class by using

    Private Sub DetailBereich_MouseMove (…)

    [Like you did it for frm_Keydown(…)]
    has no result.

    Thanks and greetings from Germany


    • Ben Clothier
      January 17, 2013 - 2:35 am Reply

      Werner, You need to have an object variable to provide the events. In my quick test, this would be what you’d want:

      Private WithEvents DetailSection As Access.Section

      Private Sub DetailSection_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)

      End Sub

      You would then instantiate the “DetailSection” object variable by doing something like “Set DetailSection = InputForm.Section(acDetail)” in steps that I outlined for the Init procedure.

      I hope that helps get you started.

  • Anders Ebro
    January 10, 2013 - 4:52 am Reply

    Very very interesting read. Thank you for sharing it. I never knew that a object could have multiple event handlers for the same event, I think I just jumped to the conclusion that one would replace the other, and never bothered to check otherwise.

    A few questions:
    Why do you have a “Resume” after “resume exitProc”?

    Do you have any suggestions (not code) of usefull places to apply this approach besides the example (continues form, onKeyDown) here? I am trying to best understand where to potentially use this.

    • Ben Clothier
      January 10, 2013 - 5:11 am Reply

      Thank you for your kind words!

      Great questions!

      1) Resume is not actually reachable in production but I put it in there so that when I’m developing, testing and debugging, I have a easy way to get to the offending line without adding line numbers and using old Erl method. Here’s how this works:

      I do something that goes wrong; the error handler executes and shows a MsgBox. I then hit Ctrl + Break to break into execution mode, set the execution on unreachable “Resume” which moves me to the exact line that caused the error and get to fixing hte error right away.

      This is a big timesaver in debugging and because it’s after Resume Next, doesn’t require me to remove the line like Stop statement would. Nice’n’tidy!

      2) Few examples where I’ve used:

      a) To manage a quantity control that’s used on a inventory control application to maintain the quantity on hand calculation – they have it recurring on different forms for each step of inventory (ordering -> receiving -> outgoing)

      b) to handle unexpected ODBC errors & provide diagnostic/friendly error message via form Error events for SQL Server backend on the account of KB article: http://support.microsoft.com/kb/185384; note that the KB article is mistitled; it’s for Error event, not Open event.

      c) to manage pair of listboxes on various forms where you have all choices on left and put in selections on right – instead of coding it for every forms that uses this listbox, have the class manage it via their intercepted events and form get to do their specific thing with the selections

      d) to manage ODBC transactions for set of forms with MySQL backend (e.g. committing a parent and a set of child records all together as a single transaction or drop them all together)

      Again, in all of those scenarios, each form also had their own specific requirement (e.g. one of form may want to reject a change to the quantity on its BeforeUpdate which for the generic class is permissible).

      There may be few more but I hope that gives you ideas of what this allow us to do.

  • grovelli
    October 31, 2012 - 5:24 pm Reply

    Hi Ben, when you say “We assign “[Event Procedure]” to one of form’s properties such as OnKeyDown”, do you mean you do that within the full code for the KeyNavigator class? You continue by saying, “which actually correspond to “the Key Down Event shown on the Event tab.” but the Event tab actually displays “On Key Down”.

    • Ben Clothier
      October 31, 2012 - 6:31 pm Reply


      Good questions.

      The “On Key Down” as we see on Event tab and VBA’s counterpart, “OnKeyDown” are form’s event properties whose job is to enable or disable event handling for the given event, in this case, the “KeyDown” event. The VBA procedure named “Form_KeyDown” on the form’s module is the actual event handler. Behind the form’s module, “Form_” is the prefix that must be appended to the name of event in order for VBA to discover the event procedure. Hence, in our custom class, we had a object variable “frm” which means the prefix is now “frm_” instead of “Form_”. However, neither event handler will never run if the OnKeyDown property of the form isn’t set to “[Event Procedure]”. A blank event property means there is no handling requested for this event. I shouldn’t have said “the Key Down Event” but actually “the On Key Down event property”. Thanks for the catch; I’ve corrected this part.

  • grovelli
    October 23, 2012 - 8:35 pm Reply

    “this does not mean that the event handler cancelling the event will actually stop the object from notifying the other event handlers. ” How does the object notify the other event handlers? And what are these other handlers? For example, in the case of Sub Form_BeforeUpdate, what are the other event handlers being notified when we set Cancel = True?

    • Ben Clothier
      October 23, 2012 - 9:05 pm Reply

      As mentioned in the article, you can wrap a If/Then test in case of BeforeUpdate event. When one event handler sets Cancel parameter to true, then the next event handler that get notified will see Cancel = True so that’s how you know it was cancelled. For events with no parameters, there is really no way of knowing unless you maybe set up a global variable to cache the information. But really, I don’t recommend designing handlers this way. It’s best that they act without any kind of interdependence between the handlers – they should be made to do their job based on an event.

      Also, note that if we had 3 event handlers and it happens to be 2nd event handler that cancels the event, the 1st event handler will have already completed while 3rd event handler will be run still. Unless the 3rd event handler is coded specifically to not execute when Cancel = True (such as wrapping in a If/Then), it’ll still execute.

      So in general, I think it’s best that you have a clearly defined scope of what your event handler should do and it shouldn’t overlap with other. For example, it’s probably fine to have a generic BeforeUpdate validating event handler in conjunction with a form-specific event handler to validate the data before saving record, since it doesn’t matter which one fires first and more importantly, doesn’t matter which one get to cancel; only that it’s cancelled in event of failed validation. Another valid use for multiple event handler is a role of monitor. In case where you’re using events to (for example) send an email, then this is probably best when you cede the control entirely to this event handler since you don’t want email sent for event that was cancelled, creating confusion.

      In short, if you want to use multiple event handlers, you can but you need to plan it out exactly.

      I hope that helps.

  • Andrew Richards
    October 23, 2012 - 5:08 pm Reply

    That’s some pretty cool stuff.

    I was interested not only in the use of a custom class to handle the keystrokes of another form, but also in the way you’re handling the navigation without using DoCmd.GotoRecord…

    I have to admit also to not having used the SelTop property of forms too – would I be right in assuming that this sets the topmost visible data row of a form in Continuous Records view? And does it matter if the form is a subform of another form?

    Thanks a lot – great post.


    • Ben Clothier
      October 23, 2012 - 5:39 pm Reply

      Thanks, Andrew!

      Well, that’s more of a side effect rather than intention. You normally would use SelTop in conjunction with SelLength to mimic the action of selecting a number of records via record selector (gray box on the left side of a form) for perhaps copying/cutting/pasting. However, I chose to use SelTop as an mean to access the new record placeholder, since changing the SelTop also effects the position of pointer. New record placeholder at the end of form’s records is entirely a form artifact. In a recordset object, you can’t just “move” to a new record. When you issue a AddNew command on form’s recordset, this mimics the action of typing something in the form’s new record placeholder but that creates several side effects; a new autonumber is incremented, the form is now dirty and so forth. That wasn’t what we wanted for simple moving focus around. So while you can use Form.Recordset.Move*** methods safely to move your current record around, going to new record just needed to be handled via Form’s members rather than via Recordset members.

      And no, that’s the whole purpose of referencing frm object variable. Because you have a specific object variable, the class will work no matter how you had it opened, whether it’s embedded 3 deep as a subform or if it’s running in another Access application. That would be difficult to achieve using DoCmd.GoToRecord or DoCmd.RunCommand acCmdRecord*** methods.

      Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *


Contact Us
close slider
  • This field is for validation purposes and should be left unchanged.