VBA Error Handling Best Practices in MS Access

Most of the panicked calls I get start the same way: someone clicks a button, a cryptic box pops up saying "Run-time error 3061" or "Object variable not set," and the database just stops. The person on the phone is usually an office manager, not a programmer, and the problem isn't really the error itself. It's that nobody built the database to handle errors gracefully. In this post I want to explain, in plain terms, what good error handling looks like and why it matters for the tools your business runs on every day.

Why error handling matters more than you think

Every button, form, and report in an Access application is often backed by a bit of VBA code. When that code runs into something it didn't expect, one of two things happens. Either the developer planned for it, so the database shows a friendly message and keeps working, or nobody planned for it, and the user gets a technical dialog with buttons like "Debug" and "End." To a non-technical person, that second scenario feels like the whole system broke.

Poor error handling doesn't just look bad. It can leave a record half-saved, a transaction incomplete, or a report generated with wrong numbers. I've seen databases where a failed update silently left inventory counts out of sync for weeks. The error was there the whole time, just swallowed and ignored.

The goal of error handling isn't to hide problems. It's to catch them, record what happened, and let the person keep working instead of staring at a crash.

The three ingredients of solid VBA error handling

When my team reviews or builds an Access application, good VBA error handling best practices in MS Access come down to three things working together: catching the error, telling the user something useful, and logging what actually went wrong so it can be fixed.

1. Catch the error in every procedure that matters

In VBA, you catch errors with a small block of code at the top and bottom of a procedure. The classic pattern uses On Error GoTo to jump to a labeled section when something fails. Without it, Access falls back to its default behavior, which is that ugly dialog. Every procedure that touches data, opens a file, or runs a query should have this structure.

2. Show the user a message that helps, not one that confuses

There's a big difference between "Run-time error 3022" and "That customer number is already in use. Please enter a different one." The first tells the user nothing. The second tells them exactly what to do. Good handling translates technical failures into instructions a regular person can act on.

3. Log the details somewhere you can review

The friendly message is for the user. The technical details are for whoever maintains the database. I always add a small logging routine that quietly records the error number, the description, the procedure name, and the time to a table or text file. When someone tells me "it broke yesterday afternoon," that log turns a guessing game into a five-minute fix.

A simple, reusable pattern

You don't need anything fancy. A dependable structure looks like this in every procedure:

1. Start with On Error GoTo ErrHandler as the first line after the declarations.
2. Write the normal code that does the work.
3. Add an Exit Sub (or Exit Function) so the code stops before reaching the error section when everything goes well.
4. Add the ErrHandler: label, then a call to a shared logging routine and a clear MsgBox for the user.
5. Finish with Resume Next or a clean exit, depending on whether it's safe to continue.

The key is consistency. When the same pattern appears in every procedure, and the logging is handled by one central routine, the whole application behaves predictably. Fixing a problem in the logging becomes a one-place change instead of hunting through hundreds of buttons.

Common mistakes I find in existing databases

Most of the applications I inherit weren't built by professionals. They grew over years, one button at a time, often by a staff member who taught themselves along the way. A few patterns show up again and again.

• The blanket "On Error Resume Next" — this tells VBA to ignore every error and keep going. It hides real problems and is the single most damaging habit I see. Data gets corrupted quietly and nobody notices until it's expensive.
• No handling at all — the default crash dialog, which lets curious users click "Debug" and land inside the code, where one accidental keystroke can break things further.
• Handlers that do nothing useful — a message box that just shows the raw error number, giving neither the user nor the maintainer any real information.
• No central log — so every troubleshooting session starts from zero, relying on the user to remember what they clicked.

Two approaches, side by side

To make the difference concrete, here's how the same failed record save behaves with and without proper handling:

When to bring in help

If your database was built in-house and it throws technical errors your staff have learned to "just click through," that's a sign the error handling was never really designed. Retrofitting proper handling into an existing application is usually straightforward work. My team charges from $30/hour for this kind of cleanup, and it's often a small job with a big payoff in stability and staff confidence.

For a brand-new database, error handling should be part of the build from day one, not bolted on later. A new custom application from us starts at $450, and proper handling is baked into how we work rather than an extra line item. If you rely on the database daily and want someone keeping an eye on it, ongoing support starts at $650/month.

If your Access application is throwing errors nobody understands, or you'd just like a second opinion on how it's built, feel free to reach out to me and my team at XS-Data Solutions. I'm happy to take a look and tell you plainly what's going on.