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.

ADVERTISING
SQL Server Hosting

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 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 “…” 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 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:
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