VBA Debugging Techniques Every Access Developer Needs
The VBA debugger is one of the most powerful tools in Access — and one of the most underused. Here is how to use breakpoints, the Immediate window, and watch expressions effectively.
Most Access developers debug VBA the same way: add a MsgBox to display a variable value, run the code, close the dialog, remove the MsgBox, repeat. It works, but it is slow and leaves traces in your code.
The VBA debugger — built into the Access VBA editor — is dramatically more powerful. Once you know how to use it, you will never go back to MsgBox debugging.
The VBA Editor Debugger Toolbar
Open the VBA editor (Alt+F11) and look at the toolbar. The debug-related buttons are:
- Run (F5) — run the code or continue from a breakpoint
- Break (Ctrl+Break) — pause running code
- Reset (square button) — stop execution and reset
- Step Into (F8) — execute one line, stepping into called procedures
- Step Over (Shift+F8) — execute one line, treating called procedures as a single step
- Step Out (Ctrl+Shift+F8) — finish the current procedure and pause at the caller
These are the core controls. The rest of debugging flows from them.
Breakpoints: Pause Execution at Any Line
A breakpoint tells VBA to pause execution at a specific line. When execution reaches that line, the editor opens, highlights the line, and waits for you to inspect the state and decide what to do next.
Set a breakpoint: click in the left margin next to any line of code. A red dot appears. Press F9 to toggle a breakpoint on the current line.
Remove a breakpoint: click the red dot again, or press F9 on the highlighted line.
Remove all breakpoints: Debug > Clear All Breakpoints (Ctrl+Shift+F9).
Breakpoints are not saved when you close the database — they are session-only. This is usually what you want.
When execution pauses at a breakpoint, you can:
- Hover over any variable to see its current value in a tooltip
- Use the Immediate window to inspect or change values
- Step through code line by line
- Continue execution (F5) or stop (Reset)
The Immediate Window: Your Interactive Console
The Immediate window (Ctrl+G in the VBA editor) is the most useful debugging tool in VBA. It lets you:
Print variable values:
' In the Immediate window, type:
? Me.CustomerID
? strFilter
? Format(Date, "YYYY-MM-DD")
? CurrentDb.Name
The ? is shorthand for Print. Press Enter and the value appears on the next line.
Execute statements:
' Change a variable value mid-execution
strFilter = "CustomerID = 42"
' Call a function
? IsValidEmail("test@example.com")
' Run a SQL statement
CurrentDb.Execute "UPDATE Orders SET Status='Closed' WHERE OrderID=99"
Test expressions before using them in code:
? DateAdd("d", 30, Date)
? Left("Hello World", 5)
? InStr("test@example.com", "@")
The Immediate window is available both during debugging (when execution is paused) and at any time in the VBA editor. Use it to test expressions before writing them into your code.
Watch Expressions: Monitor Variables Continuously
Watch expressions let you monitor the value of a variable or expression continuously as code executes — without stopping to check manually.
Add a watch: Debug > Add Watch (or right-click a variable and choose Add Watch).
Three watch types:
- Watch Expression — displays the value in the Watch window as you step through code
- Break When Value Is True — pauses execution when the expression becomes true (e.g.,
intCount > 1000) - Break When Value Changes — pauses execution whenever the value changes
Break watches are particularly useful for tracking down bugs where a variable is being changed unexpectedly. Set a "Break When Value Changes" watch on the variable, run the code, and execution will pause the moment something changes it.
Stepping Through Code
Once execution is paused at a breakpoint, use the step controls to move through code carefully:
F8 (Step Into) — executes the current line. If the line calls another procedure, steps into that procedure.
Shift+F8 (Step Over) — executes the current line. If the line calls another procedure, runs it completely and pauses at the next line in the current procedure. Use this when you trust the called procedure and do not want to step through it.
Ctrl+Shift+F8 (Step Out) — runs the rest of the current procedure and pauses at the line that called it. Use this when you have stepped into a procedure and want to get back to the caller quickly.
F5 (Continue) — resumes normal execution until the next breakpoint or the end of the code.
A common debugging pattern: set a breakpoint near where you think the problem is, run the code, then step through line by line watching variable values until you see something unexpected.
The Call Stack
The call stack shows the chain of procedure calls that led to the current execution point. View it with View > Call Stack (Ctrl+L) when execution is paused.
If ProcedureA calls ProcedureB which calls ProcedureC, and you are paused in ProcedureC, the call stack shows all three — with ProcedureC at the top. You can click any entry to see the code at that point in the call chain.
This is invaluable for understanding how you got to a particular line of code, especially in complex databases where procedures call other procedures several levels deep.
Debug.Print: Logging Without MsgBox
Debug.Print writes output to the Immediate window without pausing execution. It is the right tool when you want to log values as code runs without interrupting the flow.
Sub ProcessOrders()
Dim rs As DAO.Recordset
Dim intCount As Integer
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Orders WHERE Status='Pending'")
Debug.Print "Processing " & rs.RecordCount & " pending orders at " & Now()
Do While Not rs.EOF
intCount = intCount + 1
Debug.Print " Processing OrderID: " & rs!OrderID & " for CustomerID: " & rs!CustomerID
' ... processing logic ...
rs.MoveNext
Loop
Debug.Print "Completed. Processed " & intCount & " orders."
rs.Close
End Sub
Run the procedure, then open the Immediate window to see the log. Much cleaner than MsgBox for tracing execution flow.
Remove Debug.Print statements before deploying — or use a conditional constant to disable them in production:
#Const DEBUG_MODE = True
Sub ProcessOrders()
#If DEBUG_MODE Then
Debug.Print "Starting ProcessOrders at " & Now()
#End If
' ... rest of procedure
End Sub
Structured Error Handling for Production Code
Debugging tools help you find bugs during development. Error handling catches unexpected problems in production.
A robust error handler:
Sub ImportCustomerData(strFilePath As String)
On Error GoTo ErrorHandler
Dim db As DAO.Database
Dim intImported As Integer
Set db = CurrentDb
' ... import logic ...
intImported = 42 ' placeholder
MsgBox intImported & " customers imported successfully.", vbInformation
Exit Sub
ErrorHandler:
Dim strMsg As String
strMsg = "Error in ImportCustomerData:" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"File: " & strFilePath
' Log to a table
On Error Resume Next ' don't let logging failure mask the original error
db.Execute "INSERT INTO tblErrorLog (ErrorDate, Procedure, ErrorNum, ErrorDesc) " & _
"VALUES (#" & Now() & "#, 'ImportCustomerData', " & _
Err.Number & ", '" & Replace(Err.Description, "'", "''") & "')"
On Error GoTo 0
MsgBox strMsg, vbCritical, "Import Error"
End Sub
Key elements:
On Error GoTo ErrorHandlerat the top of every procedure that touches dataExit Subbefore the error handler (so it does not run on success)- Log errors to a table for post-mortem analysis
- Show a meaningful message to the user, not just the error number
A Debugging Workflow
When you encounter a bug:
-
Reproduce it reliably. You cannot fix what you cannot reproduce. Find the exact steps that trigger the problem.
-
Set a breakpoint near where you think the problem occurs — or at the start of the procedure if you are not sure.
-
Step through the code watching variable values. Look for the first moment something is not what you expect.
-
Use the Immediate window to test hypotheses. "What does this expression evaluate to? What if I change this variable?"
-
Fix the root cause, not the symptom. If a variable has the wrong value, find where it gets set incorrectly — do not just add a check that corrects it after the fact.
-
Test the fix by running the reproduction steps again.
The VBA debugger makes this process fast. Once you are comfortable with breakpoints, stepping, and the Immediate window, most bugs that used to take an hour to find take ten minutes.
Work With Access Evolved
If you found this article useful, Access Evolved specializes in exactly this kind of work — from hands-on Microsoft Access support to full database modernization projects.
Stuck debugging a complex VBA issue in your Access database? Schedule a free call and get a second set of expert eyes on the problem.
Thinking about migrating?
Ready to move beyond Microsoft Access?
Access is a great starting point, but growing businesses often hit its limits. Anthony Passeri specializes in migrating Access databases to modern platforms — Power Platform, Power Apps, SQL Server, Azure, and more — so you keep your data and workflows without the headaches.
Written by
Anthony Passeri, MBA
Access Expert and Editor
Bridging the gap between Access and the Future
Explore Topics
Keep reading
More on VBA & Macros
Building an Audit Log in Access with VBA
Knowing who changed what and when is essential for compliance and troubleshooting. Here is how to build a reliable audit trail in Access using VBA form events and a log table.
Automating Access Reports with VBA
Stop manually opening and printing reports. VBA lets you automate report generation, apply filters on the fly, export to PDF, and email output — all without user intervention.
VBA Class Modules: Write Cleaner Access Code
Class modules bring object-oriented structure to VBA. Learn how to use them in Access to reduce repetition, improve maintainability, and write code that scales.