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