Programming WithEvents

Marek Kepinski


By using the WithEvents keyword, you can extend the possibilities of Access's classes. Marek presents new ideas on programming in Access.

In the Access 97 Developer's Handbook by Litwin, Getz, and Gilbert (Sybex), there's an example of how to use the WithEvents keyword with OLE Automation objects like Microsoft Word. It turns out that this just scratches the surface of what can be done with the WithEvents keyword. Generally, there are six areas where the WithEvents keyword can be used:


In this article, I'm going to provide some simple examples of the first three areas. I'm going to start with a simple example that shows how you can create a reusable class module that responds to a form's events. The first step is to insert a new class module (which I've called cTextBox in this example) into an Access 97 database and then add the following code:

 Option Compare Database 
 Option Explicit 

 Private WithEvents mTextBox As TextBox 

 Private Sub mTextBox_Exit(Cancel As Integer) 
  MsgBox "You're in the Exit event of " & _ 
         "the cTextBox class." 
 End Sub


The objects that are created from this Class module will respond to the Exit event of any TextBox control, in any form in your database. To use the object, you'd create a form with two TextBox controls -- I'll call them txtText1 and txtText2. What I'll do now is have the routines in the cTextBox class intercept the events fired by these two text boxes. To do that, I have to add some code to the class to accept a reference to a text box. That code looks like this:

 Public Property Set Control _ 
  (ByVal ctlNewControl As TextBox) 
     Set mTextBox = ctlNewControl 
 End Property


This routine will define a property that accepts a TextBox control. The property is called Control, and it sets the mTextBox variable to point to the TextBox passed to the routine. I can now use the cTextBox object in my form as follows: 

 Dim cTB1 As cTextBox 
 Dim cTB2 As cTextBox 

 Private Sub Form_Load() 
     Set cTB1 = New cTextBox 
     Set cTB2 = New cTextBox 
      
     Set cTB1.Control = Me!txtText1 
     Set cTB2.Control = Me!txtText2 
 End Sub


As you can see, there are just four lines of code in the Form_Load event. These lines first create a copy of the cTextBox object for each text box on the form. The lines then tie each text box to one of those cTextBox objects. I then save the form, open it in the Form View, and try to tab out of either of the text boxes. When I do, the message "You are in the Exit event of the cTextBox class" appears.

What does this mean? It means that, with the WithEvents keyword, it's possible to create Class modules that contain standard code for working with form controls and reuse them in different Access projects. You can even place these Class modules in a library database so that the code can be reused without having to cut and paste the Class modules from project to project.

The preceding example is very simple, but it can be extended easily. I'll add this code to my cTextBox object:

 Public Property Let Length(ByVal intNewLength As Integer) 
     intLength = intNewLength 

 End Property 

 Private Sub mTextBox_KeyPress(KeyAscii As Integer)  
     If Len(mTextBox.Text) >= intLength Then 
         Beep 
         KeyAscii = 0 
         Exit Sub 
     End If 
 End Sub


With this code, I've created a property called Length for my object. The Length property is used to set the value of the intLength variable. I also have a KeyPress event that fires every time the user presses a key in the text box. The code in the KeyPress text box checks the length of the Text property in the form and sees whether it exceeds the length specified in the intLength variable. If it does, the event sounds a beep and exits the routine after setting the KeyAscii parameter to 0. Setting the KeyAscii parameter to 0 causes the last keypress to be discarded.

Put it all together and my cTextBox object allows me to specify a maximum length for the text entered into my text boxes. Should the user try to type more characters, he or she will get a warning beep and be prevented from doing so. The only change I have to make in my form is to set the new Length property of the cTextBox object:

   cTB1.Length = 6 
   cTB2.Length = 4


Listening to forms
For my next example, I'm going to create an object that captures the events fired by a form. I'll begin by saving the following code as a class module called cFormListener:

 Private WithEvents frm As Form 

 Public Sub BindForm(NewForm As Form) 
     Set frm = NewForm 
 End Sub 

 Private Sub frm_Activate() 
     MsgBox "You are in the frm_Activate event " & _ 
        "of the cFormListener class.", vbInformation 
 End Sub 

 Private Sub frm_Resize() 
     MsgBox "You are in the frm_Resize event of " & _ 
        " the cFormListener class.", vbInformation 
 End Sub


This object has a BindForm method that accepts a Form object variable. The cFormListener object's frm variable is then set to the Form that's passed to this method. As a result, the frm_Activate and frm_Resize routines will now execute when the Form passed to the BindForm method fires its Activate or Resize events.

To test the cFormListener object, I'll create a form with its BorderStyle property set to Sizable and put the following code in it:

 Dim FListener As cFormListener 

 Private Sub Form_Open(Cancel As Integer) 
     Set FListener = New cFormListener 
     FListener.BindForm Me 
 End Sub


This time I used the Form_Open event to create a cFormListener object and then bind the Form to the object using my BindForm method. Now when I try to resize the form, I'll get the message boxes that show that the cFormListener is responding to my form. I could use this technique to create a reusable Class that would be able to resize any form. To tie the object to the form, I'd need only two lines of code (one of which is the declaration of a variable).

Communicating between controls
For my final example, I'm going to create an object that automatically passes information from one control to another. The cTwoObject object allows you to bind one control to another. I'm going to begin this example with the form that uses the object (see Figure 1 ). The form consists of one command button (called cmdTest1) and one option group with two option buttons (called fraOption1). The form's code module contains the following code:

 Option Compare Database 
 Option Explicit 

 Dim cTwoCtl As cTwoControls 

 Private Sub Form_Load() 
     Set cTwoCtl = New cTwoControls 
      
     Set cTwoCtl.BindCommandButton = Me!cmdTest1 
     Set cTwoCtl.BindOptionGroup = Me!fraOption1 
 End Sub


In this form, clicking on the Command button causes the option group to change, and selecting one of the options in the group changes the Command button. The code that does this is in the cTwoControls object, outside the form's module.

The code in the cTwoControls object can be found in Listing 1 . The code creates BindCommandButton and a BindOptionGroup property that accept a reference to a Command button and an Option group. The two event routines in the listing are fired when either the Command button is clicked or the Option group is updated. In the Command button code, the Mod operator causes the option group to toggle between its two settings. In the Option button code, the caption on the Command button is swapped between bold and normal.


Listing 1: The code for the cTwoObjects object.

 Option Compare Database 
 Option Explicit 

 Private WithEvents mCommandButton As CommandButton 

 Private WithEvents mOptionGroup As OptionGroup 
 Private iState As Integer 

 Public Property Set BindCommandButton _ 
    (ctlNewCommandButton As CommandButton) 
     Set mCommandButton = ctlNewCommandButton 
 End Property 

 Public Property Set BindOptionGroup _ 
   (ctlNewOptionGroup As OptionGroup) 
     Set mOptionGroup = ctlNewOptionGroup 
 End Property 

 Private Sub mCommandButton_Click() 
     If ValidateControls() = False Then Exit Sub 
      
     iState = mOptionGroup.Value 
      
     mOptionGroup.Value = iState Mod 2 + 1 
 End Sub 

 Private Sub mOptionGroup_AfterUpdate() 
     If ValidateControls() = False Then  
        Exit Sub 
     End Sub 
      
     If mCommandButton.FontBold = False Then 
         mCommandButton.FontBold = True 
     Else 
         mCommandButton.FontBold = False 
     End If 
      
 End Sub 

 Private Function ValidateControls() As Boolean 
     ValidateControls = False 
      
     If mCommandButton Is Nothing Then  
        Exit Function 
     End If 
      
     If mOptionGroup Is Nothing Then  
        Exit Function 
     End If 
      
     ValidateControls = True 
      
 End Function


The ValidateControls routine checks to make sure that the references to the Command and Option buttons have been set. If the relevant object variables aren't set, ValidateControls exits with an error code.

Conclusion
The WithEvents keyword gives you power that you never had before. You can move basic functionality from forms into classes and share those classes among Access projects. It's also possible to mix the functionality of two different classes, creating a kind of simple inheritance.

As a bonus, the sample database that comes with this article is set up so that all the classes in it are available to other Access databases (it's available in the accompanying Download File ). If you add a reference in your Access database to my sample database, you'll be able to use all the classes defined in it, just as if they were defined directly in your project. The simple trick to expose classes from library databases to other Access projects is described on the ATTAC Consulting Group Web server at http://ourworld.compuserve.com/homepages/attac-cg/acgsoft.htm .

[Next month, in another article on WithEvents programming, Shamil Salakhetdinov will present some techniques that build on this foundation to dynamically attach events to controls in Access without adding any code to the corresponding forms. -- Ed.]

Download WITHEVNT Sample.

Marek Kepinski works as a technical consultant at Impaq Technology Center, a Microsoft Solution Provider based in Warsaw, Poland. He's currently working on a major database project for a Swiss bank. MKepinski@Impaq.com.pl.


Sidebar: Excel-ent Events
 The WithEvents keyword can also be used to communicate between Access and other Microsoft products. As an example, you can use this code to not only gain control of Excel but also to respond to events fired by Excel:

 Dim WithEvents xcl As Excel.Application


Excel fires a number of events, which I've listed below, that you can respond to from within your Access Basic modules. These events are as follows:



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 allow you to 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 Visual Basic for Applications. 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).

Download sample code for this article here.