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 event property? This trick lets you do it.
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)
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.
UPDATE [2021-12-4]: Updated title from "Watch This!" to the clearer title, "How to Set a Breakpoint Inside of an Access Query."