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.

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:

Private kn As KeyNavigator

Private Form_Load()
  Set kn = New KeyNavigator
  kn.Init Me
End Sub

Private Form_Close()
  Set kn = Nothing
End Sub

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.

Private WithEvents frm As Access.Form
...
Private Const Evented As String = "[Event Procedure]"

Public Sub Init(SourceForm As Access.Form)
...
    Set frm = SourceForm
    frm.KeyPreview = True
    frm.OnKeyDown = Evented
...
End Sub

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 parameter 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.

Private Sub frm_KeyDown(KeyCode As Integer, Shift As Integer)
    ...
    Select Case KeyCode
        Case vbKeyUp
            ...
        Case vbKeyDown
            ...
        Case vbKeyLeft
            ...
        Case vbKeyRight
            ...
    End Select

...
End Sub

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 the … 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:

Private Sub Form_BeforeUpdate(Cancel As Integer)

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:

Private Sub frm_BeforeUpdate(Cancel As Integer)
  If Cancel = False Then
    'perform usual actions
  End If
End Sub

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:

Private Sub frm_BeforeUpdate(Cancel As Integer)
  If Cancel = True Then
    Cancel = False
  End If
End Sub

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‘s 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 is the only way to open a form in diagog mode using acDialog argument.

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:

Option Compare Database
Option Explicit

Private col As VBA.Collection
Private WithEvents frm As Access.Form
Private ctl As Access.Control

Private lngMaxTabs As Long
Private Const Evented As String = "[Event Procedure]"

Public Sub Init(SourceForm As Access.Form)
On Error GoTo ErrHandler
    Dim varTabIndex As Variant

    Set frm = SourceForm
    frm.KeyPreview = True
    frm.OnKeyDown = Evented
    With frm
        For Each ctl In .Section(acDetail).Controls
            varTabIndex = Null
            On Error GoTo NoPropertyErrHandler
            varTabIndex = ctl.TabIndex
            On Error GoTo ErrHandler
            If Not IsNull(varTabIndex) Then
                col.Add ctl, CStr(varTabIndex)
                If lngMaxTabs < CLng(varTabIndex) Then
                     lngMaxTabs = CLng(varTabIndex)
                End If
            End If
        Next
    End With

ExitProc:
    On Error Resume Next
    Exit Sub
NoPropertyErrHandler:
    Select Case Err.Number
        Case 438
            varTabIndex = Null
            Resume Next
    End Select
ErrHandler:
    Select Case Err.Number
        Case Else
            VBA.MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Unexpected error"
    End Select
    Resume ExitProc
    Resume
End Sub

Private Sub Class_Initialize()
On Error GoTo ErrHandler
    Set col = New VBA.Collection
ExitProc:
    On Error Resume Next
    Exit Sub
ErrHandler:
    Select Case Err.Number
        Case Else
            VBA.MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Unexpected error"
    End Select
    Resume ExitProc
    Resume
End Sub

Private Sub Class_Terminate()
On Error GoTo ErrHandler
    Do Until col.Count = 0
        col.Remove 1
    Loop
    Set ctl = Nothing
    Set col = Nothing
    Set frm = Nothing
ExitProc:
    On Error Resume Next
    Exit Sub
ErrHandler:
    Select Case Err.Number
        Case Else
            VBA.MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Unexpected error"
    End Select
    Resume ExitProc
    Resume
End Sub

Private Sub frm_KeyDown(KeyCode As Integer, Shift As Integer)
On Error GoTo ErrHandler
    Dim i As Long
    Dim bolAdvance As Boolean
    Dim bolInsertable As Boolean

    bolInsertable = frm.AllowAdditions
    If bolInsertable Then
        Select Case True
            Case TypeOf frm.Recordset Is DAO.Recordset
                bolInsertable = frm.Recordset.Updatable
            Case TypeOf frm.Recordset Is ADODB.Recordset
                bolInsertable = Not (frm.Recordset.LockType = adLockReadOnly)
            Case Else
                bolInsertable = False
        End Select
    End If

    Select Case KeyCode
        Case vbKeyUp
            With frm.Recordset
                If frm.NewRecord Then
                    If Not (.BOF And .EOF) Then
                        .MoveLast
                    End If
                Else
                    If Not (.BOF And .EOF) Then
                        .MovePrevious
                        If .BOF And Not .EOF Then
                            .MoveFirst
                        End If
                    End If
                End If
            End With
            KeyCode = &H0
        Case vbKeyDown
            With frm.Recordset
                If Not frm.NewRecord Then
                    If Not (.BOF And .EOF) Then
                        .MoveNext
                        If .EOF And Not .BOF Then
                            If bolInsertable Then
                                frm.SelTop = .RecordCount + 1
                            End If
                        End If
                    Else
                        If bolInsertable Then
                            frm.SelTop = .RecordCount + 1
                        End If
                    End If
                End If
            End With
            KeyCode = &H0
        Case vbKeyLeft
            Set ctl = frm.ActiveControl
            On Error GoTo NoPropertyErrHandler
            bolAdvance = (ctl.SelStart = 0)
            On Error GoTo ErrHandler
            If bolAdvance Then
                Do
                    If ctl.TabIndex = 0 Then
                        With frm.Recordset
                            If frm.NewRecord Then
                                .MoveLast
                            Else
                                .MovePrevious
                            End If
                            If .BOF And Not .EOF Then
                                .MoveFirst
                            End If
                        End With
                        Set ctl = col(CStr(lngMaxTabs))
                    Else
                        Set ctl = col(CStr(ctl.TabIndex - 1))
                    End If
                Loop Until ((ctl.TabStop = True) And (ctl.Enabled = True) And (ctl.Visible = True))
                ctl.SetFocus
                KeyCode = &H0
            End If
        Case vbKeyRight
            Set ctl = frm.ActiveControl
            On Error GoTo NoPropertyErrHandler
            bolAdvance = (ctl.SelStart >= Len(ctl.Value))
            On Error GoTo ErrHandler
            If bolAdvance Then
                Do
                    If ctl.TabIndex = lngMaxTabs Then
                        With frm.Recordset
                            If Not frm.NewRecord Then
                                .MoveNext
                            End If
                            If .EOF And Not .BOF Then
                                If bolInsertable Then
                                    frm.SelTop = .RecordCount + 1
                                End If
                            End If
                        End With
                        Set ctl = col("0")
                    Else
                        Set ctl = col(CStr(ctl.TabIndex + 1))
                    End If
                Loop Until ((ctl.TabStop = True) And (ctl.Enabled = True) And (ctl.Visible = True))
                ctl.SetFocus
                KeyCode = &H0
            End If
    End Select

ExitProc:
    On Error Resume Next
    Exit Sub
NoPropertyErrHandler:
    Select Case Err.Number
        Case 94
            Resume ExitProc
        Case 438
            bolAdvance = True
            Resume Next
    End Select
ErrHandler:
    Select Case Err.Number
        Case 3021, 3426
            Resume Next
        Case Else
            VBA.MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Unexpected error"
    End Select
    Resume ExitProc
    Resume
End Sub