Event Handlers in Microsoft Access VBA

This article is one in a series on Subs vs. Functions in VBA.

One of the "additional facts" from my Subs vs. Functions article was this:

Sometimes you MUST use a Sub (e.g., event handlers)

For seasoned VBA developers, this is something we don't even think about anymore, but I remember it being a bit confusing when I first got started.

Attaching Event Handlers to Events

Event handlers are not explicitly attached to form, report, or control events the way they are in some other programming languages.

Instead, the events are attached via a special naming convention in the "code behind" Form or Report module in VBA.

The standard naming convention looks like this:

Private Sub {ControlName}_{EventName}([Parameter As {DataType}])

For example, here's the event handler for the "On Click" event of a command button named Command0:

Private Sub Command0_Click()
End Sub

Event Handler Parameters

Different event handlers have different sets of parameters.

For example, the "On Click" event of a command button has no parameters (as shown above).  However, the "Mouse Down" event includes parameters that describe certain conditions at the time of the event:

• Which button was clicked (left/right/middle)
• Whether the [Shift] key was depressed
• The horizontal position of the mouse cursor
• The vertical position of the mouse cursor

Private Sub Command0_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
End Sub

Creating Event Handler Code

Luckily, you don't have to memorize all the Sub definitions for every type of event handler.

The easiest way to generate the event handler code is via the Property Sheet in Form or Report Design view (shortcut key [F4]).  Set the appropriate event property to "[Event Procedure]" (via the dropdown) and then click the button with three dots [...] to go to that event handler in VBA:

Breaking the Naming Convention

When an event handler is active in the VBIDE window, the two dropdowns at the top of the code window are set to the name of the object (left dropdown) and the name of the event (right dropdown).

When a non-event procedure is selected, the left dropdown is set to "(General)" and the right dropdown is set to the name of the procedure.

If you change the name of the event handler, such as by removing the underscore between the object name and event name, then you break the connection between the event and the procedure.  In the screenshot below, notice the changes from the earlier screenshot:

• Left dropdown: Command0  --> (General)
• Right dropdown: Click --> Command0Click

If your event handler does not seem to be getting called, one of the first things you should check is to see if the left dropdown shows the name of the object or the string (General).  If it says (General) then there's a problem with your procedure name.

Changing the Control Name Breaks the Event Handler Connection

A common mistake that many new (and experienced) Access developers make is to change the name of a control on the form or report, but not adjust the VBA to match it.

For example, if we were to change the name of the button on the form to btnMyButton, then the previously working Sub Command0_Click() will be broken.  The easiest way to fix this is to click the button with the three dots on the property sheet [...] next to "[Event Procedure]" and then cut and paste the code from the old button to the new one.

Breaking the Event Handler "Signature"

The declaration line of a routine (a Sub or Function) is known as its "signature."  

If you make changes to the signature of an event handler in VBA, you will get the following runtime error when executing the event:

The expression On Click you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name.

Here's a simple example of breaking the event handler's "signature."

'GOOD: the default, correct procedure "signature" 
'      for a command button Click event
Private Sub Command0_Click()
End Sub

'BAD: modified, incorrect procedure "signature"
Private Sub Command0_Click(Button As Integer)
End Sub

Note that the actual names of the parameters are not important.  The critical parts are the data types, the order of the parameters, and the number of parameters.

For example, the original procedure definition and the modified version below are equivalent procedure signatures, so there will be no problem at runtime (assuming only one or the other is present in your code--if you copy the code exactly as shown below you'll get an "ambiguous name detected" error):

'Original default MouseDown event handler code:
Private Sub btnMyButton_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

End Sub

'Equivalent procedure signature:
Private Sub btnMyButton_MouseDown(WhichButton As Integer, _
                                  ShiftFlag As Integer, _
                                  X As Single, Y As Single)

End Sub

That being said, just because you can change the parameter names of an event handler that does NOT mean that you should.  

I suppose one reason you might want to change the parameter name is if you were trying to avoid naming conflicts with a global variable or constant that you need to reference from inside the routine.

Final Thoughts

If you follow these guidelines, you will minimize the number of event-handler related problems in your code:

1. Rename controls before generating event handlers in code
2. Do not rename a control once it has an event handler in code
3. Let Access create your event handler Sub definitions for you
4. Do not rename your event handlers or any of their parameters (unless you are doing it to match a control name change...but in that case, refer to 1. above)