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 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.
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.]
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).