Calling VBA Functions from the Form or Report Property Sheet

Mike Wolfe -

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 Function, even if you don't need its return value (e.g., when calling from a form/report/property control, etc.)

Event-Driven Programming

Microsoft Access applications follow the event-driven programming paradigm.

Rather than run a single "Main" procedure from start to finish (as a command-line utility might), an Access application responds to user events, such as:

  • Button clicks
  • Data changes
  • Opening and closing of forms and reports
  • Etc.

Each one of the above actions has an associated event.  As developers, we can "handle" these events one of two ways:

  • With an event handler routine defined as a Sub
  • By calling a Function from the event entry on the Property Sheet

Sample: Using a Sub to Handle an Event

Here's how you would use a Sub to handle an event for a control.

  1. Select the control in Design View
  2. Go to the Event tab in the Property Sheet (open with [F4])
  3. Set the value for the appropriate event to  "[Event Procedure]"
  4. Click the button with three dots to go to the autogenerated Sub in VBA

For lots more details, refer to my full article on the topic:

Event Handlers in Microsoft Access VBA
You need event handlers to write event-driven code. This tutorial will help new users get up to speed and may even teach seasoned pros a thing or two.
No Longer SetMike Wolfe

Sample: Using a Function to Handle an Event

Here's how you would use a Function to handle an event for a control.

  1. Select the control in Design View
  2. Go to the Event tab in the Property Sheet (open with [F4])
  3. Set the value for the appropriate event to  =MyFunctionsName()
  4. Create a Function with the code you want to execute when the event occurs
Function MyButtonKeyPressed()
    MsgBox "You pressed a key on my button!"
End Function

Notes About the Function Call

  • When called from the Property Sheet, functions are evaluated as expressions rather than pure code, which can lead to some important differences
  • You MUST include the leading equal sign, otherwise Access treats the value as the name of a macro
  • You MUST include values for all optional parameters, even if you want to use their defaults (otherwise you get a "wrong number of arguments" error)
  • You SHOULD include the trailing parentheses, even if the function takes no parameters, otherwise you could see unexpected behavior (e.g., removing the parentheses in the example above leads to Access auto-correcting the value to =[MyButtonKeyPressed]; the more concerning issue is that the expression gets evaluated three times per event without the parentheses [BAD] versus only once per event when the parentheses were included [GOOD])
  • The called function CAN be a Private Function, if it is located within the code behind of the form or report where it is referenced
  • The called function MUST be a Public Function, if it is located within a Standard Module
  • The called function CANNOT be in a Class Module
  • The called function CANNOT receive event arguments (for example, there is NO way to access the value of the key the user pressed if you call a Function from the Property Sheet's KeyPress event; instead, use [Event Procedure] and the standard Sub event handler if you need access to the values of the event arguments, such as Private Sub btnMyButton_KeyPress(KeyAscii As Integer))

Lightweight Forms and Reports

In Microsoft Access, so-called "lightweight" forms and reports are those that do not have a VBA code module behind them (i.e., HasModule = No).

Lightweight objects are often extolled as a way to improve performance in Access applications.  Personally, I think those benefits are overblown, but they do exist.  In my experience, the mere presence of a code module does not lead to a noticeable slowdown when opening a form or report. Rather, I've found poorly designed queries are usually to blame.

That said, if you have a form or report with only a bit of code behind it, you may be able to convert it* from a "heavyweight" object to a "lightweight" object.  To do that, convert all the [Event Procedure] event handlers to calls to Public Functions in Standard Code modules, as demonstrated above.

* Note that this is only possible if you do not need access to any of the event handler arguments.

Cover image created with Microsoft Designer