Access’ Little Secret – Classes and WithEvents

Classes and WithEvents are Access’ little secret.  It’s hard to find any information about the subject on the web, so I’m going to change that!

A Discussion of Classes and Events

We need to start with some basic concepts.  I am assuming that you know how to program, so I will quickly move from basics to the stuff you may not know.

Visual Basic for Applications (VBA) is an interpreted language.  The English language is source code which is compiled into P-Code the first time the module is loaded.  The P-code is then executed by an interpreter line by line as it is encountered.  In no case is an EXE file ever created.  Additionally, there are VBA variations specific to each Office application — one for Access, Excel, Word, etc.  Each of these variants has keywords useful to their respective environments.  Excel VBA has spreadsheet keywords, and Word VBA has document kinds of keywords.  Access has database kinds of keywords.  Additionally, there is a DLL or set of DLLs which provides an object model for each environment.


Modules are containers in which the English language source code for VBA is stored.  Modules can contain variables and constants with public or private scope.  They also can contain functions and subs with public or private scope.  You can have one or dozens of modules in an Access container.


When any function is called or any variable or constant is referenced, Access loads the entire contents of that module into memory.  All of the functions and subs are loaded, and all of the variables and constants are created in memory.

Most Access developers understand modules, and I won’t go into more detail here.


  • The Code Behind Forms module in a form is a class module.
  • A class is a module, but a module is not a class.
  • A class has properties and behaviors that a module does not.
  • A class is actually instantiated when a ‘set cls = new class’  statement is executed.  In other words, the newkeyword causes an instance of the class to be loaded into memory, and the instance stays in memory until it is specifically unloaded.  The instance is turned over to the garbage collector when the last pointer to the instance is set to nothing.
  • Like a module, a class can contain data (variables) and code.  However, the variables in a module can contain only one value at a time.
  • A class can be loaded into memory as many times as you want (limited only by the size of your memory), and each instance of a class can contain its own value in its variables.
  • All instances of a class share code, but they do not share variables.  In other words, the code is loaded into memory only one time, but the variables are loaded once per class instance loaded.  Constants are loaded only once.
  • The class (and every object, including forms and controls) unloads from memory when the last variable holding a pointer to the object is set to nothing.
  • A class has two built-in Events that fire — one as a class instance loads (Class_Initialize), and the other as the class instance unloads (Class_Terminate).  Class_Initialize can be used to initialize objects such as collections.  Class_Terminate can be used to clean up pointers to any object variable in the class header.
  • A class can sink events from Access objects such as text boxes, command buttons, and so forth.
  • A class can raise events of its own.

Think of a class as a place to store information and code about something in the real world.  Perhaps you have a clsPerson.  That class has a bunch of variables called FirstName, LastName, SSN, ColorHair, ColorEyes, Gender, Birthdate, etc.  Load an instance of that class and fill in the data about John Smith; load another instance and fill in the data about Mary Smith, etc.  You might then have a piece of code that takes the Birthdate and calculates the current age from that.  The data and the code all are stored together in the class.


Events can be thought of kind of like a radio transmission.  The radio station crew transmits a signal, but they have no idea whether anyone is listening.  In the case of events, this is called “Raising (or sourcing) an event”.

If someone is listening to that radio signal, then the people listening can do whatever they want with the signal they are receiving.  They can do nothing at all; they can use it as a signal to launch an attack on an enemy; they can enjoy music.  The important thing to understand here is that what the listener does is up to the listener.

In the case of events, receiving the signal is called “sinking” the event. Notice that the people broadcasting the signal don’t know or care whether anyone is listening. Nor do they know or care what the listener (if one even exists) does with the signal.

When you open a form, the form is constantly raising events.  It raises OnOpen, OnClose, OnCurrent, BeforeUpdate, AfterUpdate, MouseMove, etc.  The events are raised whether or not anyone is listening.  The form neither knows nor cares whether anyone is listening to (sinking) those events; it is simply raising these events so that if anyone is listening to (sinking) the events, they can do whatever they want when the events fire.

When you place a control on the form, the control raises events under certain circumstances.  When the control gets the focus, it raises an OnFocus event; when it loses the focus, it raises a LostFocus event; it raises a BeforeUpdate, AfterUpdate, etc.  Of course, these events depend on what the user does; in other words, they don’t happen unless the user manipulates the control in the correct manner — for example, entering data.  But notice that while the control always raises the event, it neither knows nor cares whether anyone is listening, nor does it know or care what the listener does with the event if anyone is listening (sinking the event).

This is a critical point to understand:  The object raising an event does not know or care about the listener or what the listener does.   The reason this is critical is the fact that it allows you to design an interface between objects that is totally asynchronous or disconnected.  Have you ever built a subform and referenced a control on the parent form?  Have you ever tried to open that subform by itself?  It complains that it cannot find the control on the parent.  The subform has a “connected” interface to the parent; without the parent, it cannot do its thing correctly.  The event “Raise/Sink” interface eliminates that dependence.  The object raising the event does not depend on having a receiver of the event in order to function correctly.  The receiver of events does not depend on the broadcaster’s existing in order to function; although, of course, it cannot do whatever it would do with the events if they are not being broadcast.  But each side can be loaded and code can execute without the other side being loaded, without compile errors, etc.

The last thing to know is that regular modules cannot sink events, but a class can.  A regular module cannot raise an event, but a class can.  Classes are modules, but modules are not classes.

Just like the built-in Access objects, your custom classes can raise events.

Existing Access objects – what you already know

Every Access developer quickly learns to handle object events raised by the form and control objects.  It is old hat to most of us to double click in the OnOpen event property of a form and place code in there to be run when the form opens.  It is old hat to double click on a command button OnClick event property and place code in there to run when the command button is clicked.  You could say that a command button has no purpose without its click event.

As we know, all of these event sinks, as they are called, are placed into the ‘code behind forms’ module of the form itself.  What we often do not appreciate, however, is that the form’s module is nothing more than a special class, very similar to the classes we are discussing.  The form’s class is special in that we do not have to do anything to allow objects on the form to raise events and have the form sink those events.  Place a text box on a form, double click in the OnEnter event property of the text box, and the VB editor very helpfully creates an empty event sink stub.  The form’s class module opens, and you are taken directly to the event stub just created.  That is all ‘magic’ that the editor does to make the programmer’s life easier.  But in the end, it is just an Access object raising an event and an event sink in a class sinking the event and causing code to run when that event fires.

What is not obvious is that any event can be sunk in multiple locations.  For example, a command button on one form can have its event sunk in an entirely different form.  Or in twenty different forms sequentially.  Of course, those twenty forms must have an event sink defined for the command button, and all the forms must be open in order to sink the event.  Think back to the ‘radio’ example above.  Just like a radio signal, more than one person can receive the signal.  More than one event stub can sink an event.  In the case where this happens, the code executes in the order that the class modules instantiate – in this case, the order that the forms open.

This may all sound a bit confusing, but just remember that an event raised by any object may be sunk in more than one location. It isn’t very common to sink an event in more than one place, but I do use it in what I call a message class.

Leave a Reply

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