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.
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
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.
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 EnableVBWatchdog '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.
Image by Walter Knerr from Pixabay