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 Micrsoft 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:
•The creation of classes with reusable functionality
•"Listening" to some object's events and responding to them
•Direct communication between Access controls (outside forms' modules)
•Communication between forms and their controls in Access (including subforms and ActiveX controls)
•Communication between Access applications and other applications that support OLE Automation (Excel, Word, PowerPoint, Outlook, and so on). See the sidebar "Excel-ent Events" for an example of the kinds of events that you can intercept in this scenario.
•Dynamic attachment of events to controls
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
Private WithEvents mTextBox As TextBox
Private Sub mTextBox_Exit(Cancel As Integer)
MsgBox "You're in the Exit event of " & _
"the cTextBox class."
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
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
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
Private Sub mTextBox_KeyPress(KeyAscii As Integer)
If Len(mTextBox.Text) >= intLength Then
KeyAscii = 0
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
Private Sub frm_Activate()
MsgBox "You are in the frm_Activate event " & _
"of the cFormListener class.", vbInformation
Private Sub frm_Resize()
MsgBox "You are in the frm_Resize event of " & _
" the cFormListener class.", vbInformation
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
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
Dim cTwoCtl As cTwoControls
Private Sub Form_Load()
Set cTwoCtl = New cTwoControls
Set cTwoCtl.BindCommandButton = Me!cmdTest1
Set cTwoCtl.BindOptionGroup = Me!fraOption1
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
Private WithEvents mCommandButton As CommandButton
Private WithEvents mOptionGroup As OptionGroup
Private iState As Integer
Public Property Set BindCommandButton _
(ctlNewCommandButton As CommandButton)
Set mCommandButton = ctlNewCommandButton
Public Property Set BindOptionGroup _
(ctlNewOptionGroup As OptionGroup)
Set mOptionGroup = ctlNewOptionGroup
Private Sub mCommandButton_Click()
If ValidateControls() = False Then Exit Sub
iState = mOptionGroup.Value
mOptionGroup.Value = iState Mod 2 + 1
Private Sub mOptionGroup_AfterUpdate()
If ValidateControls() = False Then
If mCommandButton.FontBold = False Then
mCommandButton.FontBold = True
mCommandButton.FontBold = False
Private Function ValidateControls() As Boolean
ValidateControls = False
If mCommandButton Is Nothing Then
If mOptionGroup Is Nothing Then
ValidateControls = True
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.
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.]
Read about the download WITHEVNT.ZIP on this page
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."
To get more information on WithEvents, see the "ItemAdded Event" topic in the Access Help file (References collection).