This is part two of two-parts on maximizing code reuse. Part one focused on assigning more than one event handlers to same object’s event or sharing an event handler for several objects. We now turn to looking at how we can write design-time automation to help us save time in coding.

If you enjoy lots of typing, stop reading now

Sometime we get an inherited Access database and we find that there are lot of work we need to perform. For starter, let’s pretend we want to add KeyNavigator from the part one to 100 of continuous forms scattered around in the Access database. Even though we showed you how we can reduce the setup required in each form to only 4 lines of code, that’s still 400 lines of code to be added to each form. That’s lot of typing still.

But why type when you can get VBA to do it for you?

Note that before you can run the code, you must have a reference to “Microsoft Visual Basic for Applications Extensibility 5.3” library. You only need that reference for the duration and should remove it afterward.

Using Module object, we can write a procedure in a scratch module to perform the work for us. We typically have one scratch module added to the project — maybe named “zzScratchPad” so that it sorts to the bottom of the list. We also prefer to make all procedures within the module private, because the intention is not use them at runtime. The only way to run them is to run them from the VBA editor, which is appropriate for our purposes.

Since we want to add the needed 4 lines of code to continuous forms only, we need to set up a loop to transverse the forms in the application. To reduce the errors associated with multiple forms being open (e.g. you can’t open a form in design view when it’s already open as a subform in another form), we will close all objects and work with only one object at a time. Furthermore, because we may not have module for a form, we need to be sure we make one.
Private Sub StartAddKNInitCode()
Dim ao As Access.AccessObject

On Error GoTo ErrHandler

DoCmd.RunCommand acCmdCloseAll
If Forms.Count Then
MsgBox "Cannot process; there are still open forms. Please close them."
Exit Sub
End If

For Each ao In CurrentProject.AllForms
DoCmd.OpenForm ao.Name, acDesign, , , , acHidden
With Forms(ao.Name)
If .DefaultView = 1 Then 'It's a continuous form
If .HasModule = False Then
.HasModule = True 'Add a module
DoCmd.Save acForm, ao.Name
End If
AddKNInitCode .Module
End If
End With
DoCmd.Close acForm, ao.Name
Next

ExitProc:
On Error Resume Next
Exit Sub
ErrHandler:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
End Select
Resume ExitProc
Resume
End Sub
Now we have the right form and its module acquired, there are few more considerations. What if a form has a Open event handler already created? We don’t want to duplicate and thus write erroneous code that won’t compile. So we need to examine the module, determine if there are event handler and edit it or add new one as appropriate.
Private Sub AddKNInitCode(FormModule As Access.Module)
Dim l As Long 'Line Pointer
Dim i As Long 'Iterator
Dim b As Long 'Start point
Dim c As Long 'End point
Dim s As String 'Current Line Text
Dim bolHasIt As Boolean

Const Evented As String = "[Event Procedure]"
Const VariableLine As String = "Private kn As KeyNavigator"
Const InitLines As String = "Set kn = New KeyNavigator" & vbNewLine & "kn.Init Me"
Const DestLine As String = "Set kn = Nothing"

On Error GoTo ErrHandler
bolHasIt = False
b = 1
c = FormModule.CountOfDeclarationLines
For i = b To c
s = FormModule.Lines(i, 1)
Select Case s
Case VariableLine
bolHasIt = True
Exit For
End Select
Next
If Not bolHasIt Then
FormModule.InsertLines c + 1, VariableLine
End If

bolHasIt = False
l = 0

On Error Resume Next
l = FormModule.ProcBodyLine("Form_Load", vbext_pk_Proc)
On Error GoTo ErrHandler
If l Then
s = FormModule.Lines(l, FormModule.ProcCountLines("Form_Load", vbext_pk_Proc))
If InStr(s, InitLines) = 0 Then
If InStr(s, "On Error GoTo") = 0 Then
FormModule.InsertLines l + 1, InitLines
Else
b = l
c = l + FormModule.ProcCountLines("Form_Load", vbext_pk_Proc)
For i = b To c
s = FormModule.Lines(i, 1)
If InStr(s, "On Error GoTo") Then
FormModule.InsertLines i + 1, InitLines
Exit For
End If
Next
End If
End If
Else
FormModule.Parent.OnOpen = Evented
FormModule.InsertText _
"Private Sub Form_Load()" & vbNewLine & _
InitLines & vbNewLine & _
"End Sub" & vbNewLine
End If
bolHasIt = False
l = 0

On Error Resume Next
l = FormModule.ProcBodyLine("Form_Close", vbext_pk_Proc)
On Error GoTo ErrHandler
If l Then
s = FormModule.Lines(l, FormModule.ProcCountLines("Form_Close", vbext_pk_Proc))
If InStr(s, DestLine) = 0 Then
If InStr(s, "On Error GoTo") = 0 Then
FormModule.InsertLines l + 1, DestLine
Else
b = l
c = FormModule.ProcCountLines("Form_Close", vbext_pk_Proc)
For i = b To c
s = FormModule.Lines(i, 1)
If InStr(s, "On Error GoTo") Then
FormModule.InsertLines i + 1, DestLine
bolHasIt = True
Exit For
End If
Next
If bolHasIt = False Then
FormModule.InsertLines l + 1, DestLine
End If
End If
End If
Else
FormModule.Parent.OnClose = Evented
FormModule.InsertText _
"Private Sub Form_Close()" & vbNewLine & _
DestLine & vbNewLine & _
"End Sub"
End If

DoCmd.Save acForm, FormModule.Parent.Name

ExitProc:
On Error Resume Next
Exit Sub
ErrHandler:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ") on Line: " & Erl
End Select
Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
Resume
End Sub
Now we have a means of automatically adding the needed 4 lines to all forms. Because the code also checks for existence of those 4 lines, we can run it again and again so if we added 10 new continuous forms but didn’t add the needed 4 lines, it’s one-click run. This is also useful when you might not have 100 forms in one Access file but maybe do have that many in several different Access files.

Obviously, you could adapt the code to tailor your needs. Building a set of routines to use for taking over an old database and populate it with your libraries and fitting various objects with the needed calling code can be now made simpler. Happy automating!