How do I know the source of an MS Access VBA error when the program becomes unresponsive?

Do you have error handlers in your code?

Short answer is no.  

Try ctrl/break .

If the VBA window comes up, you'll be able to step through to find the loop.

Other than that:
a. you can sprinkle Debug.Print statements in the code, or do some other form of logging.
b. Put stops in various places so you know you are reaching that point.  This one you need to be careful with though....sometimes you won't get the problem if the code stops and resumes because of the nature of the problem.

Jim.

I have been putting msgboxes in as stops and to let me know the value of variables. I was hoping for something more automated. Thank you

 if it's an actual Access ever, you'll never catch it.

 But what type of operation is it?   If it involves opening record sets, be aware that normally those are performed synchronously, so Access will stop until an operation completes.   It may even go "white" and say "Not responding" when in fact it is still executing.

One way to tell if it is is to look at task manager and sort by CPU utilization descending.   If you see MSACCESS.EXE floating to the top, you'll know it's executing.

Jim.

Well the whole case it depends....the solution for me is to put breakpoints to try to find the last working part of the code....after that you should be able to pinpoint the culprit.
So i start working like this...on the method that starts everything a breakpoint...advance a little. If it works remove the 1st breakpoint and a put a new one to the current working line...again advance a little.. again the same.

IMO, the best thing to do is to have a good error reporting strategy in place, that will already tell you a lot.

As for recordsets, queries taking a long time to execute, not much you can do, but simplify / optimize queries, review the data model ect ..

ctrl break does not always work.

Often code gets into a Do or For loop that cannot be interrupted. If you can find the subroutine that is hanging you can "fix" it by adding a DoEvents inside the lowest level of the loop. For instance
do  
   ... some code
    doEvents  ' <== adding this line will allow Ctrl Break to interrupt the code.
loop until more = false

But what to do if you have no idea which routine is looping?  

Sometimes I get really desperate and simulate a "trace" facility by doing the following. (I wish vba had a native Trace facility.)
I have no idea if you are that desperate. but the following might help.

You will need code like the following in a regular module

Public Sub writelog(param As String) ' please note the parameter must be present

Dim ff As Integer, fileName
fileName = "c:\aaatmp\deletenow.txt"
ff = FreeFile
Open fileName For Output As ff
    Print #ff, param
Close ff
doevents
End Sub

Select allOpen in new window

Next, Change all occurrences  of <   Exit Sub   >
to be  <  call desperateLog : exit sub >   <== you will notice that the global change does NOT have a parameter
vba will respond with   135 replacements were made.

Then compile the project and manually change all 135 compile errors by adding any unique parameter.
For instance start at 10:13::04 one day I created code like this.

sub myMainCode (p1 as long, p2 as worksheet)   '
call DesperateLog("myMainCode")   ' <== useful addition
......  many lines of code
    If thisIsSaturday then call DesperateLog(101304): exit sub
.....
DesperateLog(101315) : End Sub

I then repeated the global changes  Exit Function;  End Sub  and End Function.

When my program crashed, I could examine the log and find the last log entry.

Replacing hundreds of missing parameters with time of day is a mindless pain in the backside, so I wrote a vba progam to do it automatically.   And, my vba program automatically added a useful addition at the beginning of each sub/function that you can see above.

This was all a long time ago, but If you are interested I will try to find that code.

rberke - that is a really cool way to find the problem. I would be interested in see the code if you can find it.

Many interesting suggestions but if you want to take it a step further you have to dive in the deep
like using the Module Object allowing to iterate all the modules/methods and all these custom Error Handlers.
the issue is that an error fired on a function called by another function...another fuction (and so on) usually returns to the top function thus making it hard to pinpoint the issue....but good error handling is always a neccessity although in many cases is somewhat "chatty" and hard to follow at least visually

Expanding the above suggestion
Here is a code that iterates the Modules (it can be tweaked for other Objects) and returns the Procedures Names
Taken from here

Public Sub ModuleList()
Dim dbs As Database
Dim doc As Document
Set dbs = CurrentDb


With dbs.Containers!Modules
For Each doc In dbs.Containers!Modules.Documents
AllProcs doc.Name
Next
End With
dbs.Close
Set dbs = Nothing
MsgBox "Done"
End Sub


'taken straight out of help example in MS Access. You didn't look in


Function AllProcs(strModuleName As String)
Dim mdl As Module
Dim lngCount As Long, lngCountDecl As Long, lngI As Long
Dim strProcName As String, astrProcNames() As String
Dim intI As Integer, strMsg As String
Dim lngR As Long


' Open specified Module object.
DoCmd.OpenModule strModuleName
' Return reference to Module object.
Set mdl = Modules(strModuleName)
' Count lines in module.
lngCount = mdl.CountOfLines
' Count lines in Declaration section in module.


lngCountDecl = mdl.CountOfDeclarationLines
' Determine name of first procedure.
strProcName = mdl.ProcOfLine(lngCountDecl + 1, lngR)
' Initialize counter variable.
intI = 0
' Redimension array.
ReDim Preserve astrProcNames(intI)
' Store name of first procedure in array.
astrProcNames(intI) = strProcName
' Determine procedure name for each line after declarations.
For lngI = lngCountDecl + 1 To lngCount
' Compare procedure name with ProcOfLine property value.


If strProcName <> mdl.ProcOfLine(lngI, lngR) Then
' Increment counter.
intI = intI + 1
strProcName = mdl.ProcOfLine(lngI, lngR)
ReDim Preserve astrProcNames(intI)
' Assign unique procedure names to array.
astrProcNames(intI) = strProcName
End If
Next lngI
strMsg = "Procedures in module '" & strModuleName & "': " _
& vbCrLf & vbCrLf
For intI = 0 To UBound(astrProcNames)
strMsg = strMsg & astrProcNames(intI) & vbCrLf


Next intI
' Dialog box listing all procedures in module.
MsgBox strMsg
End Function

Select allOpen in new window

Yeah, my vba trace simulation was a neat idea.

I found the code and am posting it, but it was part of an old project that was abandoned years ago.
EEMarch19.xlsm
The code I posted barely works, and has not been well tested.

If you are really desperate enough to use the idea, I will happily work to clean it up for you.

To try it out make the global changes to your program as I described above.  
For instance change Exit Sub, to Call mbptrace:Exit Sub.



I suggest you start by changing one module, then continue to the whole project if you like.

Then copy the entire module to the clipboard and call AddVbaLines. Which will put the modified code onto the clipboard.

Paste it on top of your original module and see what happens.

Sorry it is so ugly.

Rberke

The  EEMarch19.xlsm that I recently posted contains AddVbaLines that transforms vba code to include something like "Trace Function". Currently AddVbaLines gets its input from the clipboard, and returns its output to the clipboard.

In theory, John's "deep dive" code could be merged into AddVbaLines, but it would only work in MS  Access, not Excel or outlook. But if I was way more ambitious and only wanted MS Access, I could grab John's code and change AddVbaLines to change the modules directly instead of using the clipboard. I will leave that "deep dive" to some future researcher.  

But John made a good point that an error 5 levels down in the vba Call Stack is sometimes difficult to pinpoint.
Fabrice's object oriented approach is also neat, but I wish it could be extended to list the the ide call stack.
Also, it still requires changes to every vba module that might cause the problem.

I am afraid Error handling in vba will always be a bit cumbersome.

I am new to the errorLogger concept, but I think the following is mostly true.

As I understand it, the big advantage of Fabrice's errorLogger is that it can easily catch and report errors from "Simple Code" that does not have any error handing logic.  
 

Function TestSimpleCode(Value)
Badline: Msgbox “click Ok and watch this”
                SuperCode= 100/value
End function.

Select allOpen in new window

 
But, the errorLogger does not replace or interfere with vba's normal error handlers
 
This is a great advantage: During debugging, you can safely add Set errorLogger = errorLoggerFactory to EVERY subroutine. It will make things clearer and never interfere with program logic.
 
You can even add a Stop command to the beginning of the Class_terminate. When the Stop occurs, you can use IDE to inspect many variables of the routines in the call stack.
 
 
For instance let's say a divide by zero occurs in TestSimpleCode which is  6 levels down in the following call stack.
 
TestSimpleCode is simple (i.e. no error handler)
Routine5 is simple
Routine4 is simple, but has a Set errorLogger = errorLoggerFactory.create(meModule, “Routine4”)
Routine3 is simple
Routine2 is not simple. It has an error handler like the following code

Sub Routine2 ()
      Set errorLogger = errorLoggerFactory.create(meModule, “Routine2”)
      On error goto Err_handler:
      ….. bunch of code
1111      Err_handler:
    … a bit more code
 End sub

Select allOpen in new window

Routine1 is main program
 
If we call Routine1 from the immediate window, the following sequence of events occur.
During execution two instances of errorLogger are created, and then the BadLine msgbox stops.
When the user clicks OK VBA catches the divide by zero.
 Vba searches and deallocates each routine in the call stack until vba finds an error handler.  
TestSimpleCode and Routine5 do not have errorhandlers so they are deallocated
Routine4 is also deallocated because it does not have an error hander.
 
BUT when Routine4’s instance of errorLogger is deallocated, the Class_Terminate is invoked and your Stop command would allow you to inspect variables before the error gets logged to the log file.
               Vba keeps going up the log stack and finally finds an error hander.
Control is given to Routine2 line 1111.  
   
 
There is one disadvantage of the error logger: by the time the call_terminate is invoked, all the lower level routines have been deallocated, so you cannot use IDE to inspect some important variables. For instance you cannot inspect Routine4's variables, even even though Routine4's errorLogger still exists.  

In other words, you might still need to add or improve error handlers in your subroutines.

Well, if a function do not hold any error reporting instructions, it only make sens that nothing is reported.

Basically, it is the developer's responsibility to implement such mecanisms, but to my knowledge, not a single IDE offer the possibility to add / edit / check error handling automatically.
Nothing is lost tho, as there are some code analytis tools on the net that can more or less automate this task, choose your vaforit one.

For instance you cannot inspect Routine4's variables, even even though Routine4's errorLogger still exists.   

Hit F5 (step by step) a couple of times till the execution is given back to routine4.