Using Dynamic External Event Procedures

Reusable code libraries let you deploy bullet-proof applications. Shamil shows you how to create a library of objects (with inheritance) that you can use to process Form events. All you have to do is use the WithEvents keyword and Access 97 objects.

Modern programming uses the event-driven concept of application workflow. All of the actions that your application performs are triggered by a series of events. As I’ll show, this basic concept (together with some object-oriented programming) allows you to build sound, streamlined, and reusable code. In this article, I’m going to work with the events used by Access programmers to initialize Forms and process database data.

Access makes it very difficult to develop reusable event procedures. Generally, what developers do is write a standard routine and then call it from all the event procedures that it’s to be used from. This method isn’t very satisfactory when you develop large Access applications. For one thing, the calls to these standard routines must be placed in each and every control’s event routine on all of the Forms that use these functions. If you need to change the headers on these routines’ definitions or extend them to let them work with additional events, you have to edit all of the Forms and controls that use these functions.

And, of course, if you put code behind a Form, you can’t use the “light-weight” Forms introduced in Access 95. If you don’t put your code in your Form but just call your functions from the Form’s event properties, you do get lighter-weight Forms. However, you give up all the parameters that Access passes to events like OnKeyDown and OnMouseMove. You also lose the ability to return values in parameters that allow you to cancel events (OnOpen, OnUnload) or suppress standard event processing (OnError, OnNotInList). These Forms aren’t true light-weight ones, either — their first function call will be slow as Access preloads all the code associated with the Form’s events.

Recently, in postings to the Access-L listserve, Marek Kepinski showed how using the WithEvents keyword let him bind Form and control events to a class module’s procedures. (Marek’s article on this topic, “Programming WithEvents,” appeared in last month’s issue of Smart Access.) The result is a very different way to create reusable Access code that supports a Form of inheritance. To show how it works, I’ll start from some standard CBF code, generated by the Access wizards, and move it step by step to a set of class modules.

DEEP code

I created a Form (see Figure 1) holding a wide variety of command buttons. What follows is the code for some of the command buttons (First, New, Delete) as generated by the Access 97 wizards. I dropped all the error handling and will also omit it from my later examples to save space.

 Figure 1

Private Sub cmdFirst_Click()

DoCmd.GoToRecord , , acFirst

End Sub

Private Sub cmdNew_Click()

DoCmd.GoToRecord , , acNewRec

End Sub

Private Sub cmdDel_Click()

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , _


DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , _


End Sub

The first steps in this process are:To replace this code behind the Form (and so create a reusable object), I’m going to create what I call Dynamic External Event Processing (DEEP) objects — objects instantiated at runtime to catch and process the events of any Form and its controls.

  1. Save my sample Form as frmForm2.
  2. Create a Class module called clsFrmForm2.
  3. Move all the code from frmForm2 into the Class module.
  4. Add the following code to the Class module:


Private WithEvents Form As Form

Private WithEvents cmdFirst As CommandButton

Private WithEvents cmdDel As CommandButton

Private WithEvents cmdSave As CommandButton

and so forth.

Several interesting things result from adding this code. All the objects declared in the code now appear in the module’s “Objects” combo box (the one in the top-left corner of the class module window). If you select one of these objects from the combo box and an event from the Event combo box (top-right corner of class module window), an event procedure will be added to the Class module — just what you get when you add an event procedure to a Form. The coolest thing is that this code can be called from the Form simply by setting the object variables (cmdFirst, cmdDel, and so on) to point to the relevant objects on the Form, or to the Form itself.and so forth.

Anyway, on with the creation of your first DEEP object. Add this method to the Class module:

Public Sub DeepsAttach(ByRef rfrm As Form)

Set Form = rfrm

Set cmdFirst = Form![cmdFirst]

cmdFirst.OnClick = "[Event Procedure]"

Set cmdNew = Form![cmdNew]

cmdNew.OnClick = "[Event Procedure]"

Set cmdDel = Form![cmdDel]

cmdDel.OnClick = "[Event Procedure]"


End Sub

This code sets each of my object variables to point to a particular control on the Form or (in the case of the object variable Form) to the Form itself. In addition, you must set the event property that you want to capture to the string “[Event Procedure]”, or the process won’t work.

To tie the objects declared in the Class module to the code in the Class module, you need only add the following code to the Form:

Private mobjDeep As clsFrmForm2

Private Sub Form_Open(Cancel As Integer)

Set mobjDeep = New clsFrmForm2

mobjDeep.DeepsAttach Form

End Sub

The first line of this code just declares an object variable that can point to the object you just created, a clsFrmForm2 object. The first line in the Form’s Open event then creates a clsFrmForm2 object by using the New keyword. The second line in the procedure passes a reference to the Form to that object’s DeepsAttach method. The DeepsAttach method takes that reference and sets its internal variables (Form, cmdFirst, cmdNew, and so forth) to the Form and the controls on the Form. It’s also essential that the object you pass to the DeepsAttach method be the Form and not the usual Me.

With all this work done, start up frmForm2 and click on its command buttons. All of the buttons work as expected, even though the Form has no code behind it beyond the Form_Open event. All of the Click events fired by the buttons are caught by the routines in the clsFrmForm2 Class module.

I’m not sure why the “[Event Procedure]” code line for each command button is required, but it works and seems to be stable enough to be used in real-life applications development. In fact, this lets you turn the Class module on and off with two lines of code. This line removes the entry in the Form’s Click event property and stops the Class module from responding to the Form:


and this line restores it:

Forms![frmForm2]![cmdFirst].OnClick= _

"[Event Procedure]"


But there’s more. This code also lets me implement a kind of Event inheritance. For this example, I created a Class module called clsCommandButton. This Class module contains what I’m going to use as the base functionality for any Command button. Here’s the code that, for the purposes of this example, defines the base functionality of a Command button:

Private WithEvents mcmd As CommandButton

Public Sub InitStandard(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

I then went on to create a specialized Class module, just for the cmdFirst button. I called it clsCmdFirst and gave it the following code:My base functionality sets the Event procedures for Enter and Exit events of the button passed to its InitStandard method. The code also hides the button’s image and sets the button’s caption to the button’s Tag property. The sample Enter and Exit event procedures change the button’s font to Bold and back to normal.

Private mcmd As clsCommandButton

Private WithEvents cmd As CommandButton

Public Sub Init(ByRef rcmd As CommandButton)

Set mcmd = New clsCommandButton

mcmd.InitStandard rcmd

Set cmd = rcmd

cmd.OnClick = "[Event Procedure]"

End Sub

Private Sub cmd_Click()

DoCmd.GoToRecord , , acFirst

End Sub

Like the DeepsAttach method, the Init method accepts a reference to a Command button and associates it with an object variable that’s been declared with WithEvents. However, the routine also creates a clsCommandButton object and passes the Command button reference to the clsCommandButton’s InitStandard method. As a result, this code associates a single button with both my clsCommandButton object and my clsCmdFirst object. Click events will be processed by the clsCmdFirst object, while the Exit and Enter events will be processed by the clsCommandButton object.

I’ve found that I need to be careful about setting these object variables to Nothing when the variables are declared with WithEvents. The usual result of doing so is a GPF. While it’s normally considered good practice to set your objects to Nothing before ending a program, if you’re using these techniques it’s better to just let the variables go out of scope and let Access destroy them and deallocate their memory. To destroy all other objects (the ones used by my procedures that aren’t declared with WithEvents), I added a Terminate method to my objects, put code in the method to set the appropriate objects to Nothing, and called the Terminate method as necessary.


But my goal was to remove all the code from behind the Form. So, my next step was to remove the rest of the code from frmForm2 while leaving the Form’s HasModule property set to True (this is essential). I then set the Form’s OnOpen property to:


The routine frmDeepInit is stored in a standard Basic module and consists of this code:

Public Function frmDeepInit(ByRef rfrm As Form)

Dim obj As New clsFrmForm2

obj.Init rfrm

End Function

This function instantiates the object clsFrmForm2 and uses the clsFrmForm2’s Init method to tie the object to the Form passed to the frmDeepInit routine. The only problem is that as soon as frmDeepInit finishes running, the variable obj goes out of scope and the clsFrmForm2 object that it points to is destroyed. To prevent that from happening, I added a declaration to the clsFrmForm2 class’s declaration section:

Private mobjSelfRef As Object

I also added one line to the class’s Init method:

Set mobjSelfRef = Me

This code causes the object to hold a reference to itself, and as long as it does so, the object won’t be destroyed.

I now have all of the tools in place to create a Custom Class Factory for DEEP objects. The Class Factory is another object, called clsFormDeepOICCF. It has a single method called FormDeepCreate that accepts a Form’s Tag property. Based on the Form’s Tag property, the method creates and returns the appropriate object to manage the Form. This object (as I showed previously) might catch all of the Form’s events or instantiate other objects to handle the Form:

Public Function FormDeepCreate _

(ByVal vstrNavFormType As String) As Object

Dim obj As Object

Select Case vstrNavFormType

Case "BussObj1"

Set obj = New clsBussObjOI1

Set FormDeepCreate = clsBussObjOI1

Case "BussObj2"

Set obj = New clsBussObjOI2

Set FormDeepCreate = clsBussObjOI2

Case Else

End Select

End Function

I then enhanced the frmDeepInit routine so that it uses this Class Factory to create the appropriate object for the Form and then uses that object’s Init property to bind the Form to the newly created object:

Public gcolDeeps As New Collection

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

gcolDeeps.Add obj, CStr(rfrm.Hwnd)

End Function

As the code shows, the function FormDeepInit not only creates the objects, it also adds them to a Collection called qcolDeeps. A Collection’s Add method accepts two parameters: the object to be added to the Collection and a name by which to reference the object. Since the object I’m adding is a Form, I used the object’s Hwnd or handle to the Form’s window as the object’s name. This is guaranteed to be unique and lets me retrieve the object for a Form with just the Form’s window’s handle.

The Class Factory needed some additional routines, which I’ll provide here. The first is a method called FormDeepOIKill that removes a Form from the collection using the Form’s Hwnd:

Public Function FormDeepOIKill(ByRef rfrm As Form)

On Error Resume Next

gcolDeeps.Remove CStr(rfrm.Hwnd)

End Function

The second is a Property called FormDeep that, passed a Form, uses that Form’s Hwnd to retrieve the object associated with the Form:

Public Property Get FormDeep(ByRef rfrm As Form) As Object

On Error Resume Next

Set FormDeep = gcolDeeps(CStr(rfrm.Hwnd))

End Property

Future directions

I’ve really only scratched the surface of what can be done with this technology (the original version of this article was twice as long!). I’m currently exploring ways to use this technique without having to put any code in the Form at all. The sample database includes variations of the code in this article, plus a great deal more. All of the objects discussed in this article are available for use just by setting a reference to the sample library in your Access 97 database.

These techniques are a base of a brand-new programming technology — component object-oriented programming in Access. With minor modifications, these techniques are also applicable to all the VBA-enabled Office 97 applications, VB 5.0/6.0, and the upcoming version of Office 2000. Just like VB, Access can now create standalone objects that can be shared among a variety of applications.


Sidebar: Foundation Concepts

Class modules in Access allow you to define objects by writing VBA code. In a Class module, subroutines and functions appear as methods of the object defined by the Class module. Property Lets, Gets, and Sets let you create properties for your objects.

The techniques presented in this article can be used not only in Access, but in all the other Microsoft applications that support VBA. The version of VBA that’s used by Access 97 contains some new Visual Basic keywords, which can’t be used as identifiers (these include AddressOf, Assert, Decimal, DefDec, Enum, Event, Friend, Implements, RaiseEvent, and WithEvents). If you try to use these keywords in your code, you’ll get a compile error — that is, except for the WithEvents keyword, which can be used in Access 97.

The WithEvents keyword is used as follows:

Dim WithEvents varname As objectname

This statement specifies that varname is an object variable that’s used to respond to events triggered by the object specified by objectname. In other words, if you create an object and point the varname variable at it, you can use WithEvents to respond to events fired by the object.

The following code defines the object variable obj that points at the object Fred. The object Fred fires an event called Alert. To create an event procedure that would run when the Fred object fires the Alert event, you’d write this code:

Dim WithEvents obj As Fred

Set obj = New Fred

Public Sub obj_Alert

MsgBox "Fred has had an Alert event."

End Sub

To get more information on WithEvents, see the “ItemAdded Event” topic in the Access Help file (References collection).

This entry was posted in VBA. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.