Using Dynamic External Event Procedures to navigate in forms and more...

Use Dynamic External Event Procedures to navigate in forms and more…

Shamil Salakhetdinov
e-mail: shamil@marta.darts.spb.ru
All rights reserved. Terms of use.

Written in October, 1998

Originally published in Smart Access January 1999
Pinnacle Publishing, Inc.
P.O. Box 888
Kent, WA 98035-0888
Tel 206-251-1900, Fax 206-251-5057
http://www.pinpub.com

Every professional MS Access developer has to have reusable code libraries to build and deploy bullet-proof applications RAD-way. One of the main sets of the functions of such a library is a set of functions to navigate and to process database data in forms. There are several well-known standard ways to program and use such functions. This article shows brand-new approach based of custom classes and WithEvents-enabled MS Access 97 objects.

Standard data navigation form
Reworked standard data navigation form
Two main methods to use code libraries for event processing
Limitations of standard methods to use event processing library functions
WithEvents-enabled objects – the key of the new solution
Standard code for navigation buttons
DEEP-object concept
Advanced generalisation of CBF
DEEP-object's code template for command button
Is it a bug or a feature?
Generic DEEP-object for navigation form
Simplified form's DEEP-object and CBF
Custom Class Factory and DEEP-object for textbox
"CBF-less" form and self-referenced DEEP-object
Custom Class Factory code and its usage
Business Object Interface template
Inverted DEEP-objects
Framework for MS Access 97 component programming
Conclusion

Standard data navigation form

Working with standard MS Access 97 form generated by Wizard you can use built-in navigation buttons, record selection form area, short-cut keys and predefined command bars/pop-up menu entries/buttons to process database data and navigate in it (Figure 1).

Figure 1. Standard form generated by MS Access 97 Form Wizard

Such form is a ready-to-run solution but most of the developers use such solutions only to prototype application, to create its working mock-up.

Reworked standard data navigation form

Having been accepted by customer prototype forms’ layouts are usually styled by developers one or another way (see Figure 2 - In this article I use very simple form layout style) -

Figure 2. Reworked layout of standard data navigation form

and forms’ application code (known as Code Behind Forms (CBF)) is reworked using code building Wizards. For example, here is the code generated by code building wizard for <First> command button:


Private Sub cmdFirst_Click()
    On Error GoTo Err_cmdFirst_Click
    DoCmd.GoToRecord , , acFirst
Exit_cmdFirst_Click:
    Exit Sub
Err_cmdFirst_Click:
    MsgBox Err.Description
    Resume Exit_cmdFirst_Click
End Sub

But shown above is a beginner's approach – advanced developers do the next step – they develop their own add-in Wizards which not only style/modify forms'/controls' layouts and add standard controls – command buttons, context display textboxes etc. but also insert calls to developer's reusable code libraries.

Two main methods to use code libraries for event processing

The main two methods to use code library's functions to process MS Access forms' and controls events are the following:

Set form's or control's corresponding event property equal to the public function name, used to process this event (see Figure3):

Figure 3. OnClick property set to the function used to process Click event

Then the function is called when event is fired. Here is this function – it is not a function from CBF – it is from code library but named the same style as are usually named CBF event procedures:
```
Public Function cmdFirst_Click()
    On Error GoTo cmdFirst_Click_Err
    DoCmd.RunCommand acCmdRecordsGoToFirst
cmdFirst_Click_Exit:
    Exit Function
cmdFirst_Click_Err:
    MsgBox "cmdFirst: Err = " & Err & " – " & Err.Description
    Resume cmdFirst_Click_Exit
End Function
```
You can also write a function used to process several buttons' Click events – in this case you usually pass to such a function event context and other parameters including form reference (see Figure 4).

Figure 4. Public function name used to process several Click events

Such function uses Select Case operator to recognise current context passed to it through its arguments:
```
Public Function cmd_Click(ByRef rfrm As Form, _
                          ByVal vstrCtlName As String)
    On Error GoTo cmd_Click_Err
    
    Select Case vstrCtlName
    Case "cmdFirst":
       DoCmd.RunCommand acCmdRecordsGoToFirst
    Case "cmdPrev":
    '...
    Case Else
    End Select
cmd_Click_Exit:
    Exit Function
cmd_Click_Err:
    MsgBox "cmdClick: Err = " & Err & " - " & Err.Description
    Resume cmd_Click_Exit
End Function
```
Create CBF event procedure which have nothing but the call to reusable code library function:
```
Private Sub cmdFirst_Click()
     cmd_Click Form, "cmdFirst"
End Sub
```
In this case the event property is set to "[Event Procedure]" (without quotes) and linked to the corresponding event procedure (see Figure 5):

Figure 5. OnClick property set to the private event procedure, which calls generic function.

Limitations of standard methods to use event processing library functions

Both methods described in short above are well known and widely used by MS Access developers of any level. I'm not an exception and used both the first and second one in my applications starting from MS Access 1.1 (in which only the first method can be used) and then in MS Access 2.0 and 97.

But none of these methods is satisfactory when you develop large MS Access applications:

Yes, they use reusable well tested functions but they still need to place these functions calls in each and every control's event property or corresponding CBF event procedure on all of the forms which use these functions;
In the case you need to change library functions' definitions or extend forms'/controls' functionality to make them react on additional events you have to go and edit all the forms and controls which use these/new functions. There are third-party tools on the market (SPEED Ferret by Black Moshannon System ( http://www.speedferret.com/speedferret.html), Search and Replace by Rick Fisher Consulting (http://www.dnai.com/~rfisher/) which help you to perform batch replacement. I don't known about third-party tool being able to add new properties/code and replace code fragments in an MS Access application – but this isn't too hard to develop such tools in MS Access 97. Anyway you need to go through all the forms and code to manually or automatically perform replacements/additions;
If you use CBF which was introduced in MS Access 2.0 and promoted by Microsoft you get relatively slow "heavy-weight" forms and "swelled" MDB front-ends;
if you don't use CBF and call your code library functions through corresponding event properties then you get light-weight forms but you cannot get all the parameters/context you can supply your functions from CBF (OnKeyDown, OnMouseMove…) and you cannot return values to MS Access internal events processing code to cancel events (OnOpen, OnUnload…), suppress standard event processing/messages (OnError, OnNotInList) etc. And such forms aren't true light-weight ones – they will be slow on first function call when MS Access preloads all the code from functions calls chain hierarchically dependent on the called entry function.

I hope you've got the general picture of the problems, which are in my opinion a real nightmare even for an advanced MS Access application developer.

WithEvents-enabled objects – the key of the new solution

Recently in Access-L discussion list I was informed by Marek Kepinski (e-mail: MKepinski@impaq.com.pl) that WithEvents-enabled MS Access 97 objects allow to bind form's/controls' events to class module's procedures to create custom event listeners. But he wrote that such a feature to be used still needs to keep in CBF (at least) empty event procedures. Such a solution being a big step forward from the two methods referred to above does not solve the problem with properties' values/code replacement/addition in the case of changes.

All the problems above and their in my opinion "semi-satisfactory" solutions forced me to try to find more technological and advanced solution. And I think I found it. And even more - this solution dramatically changed the style of event processing programming I used to use in my applications…

To show how it works I start from the CBF generated by MS Access wizards and step by step convert it into a set of custom classes used to process form's and controls' events.

Standard code for navigation buttons

Below is the part of the CBF code generated by MS Access 97's wizard to process database data and navigate underlying recordset in form. This is the code for the First, Previous, Next, Last, New, Delete, Save, Undo and Close command buttons. I dropped all the error handling code lines in it and don't usually use error handling code in other sample code to not overload article with unneeded to understand main article's subject code details – the complete code you find in sample MDB - this is CBF for frmForm1:

Private Sub cmdFirst_Click()
    DoCmd.GoToRecord , , acFirst
End Sub

Private Sub cmdPrev_Click()
    DoCmd.GoToRecord , , acPrevious
End Sub

Private Sub cmdNext_Click()
     DoCmd.GoToRecord , , acNext
End Sub

Private Sub cmdLast_Click()
    DoCmd.GoToRecord , , acLast
End Sub

Private Sub cmdNew_Click()
    DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub cmdDel_Click()
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End Sub

Private Sub cmdSave_Click()
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End Sub

Private Sub cmdUndo_Click()
   DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
End Sub

BTW, it's a little bit strange that wizard "directly from Microsoft" uses DoCmd.DoMenuItem which is Access 95 style not the DoCmd.RunCommand which is new and recommended in MS Access 97's online help.

DEEP-object concept

Now I'm going to show you how to create what I call Dynamic External Event Processing (DEEP) objects – these are the objects instantiated on run-time from custom classes used to catch and process the events of the form and its controls. The first preparatory actions to get DEEP-objects are:

save frmForm1 as frmForm2;
create class module clsFrmForm2 and move all the CBF from frmForm2 into this class module (I name class module clsFrmForm2 to follow the naming conventions I use my apps – in fact class module can be named frmForm2 if you like it because of the fact that form's class module has the name Form_frmForm2);
save class module;
compile its code.
When you try to open frmForm2 and click on its command buttons nothing happens. What is the problem? Wait a moment now I'm ready to perform the main conceptual steps:
save form frmForm2 as frmForm3;
save class module clsFrmForm2 as clsFrmForm3;
open class module clsFrmForm3 and add the following code to its declaration section:
```
Private WithEvents Form As Form
Private WithEvents cmdFirst As CommandButton
Private WithEvents cmdPrev As CommandButton
Private WithEvents cmdNext As CommandButton
Private WithEvents cmdLast As CommandButton
Private WithEvents cmdNew As CommandButton
Private WithEvents cmdDel As CommandButton
Private WithEvents cmdSave As CommandButton
Private WithEvents cmdUndo As CommandButton
Private WithEvents cmdClose As CommandButton
```
These code lines declare private form and command buttons object variables as WithEvents ones. What this means? This means at least four things:
1. While in class module design mode you see all these objects in the module's "Objects" combo-box (top-left corner of class module window) – the same way you see the form and its controls while in its class module design view;
2. While in class module design view you can select WithEvents-object from "Objects" combo-box, then select event from module's "Object Events" combo-box (top-right corner of class module window) to create event procedure declaration template – the similar way you can do that while in form's class module design view;
3. You can set the value of WithEvents form object variable in class module to the reference of an open form and if you have created also one or more form's event procedures in this class module then they seems to be called in the case if corresponding events are fired;
4. You can set the value of the WithEvents control object variable to the reference of the corresponding control of an open form. If you create also one or more control's event procedures in this class module then they seems to be called when corresponding events are fired.
save class module but don't close it;
open form frmForm3 in design view and open its class module;
insert the following code into it:
```
Private mobjDeep As New clsFrmForm3

Private Sub Form_Open(Cancel As Integer)
    mobjDeep.DeepsAttach Form
End Sub
```
First line - Private mobjDeep As New clsFrmForm3 - is just a declaration of custom object I'm going to use to process form and its controls' events. And the code line in the form's open event procedure - mobjDeep.DeepsAttach Form - is the call of the custom object's method which sets the values of its WithEvents form object variable to the reference of the form passed to it as parameter and also sets the values of its WithEvents command buttons object variables to the references of the corresponding controls. Please note that to refer to the form's object variable I use Form keyword not the usual Me one – this is very important - as you'll see later in this article this simple nonstandard technique let you to move CBF code into custom class without any modifications.
save frmForm3's class module and close it;

insert the following code into clsFrmForm3:

Public Sub DeepsAttach(ByRef rfrm As Form)
    Set Form = rfrm
    Set cmdFirst = Form![cmdFirst]
    Set cmdPrev = Form![cmdPrev]
    Set cmdNext = Form![cmdNext]
    Set cmdLast = Form![cmdLast]
    Set cmdNew = Form![cmdNew]
    Set cmdDel = Form![cmdDel]
    Set cmdSave = Form![cmdSave]
    Set cmdUndo = Form![cmdUndo]
    Set cmdClose = Form![cmdClose]
End Sub

This code is used to set object references to the form and its controls.

save clsFrmForm3 class module and close it;
Try to open frmForm3 and click on its command buttons – still nothing happens? What's a pity! Am I going the right way? Yes, I'm – in few seconds you'll see the key which opens boundless expanse:
save form frmForm3 as frmForm4;
save class module clsFrmForm3 as clsFrmForm4;
open form frmForm4 in design view and open its class module;

insert the following code into it:

Private mobjDeep As New clsFrmForm4

Private Sub Form_Open(Cancel As Integer)
    mobjDeep.DeepsAttach Form
End Sub

save frmForm4's class module and close it and the form;

open class module clsFrmForm4 and replace DeepsAttach sub with this new one:

Public Sub DeepsAttach(ByRef rfrm As Form)
    Set Form = rfrm
    Set cmdFirst = Form![cmdFirst]
    cmdFirst.OnClick = "[Event Procedure]"
    Set cmdPrev = Form![cmdPrev]
    cmdPrev.OnClick = "[Event Procedure]"
    Set cmdNext = Form![cmdNext]
    cmdNext.OnClick = "[Event Procedure]"
    Set cmdLast = Form![cmdLast]
    cmdLast.OnClick = "[Event Procedure]"
    Set cmdNew = Form![cmdNew]
    cmdNew.OnClick = "[Event Procedure]"
    Set cmdDel = Form![cmdDel]
    cmdDel.OnClick = "[Event Procedure]"
    Set cmdSave = Form![cmdSave]
    cmdSave.OnClick = "[Event Procedure]"
    Set cmdUndo = Form![cmdUndo]
    cmdUndo.OnClick = "[Event Procedure]"
    Set cmdClose = Form![cmdUndo]
    cmdClose.OnClick = "[Event Procedure]"
End Sub

save clsFrmForm4 class module and close it;

Try to open frmForm4 and click on its command buttons – they work now! Why? – I have just added <WithEventCommandButtonRef>.OnClick = "[Event Procedure]" code line for each command button and I have got such a nice and useful effect?! Honestly, I don't know exact answer – I guess this is a kind of "Easter-eggs" undocumented feature developed but not exposed by MS Access development team. Probably it hadn't been tested enough when MS Access 97 was released. But it works and seems to be stable enough to be used in real life applications development.

To make myself sure that this "[Event procedure]" assignment is the key I do the following:

open frmForm4 in normal view;
open debug window;

type in its "Immediate" sub-window:


?Forms![frmForm4]![cmdFirst].OnClick=""

I get False;
click <Next> command button of frmForm4;
type in "Immediate" sub-window:
```
Forms![frmForm4]![cmdFirst].OnClick=""
```
If you still don't realize what I have just did - I explain – I have disabled (all the chain) of event procedures which react on cmdFirst's Click event. As far as I can remember I used such a trick in MS Access 2.0 but only in MS Access 97 having been combined with WithEvents-enabled objects this trick opens a brand-new style of MS Access/Office application programming!

type in "Immediate" sub-window::


Forms![frmForm4]![cmdFirst].OnClick="[Event Procedure]"

cmdFirst command button again reacts on Click event.

Advanced generalization of CBF

But let me continue – my main goal isn't yet reached – the current solution isn't generic enough – it still uses CBF and I set references to command buttons using their names:

save frmForm4 form as frmForm5;
save clsFrmForm4 as clsFrmForm5;
open form frmForm5 in design view and open its class module;

insert the following code into it:

Private mobjDeep As New clsFrmForm5

Private Sub Form_Open(Cancel As Integer)
    mobjDeep.DeepsAttach Form
End Sub

save frmForm5's class module and close it and the form;

open class module clsFrmForm5 and replace DeepsAttach sub with the following two procedures:

Public Function DeepsAttach(ByRef rfrm As Form)
    Set Form = rfrm
    Set cmdFirst = CmdDeepAttach(Form![cmdFirst])
    Set cmdPrev = CmdDeepAttach(Form![cmdPrev])
    Set cmdNext = CmdDeepAttach(Form![cmdNext])
    Set cmdLast = CmdDeepAttach(Form![cmdLast])
    Set cmdNew = CmdDeepAttach(Form![cmdNew])
    Set cmdDel = CmdDeepAttach(Form![cmdDel])
    Set cmdSave = CmdDeepAttach(Form![cmdSave])
    Set cmdUndo = CmdDeepAttach(Form![cmdUndo])
    Set cmdClose = CmdDeepAttach(Form![cmdClose])
End Function

Private Function CmdDeepAttach(ByRef rcmd As CommandButton) _
                                                    As CommandButton
    rcmd.OnClick = "[Event procedure]"
    Set CmdDeepAttach = rcmd
End Function

Here I just moved command button's WithEvents object reference and Click event procedure assignments to the CmdDeepAttach(…) function. At first glance this is a very simple step but it is one of the main conceptual steps every programmer do to become real programmer (when you realise that you can create parameterised subroutines to perform conceptually equal actions with different in nature variables plain or object does not matter). To emphasise the importance of this step I showed it here as a separate one.

save clsFrmForm5 class module and close it;
open frmForm5 and play with its buttons- they work well as expected.

Until now I kept all the code in two class modules – form's CBF used to call its custom class module (second module) methods to pass and set references to form and its controls and to activate dynamic external event procedures. This is a good working solution but what if I have not simple navigation buttons but complex custom controls on my form and I'm working in a team of programmers in which each of them develops the code for each of these controls? To let them work in parallel I need to keep each of controls' code in separate class module. I assume also that all the complex controls have a set of common properties, procedures (functions, subs) and event procedures. So I select well-known OOP- style of programming now: create a base generic class and inherit base class features in child classes. I create clsCommandButton class module and type in it the following code:

Private WithEvents mcmd As CommandButton

Public Sub Init(ByRef rcmd As CommandButton)
    Set mcmd = rcmd
    With mcmd
        .OnEnter = "[Event Procedure]"
        .OnExit = "[Event Procedure]"
        .Picture = ""
        .Caption = .Tag
    End With
End Sub

Private Sub mcmd_Enter()
    mcmd.FontBold = True
End Sub

Private Sub mcmd_Exit(Cancel As Integer)
    mcmd.FontBold = False
End Sub

You are now familiar with the concept on dynamic event procedures assignments and you see that the code above sets event procedures for Enter and Exit events, hides button's image (if any) and sets button's caption to the command button's tag property. Enter event procedure changes command buttons font to Bold and Exit event procedure resets it to normal.

DEEP-object's code template for command button

For each command button I use the following code template to create its specific/dedicated class module:

Private mcmd As New clsCommandButton

Private WithEvents <ButtonName> As CommandButton

Public Sub Init(ByRef rcmd As CommandButton)
    mcmd.Init rcmd
    
    Set <ButtonName>  = rcmd
    <ButtonName>.OnClick = "[Event Procedure]"
End Sub

Private Sub <ButtonName>_Click()
    On Error GoTo Err_<ButtonName>_Click
    <Button specific code here>
Exit_<ButtonName>_Click:
    Exit Sub
Err_<ButtonName>_Click:
    MsgBox Err.Description
    Resume Exit_<ButtonName>_Click
End Sub

Here is the code built from this template for cmdFirst command button:

Private mcmd As New clsCommandButton

Private WithEvents cmdFirst As CommandButton

Public Sub Init(ByRef rcmd As CommandButton)
    mcmd.Init rcmd
    
    Set cmdFirst = rcmd
    cmdFirst.OnClick = "[Event Procedure]"
End Sub

Private Sub cmdFirst_Click()
    On Error GoTo Err_cmdFirst_Click
    DoCmd.GoToRecord , , acFirst
Exit_cmdFirst_Click:
    Exit Sub
Err_cmdFirst_Click:
    MsgBox Err.Description
    Resume Exit_cmdFirst_Click
End Sub

The only new code line you see here is the following line in Init sub:


mcmd.Init rcmd

This line is used to pass reference of the command button object to the generic/base clsCommandButton class showed above to set cmdFirst's command buttons generic properties/events procedures.

I copy frmForm5 to frmForm6 and supply it with the following CBF code (nothing new here):

Private mobjDeep As New clsFrmForm6

Private Sub Form_Open(Cancel As Integer)
    mobjDeep.DeepsAttach Form
End Sub

Now I create clsFrmForm6 class module:

Private WithEvents Form As Form
Private mcolControls As New Collection

Private Sub Class_Terminate()
          Debug.Print "Terminate clsFrmForm6"
End Sub

Public Function DeepsAttach(ByRef rfrm As Form)
    Set Form = rfrm
    mcolControls.Add cmdInit(rfrm![cmdFirst], New clsCmdFirst)
    mcolControls.Add cmdInit(rfrm![cmdPrev], New clsCmdPrev)
    mcolControls.Add cmdInit(rfrm![cmdNext], New clsCmdNext)
    mcolControls.Add cmdInit(rfrm![cmdLast], New clsCmdLast)
    mcolControls.Add cmdInit(rfrm![cmdNew], New clsCmdNew)
    mcolControls.Add cmdInit(rfrm![cmdDel], New clsCmdDel)
    mcolControls.Add cmdInit(rfrm![cmdSave], New clsCmdSave)
    mcolControls.Add cmdInit(rfrm![cmdDuplicate], New clsCmdDuplicate)
    mcolControls.Add cmdInit(rfrm![cmdUndo], New clsCmdUndo)
    mcolControls.Add cmdInit(rfrm![cmdClose], New clsCmdClose)
End Function

Private Function cmdInit(ByRef rcmd As CommandButton, _
                                        ByRef robjCmd As Object) As Object
   robjCmd.Init rcmd
    Set cmdInit = robjCmd
End Function

As you see I don't have here WithEvents command buttons object variables – they are now kept in dedicated class modules. To initialise/instantiate these class modules' objects I use cmdInit function. First line of this function calls Init method of dedicated class, which in turn calls Init method of generic command button class. The second line sets the return value of the function to the reference of the command button's object variable passed in the first argument of the function call. On cmdInit function exit its return value (command button object reference) is added in the calling DeepsAttach sub to the mcolControls collection. I have to have/keep (at least one) object reference in public variable/collection or in open form instance CBF variable/collection to not let MS Access to destroy the corresponding object on sub/function exit. Here I create command button specific object variable by New cls<ButtonName> expressions passed in cmdInit arguments and then add this object variable reference to clsFrmForm6 mcolControls collection. clsFrmForm6's object is instantiated in frmForm6 CBF and its reference is stored in mobjDeep object variable. Form frmForm6 keeps this object reference "alive" until closing when MS Access should automatically destroy clsFrmForm6 object instance, all the buttons' custom class instances etc. and call their Class_Terminate subs. But it seems that it (MS Access) does not destroy or only partially destroys mobjDeep object variable – clsFrmForm6 Class_Terminate(…) sub isn't called as well as Class_Terminate subs of buttons' custom class instances – nothing is printed by Debug.Print "Terminate clsFrmForm6" in immediate window when you close frmForm6.

Is it a bug or a feature?

This seems to be a bug or a feature and as a simple workaround I can replace clsFrmForm6 class module's code with the following code:

Private WithEvents Form As Form
Private mcolControls As New Collection

Private Sub Class_Terminate()
     Debug.Print "Terminate clsFrmForm6"
End Sub

Public Sub DeepsAttach(ByRef rfrm As Form)
    Set Form = rfrm
    Form.OnClose = "[Event procedure]"
    
    mcolControls.Add cmdInit(rfrm![cmdFirst], New clsCmdFirst)
    mcolControls.Add cmdInit(rfrm![cmdPrev], New clsCmdPrev)
    mcolControls.Add cmdInit(rfrm![cmdNext], New clsCmdNext)
    mcolControls.Add cmdInit(rfrm![cmdLast], New clsCmdLast)
    mcolControls.Add cmdInit(rfrm![cmdNew], New clsCmdNew)
    mcolControls.Add cmdInit(rfrm![cmdDel], New clsCmdDel)
    mcolControls.Add cmdInit(rfrm![cmdSave], New clsCmdSave)
    mcolControls.Add cmdInit(rfrm![cmdDuplicate], New clsCmdDuplicate)
    mcolControls.Add cmdInit(rfrm![cmdUndo], New clsCmdUndo)
    mcolControls.Add cmdInit(rfrm![cmdClose], New clsCmdClose)
End Sub

Private Function cmdInit(ByRef rcmd As CommandButton, _
                                        ByRef robjCmd As Object) As Object
    robjCmd.Init rcmd
    Set cmdInit = robjCmd
End Function

Private Sub Form_Close()
    Set mcolControls = Nothing
    Set Form = Nothing
End Sub

Here I explicitly destroy controls' and Form's references and this does destroy clsFrmForm6 object instance in this special case. But as I found the problem is more complex and the solution presented above does not work properly in all the cases. To show that I add the following event procedure to the CBF of frmForm6:

Private Sub Form_Close()
    Set mobjDeep = Nothing
End Sub

And I save frmForm6 as frmForm7. Now I set breakpoint on the line Set mobjDeep = Nothing, open frmForm7 and close it – code execution is stopped on breakpoint. I step into code (F8) and I see that Class_Terminate procedure is called. But when I reach End Sub line of the Form_Close sub and try to continue code execution I see that this results in the call of Dr.Watson…

Resume: when I explicitly directly or indirectly (setting to nothing container collection/object) destroy WithEvents form references while form unloading/closing is in progress something wrong happens in MS Access internals… I guess that this breaks MS Access 97's internal chain of the calls of pending event procedures…

OK, the only choice I have is to follow this "feature" and never set to Nothing directly or indirectly WithEvents forms' and controls' references. Hopefully MS Access 97 destroys them and releases by itself the memory they occupy.

But as a consequence of this "compromise" I have to not forget to write special termination subs to be used to destroy custom classes' module level (object) variables (all except WithEvents ones). Such subs calls should be chained from the parent custom class to all the child custom classes to perform custom clean-up.

Public Sub Terminate()
    On Error Resume Next
    Dim obj As Object
    For Each obj In mcolControls
        obj.Terminate
        Err.Clear
    Next
End Sub

I use On Error Resume Next in Terminate sub to suppress errors when child object being terminate does not have its own Terminate sub. The call to the parent termination sub should be placed in the Form_Close event procedure of Form_Close event of CBF (see frmForm8):

Private Sub Form_Close()
    mobjDeep.Terminate
End Sub

or in the Form_Close event procedure of Deep-object's class module (see clsFrmForm9):

Private Sub Form_Close()
    Me.Terminate
End Sub

There is also a tricky workaround when I use a special Terminator Form with TimerInterval set to very small value, say 1 millisecond. This form is used to implement the following trick - create an instance of Terminator form in form's Close event and pass DEEP-Object reference to this Terminator form. MS Access processes Timer event of Terminator form after it finishes processing unload/close events. But because of the fact that I store DEEP-Object reference in Terminator's form object variable, when Timer event fires I can get this reference and call DEEP-object termination sub. This solution works and in this case standard Class_Terminate sub of custom class module is called by MS Access. But it is too tricky to be used in real-life apps and it still needs to write and use dedicated termination sub of custom classes. So I don't describe it here in details – you can find it in the sample database – in the forms frmFormA and frmTerminator, in the standard module basTerminator and in the class module clsFrmFormA.

Generic DEEP-object for navigation form

But let me continue to generalise form navigation events processing code using DEEP-objects. The next step is to move this code into generic class module clsFormNavigation. Here is the part of the code of this module:

Private WithEvents Form As Form
Private mcolControls As New Collection

Private mblnTerminateCalled As Boolean

Public Sub Init(ByRef rfrm As Form)
    DeepsAttach rfrm
End Sub

Public Sub Terminate()
    Dim obj As Object
    If mblnTerminateCalled = False Then
       For Each obj In mcolControls
         On Error Resume Next
         obj.Terminate
         Err.Clear
       Next
       mblnTerminateCalled = True
    End If
End Sub
 
Private Sub DeepsAttach(ByRef rfrm As Form)
	' the rest of the code is the same as in clsFrmForm6 above

Init sub is used to initialize DEEP-objects – attach event procedures to the form's events and create command buttons controls' DEEP-objects. Terminate sub is used to perform custom clean-up. I added mblnTerminateCalled flag here to bypass the situation when Terminate sub is (erroneously) called more than one time.

Simplified form's DEEP-object and CBF

With all the generic code moved into clsFormNavigation the form's DEEP-object's class module becomes very simple (I save frmFormA as frmFormB and I create clsFrmFormB custom class):

Private mobjFormNavigation As New clsFormNavigation
Private WithEvents Form As Form

Public Function Init(ByRef rfrm As Form)
    Set Form = rfrm
    Form.OnClose = "[Event procedure]"
    mobjFormNavigation.Init rfrm
End Function

Public Function Terminate()
    mobjFormNavigation.Terminate
End Function

Private Sub Form_Close()
    Me.Terminate
End Sub

And the frmFormB form's CBF is very simple as well:

Private mobjDeep As New clsFrmFormB

Private Sub Form_Open(Cancel As Integer)
    mobjDeep.Init Form
End Sub

I use it to initialize form's DEEP-object when Open event fires.

Custom Class Factory and DEEP-object for textbox

But my goal is to remove all the code from CBF. To approach this goal I will create what I call DEEP- objects Custom Class Factory (DEEP-CCF). Before I do that I first explain what is the main task of such a class factory.

Above I have created custom generic class for form navigation. As you can see there could be a lot of such custom classes MS Access developers can create. Let's imagine that we would like to create another form navigation class, which includes all the functionality from clsFormNavigation plus it will change the color of the textbox having the focus. You are familiar with DEEP-object concept now and you can easily create desired textbox's custom class:

Private WithEvents mtxt As TextBox

Private mlngBackColor As Long

Public Sub Init(ByRef rtxt As TextBox)
    Set mtxt = rtxt
    mtxt.OnEnter = "[Event Procedure]"
    mtxt.OnExit = "[Event Procedure]"
End Sub

Private Sub mtxt_Enter()
    mlngBackColor = mtxt.BackColor
    mtxt.BackColor = 16776960
End Sub

Private Sub mtxt_Exit(Cancel As Integer)
    mtxt.BackColor = mlngBackColor
End Sub

This class sets reference to textbox object variable, attaches to it event procedures for Enter and Exit events. When Enter event fires (cursor is being positioned to textbox) textbox's BackColor property is set to light blue. When Exit event fires textbox's BackColor property is reset to its original value. I call this new form navigation class as clsFormNavigationExt. The only new code I add to this class is the code to create textboxes' DEEP-objects placed in DEEPsAttach sub:

Dim ctl As Control
Dim objTxt As clsTextBox
For Each ctl In Form.Controls
    If TypeOf ctl Is TextBox Then
       Set objTxt = New clsTextBox
       objTxt.Init ctl
       mcolControls.Add objTxt
    End If
Next

This new custom class is used in clsFrmFormC and frmFormC objects in sample database.

"CBF-less" form and self-referenced DEEP-object

Now I copy clsFrmFormC as clsFrmFormD, frmFormC as frmFormD, remove all the code from frmFormD's CBF (but leave its HasModule property set to true – don't miss this very important point – if HasModule = False DEEP-objects do not work in MS Access 97) and set frmFormD's OnOpen property to "=frmFormDDeepInit([Form])" (without quotes). FrmFormDDeepInit function stored in standard module is the following:

Public Function frmFormDDeepInit(ByRef rfrm As Form)
    Dim obj As New clsFrmFormD
    obj.Init rfrm
End Function

This function instantiates custom class clsFrmFormD for frmFromD. But when you open frmFormD, command buttons do not work. Why? Because of the fact that clsFrmFormD is automatically destroyed by MS Access when frmFormDDeepInit function finishes. To not let MS Access to destroy object instance I modify slightly clsFrmFormD code – I add the following line in its declaration section:


Private mobjSelfRef As Object

and the code line:


Set mobjSelfRef = Me

to its Init function. I save clsFrmFormD as clsFrmFormE, frmFormD as frmFormE and set frmFormE's OnOpen property to "=frmFormEDeepInit([Form])" (without quotes). The frmFormEDeepInit function has the following code:

Public Function frmFormEDeepInit(ByRef rfrm As Form)
    Dim obj As New clsFrmFormE
    obj.Init rfrm
End Function

Nothing changed except renaming if you compare it with frmFormDDeepInit but frmFormE works now! This is because MS Access 97 follows the COM rule – object isn't destroyed until somewhere in the app exist references to this object – and I set such reference to the form's custom object within Init method of this object.

I run now a small tricky test:

Public Sub SmallTrickyTest()
    Dim frm1 As New Form_frmFormD
    Dim frm2 As New Form_frmFormE
    Dim frm3 As New Form_frmFormE
    
    frm1.Visible = True
    frm2.Visible = True
    frm3.Visible = True
End Sub

When I run it I see that frm1 is opened and then is closed on sub exit but frm2 and frm3 stay opened after test sub completion and even operate properly! What keeps them open? – They are referenced from the instances of their custom classes, which are self-referenced. And frm1's custom class instance isn't self-referenced – thus it is destroyed on frmFormDDeepInit function exit and all its internal object references are destroyed as well – nothing references frm1 and it is destroyed. As you can see I get, by the way, a very useful "side-effect" – I can create now multiple form instances for DEEP-enabled forms by just two lines of code!

Custom Class Factory code and its usage

Now is the time to create Custom Class Factory for forms' DEEP-objects. Here it is in the class module clsFormDeepOICCF:

Public Function FormDeepCreate(ByVal vstrNavFormType As String) As Object
    Select Case vstrNavFormType
    Case "BussObj1": Set FormDeepCreate = New clsBussObjOI1
    Case "BussObj2": Set FormDeepCreate = New clsBussObjOI2
    ' .....
    Case Else
    End Select
End Function

It is used in the helper function from standard module basHelpers:

Public Function FormDeepInit(ByRef rfrm As Form)
    Dim objFormsRegistry As New clsFormDeepOICCF
    Dim obj As Object
    
    Set obj = objFormsRegistry.FormDeepCreate(rfrm.Tag)
    obj.Init rfrm
End Function

FormDeepInit function is called from form's Open event (form's OnOpen property is set to "=FormDeepInit([Form])" (without quotes) and the form's Tag property is set to the name of the Deep- object type – I have two types in the sample database "BussObj1" which is a "nickname" or mnemonic CLSID of clsBussObjOI1 custom class and "BussObj2" which is a mnemonic CLSID of clsBussObjOI2. They are used to reference and create DEEP-objects for frmBussObj1 and frmBussObj2 respectively). You can easily generalise such custom class factory object to extend it with the code used to create instances of different types of command buttons, text boxes, combo- and list boxes etc. depending on the calling context. And non-visual layer custom object instances too.

Business Object Interface template

In fact, as you can see, I've created here a template for class factory, which creates instances of object interfaces of DEEP-business objects – dynamically attachable to visual layer objects (forms, reports and their controls) on runtime depending or, defined by, the current context/application options settings. ClsBussObjOI1 and clsBussObjOI2 are containers, which initialise and keep all the custom objects used on different layers of business object's object interface. To be able to get the reference to these containers I have to store them somewhere in my application. This can be done many ways – I show, probably, the simplest – store object references in a collection and write a Property Get function which gets values from this collection. I modify helper functions presented above and add some other code:

Public gcolDeeps As New Collection

Public Function FormDeepOIInit(ByRef rfrm As Form)
    Dim objFormsRegistry As New clsFormDeepOICCF
    Dim obj As Object
    
    Set obj = objFormsRegistry.FormDeepCreate(rfrm.Tag)
    obj.Init rfrm
    gcolDeeps.Add obj, CStr(rfrm.Hwnd)
End Function

Public Function FormDeepOIKill(ByRef rfrm As Form)
    On Error Resume Next
    gcolDeeps.Remove CStr(rfrm.Hwnd)
End Function

Public Property Get FormDeep(ByRef rfrm As Form) As Object
    On Error Resume Next
    Set FormDeep = gcolDeeps(CStr(rfrm.Hwnd))
End Property

I also add new line to clsFormDeepOICCF:


Case "BussObj": Set FormDeepCreate = New clsBussObjOI

Now I copy custom class module clsBussObjOI2 to clsBussObjOI, frmBussObj2 to frmBussObj and modify frmBussObj OnOpen property to "=FormDeepOIInit([Form])" (without quotes). clsBussObj becomes very simple but generic and ready for extension business object container framework now:

Private mobjSelfRef As Object
Private mobjFormNavigation As New clsFormNavigationExt
Private WithEvents Form As Form

Public Function Init(ByRef rfrm As Access.Form)
    Set Form = rfrm
    mobjFormNavigation.Init rfrm
    Form.OnClose = "[Event procedure]"
    Set mobjSelfRef = Me
End Function

Public Function Terminate()
    mobjFormNavigation.Terminate
End Function

Private Sub Form_Close()
    Me.Terminate
    FormDeepOIKill Form
End Sub

I run now this code:

Public Sub SmallTrickyTest1()
    Dim frm1 As New Form_frmBussObj
    Dim frm2 As New Form_frmBussObj
    Dim frm3 As New Form_frmBussObj
    
    frm1.Visible = True
    frm2.Visible = True
    frm3.Visible = True
End Sub

And get three form instances of frmBussObj, which I can use, for example, to simulate how my app will work in multi-user environment (see Figure 6).

Figure 6. Three form instances of frmBussObj form

Nice and very useful "side-effect" of DEEP-object concept!

Inverted DEEP-objects

There exists another approach to create and handle DEEP-objects which I call "Inverted DEEP-objects". It does not require to have any settings to the event properties of the form at all! (But HasModule property should be set to Yes). This method is under investigations but you can find one of its applications in sample database – see frmBussObj_Inv and clsBussObj_Inv. Using this method you can open two form instances such a little bit tricky way:

Public pcolTest As New Collection

Public Sub SmallTrickyTest3()
    pcolTest.Add New clsBussObjOI_Inv
    pcolTest.Add New clsBussObjOI_Inv
End Sub

I call it inverted because of the fact that it was found after the first method described in details in this article but I think that this method is more natural way of VB/VBA OOP-programming. With it you first create (business) object interface and then you can work with this interface invoking objects of different layers etc…

Framework for MS Access 97 component programming

I create now front-end shell mdb and I set reference to the sample database. I create also a new module in front-end mdb and type the following sub in it:

Public Sub ShellTest()
  SmallTrickyTest
  SmallTrickyTest1
  SmallTrickyTest3
End Sub

Running this code I get five forms opened from library database. You'll not probably believe me but what I get now is a component MS Access 97 programming framework I can set references dynamically at runtime - do you know how to do that? I can easily move the code presented in this article to VB 5.0 ActiveX .DLL. I can do a lot of very advanced and useful things – but this is a subject of another articles.

Conclusion

This article presented advanced MS Access 97 programming techniques based on WithEvents-enabled objects. These techniques are a base of a brand-new programming technology – component object oriented programming of MS Access 97 applications. With some little modifications these techniques are also applicable to all the VBA-enabled MS Office 97 applications as well as VB 5.0/6.0 and the upcoming version of Office2000. These techniques are a base of a very close integration of MS Windows applications using Automation.

Shamil Salakhetdinov is a general- and a software- manager of DARTS Ltd., St.Petersburg, Russia. He is a professional programmer and an occasional technical writer working hardly but joyfully, liking his profession, always looking forward for new programming technologies and sharing his experience with a broad audience of Access-L discussion list, WWDN members and now Smart Access readers. You can reach him by e-mail: shamil@marta.darts.spb.ru

Click to download sample database (samp4sa.zip - 175072 bytes)
HOME	Copyright (c) 1998,1999 by Shamil Salakhetdinov. All rights reserved. Terms of use.
Last updated: June 4, 1999