How to Set a Breakpoint Inside of an Access Query

Did you ever want to set a breakpoint inside of an executing query?  

How about inside a form or control event property?  (See screenshot below.)

My early programs are full of Event properties with direct function calls:



(excerpted from Lightweight Forms? Just Don't)

The Workaround

Well, you can't set an actual breakpoint in either of those places.  But you can do the next best thing: use this Watch() dummy function to wrap the call and place your breakpoint within the function:

Function Watch(Val, Optional CalledFrom As String = "")
    'Debug.Assert Val <> "Value to watch for"
    'Debug.Print Val, CalledFrom
    Watch = Val
End Function

The function simply passes the value straight through as the returned result.  Depending on your circumstance, you can uncomment one of the first two lines to:

• halt execution when a certain value is passed to the function
• print the value passed to the function
• print the source of the function call (e.g., if you want to watch the values from multiple columns in the same query)

It's a surprisingly handy little function that I've found several occasions to use over the course of my VBA career.

Performance Warning: Use For Debugging Only!

This is strictly a tool for debugging.  You should be especially careful about adding this to a query that returns a lot of rows or one that is based on a linked SQL Server table.  Adding VBA functions to queries based on linked SQL Server tables can force Access to copy the entire table contents across the network, especially if the function is applied to a column in the WHERE clause.

Image by Pexels from Pixabay

UPDATE [2021-12-4]: Updated title from "Watch This!" to the clearer title, "How to Set a Breakpoint Inside of an Access Query."