How to Set a Breakpoint in Startup Code in VBA

Ever set a breakpoint in your Access application's startup code, only to have it get cleared before it got hit? There's a simple workaround.

How to Set a Breakpoint in Startup Code in VBA

Breakpoints are a handy tool to help you debug your code.

But what if you want to debug the code that runs automatically when your application starts?  Can't you just use a breakpoint there?  And if the answer to that question is, "No" (it is), then why won't it work and what is the workaround?

Spoiler alert: the answer to that last question is the VBA Stop statement.

Closing Access Clears All Breakpoints

When you close and reopen an Access application (or Excel workbook, Word document, etc.), any breakpoints that you have set in the application's VBA code get cleared.  As a consequence, it's impossible to set a breakpoint in your application's startup code.

"Startup" Code in Microsoft Access

What exactly do I mean by "startup" code in an Access application?

There is no special "Main" procedure that gets run automatically as there is in some other languages.  In Access, there are two ways to write code that runs automatically when the application starts:

  • Form_Open and/or Form_Load event code in the startup form
  • Code that gets called from the Autoexec macro

Startup Form

You can designate a startup form for your application via the Access Options dialog box:

File > Options > Current Database > Display Form

The startup form is generally either a hidden Global form or a Main Menu form (if you use form-driven navigation).

If you add event-handling code for that form's Open and/or Load event, then it will be executed every time the user starts your application.

AutoExec Macro

The other way to set code to run at application startup is via the special "AutoExec" macro.  

Along with "AutoKeys," the "AutoExec" macro is one of two macros that gain special powers based purely on their names.  When an Access application starts up, it looks for a macro named AutoExec.  If such a macro exists, it gets executed.

In Access applications with both a Startup Form and an AutoExec Macro, the Startup Form opens (and executes any code in its various event handlers) before the AutoExec Macro runs.

Rather than rely on the Form Load/Open events of a startup form, I prefer to put my application initialization code in a public function named Startup.  I then call this function as the only step in an AutoExec macro.

The startup function itself is usually nothing fancy.  Here's a sample:

Public Function Startup()
    'Initialize the global vbWatchdog error handler
    'Open and hide the global form
    DoCmd.OpenForm "Global", WindowMode:=acHidden
    'Other application initialization code
End Function

I prefer not to set a Startup Form in the current database options, as launching it from my Startup function gives me complete control over the process.

Stop Right There!

So what happens if my application suddenly starts crashing right at startup?

My first thought would be to put a breakpoint on the first executable line of code, the Startup function signature, like so:

Unfortunately, as discussed above, when I close my application the breakpoint I set will be cleared.  

The simplest way to work around this limitation is to add the Stop statement to the top of the public function.  This acts just like a breakpoint, except that it won't get cleared when the application closes.

Now, when we restart the application, the Stop statement halts execution and allows us to step through the problematic code:

Stop Statement Ignored in Runtime Mode

Like the Debug.Assert method, the Stop statement gets ignored when executing your Access application in runtime mode.  In other words, the program will skip right over that line of code as if it isn't even there.

External references

Set and clear a breakpoint (VBA)
Office VBA reference topic
Stop statement (VBA)
Office VBA reference topic
Create a macro that runs when you open a database
Learn how to create an AutoExec macro that runs every time you start a database.
Run an Access macro by using a keyboard shortcut
Assert method (Visual Basic for Applications)
Office VBA reference topic

Referenced articles

The Global Form
Storing global variables on a hidden form has some distinct advantages over storing them in VBA.
Runtime Mode in Microsoft Access
There are two ways to force a full version of Access to run a front-end file in runtime mode: the /runtime switch and changing the extension to .accdr.

Image by Walter Knerr from Pixabay

All original code samples by Mike Wolfe are licensed under CC BY 4.0