Debugging intermittently failing VBA code
I had a problem where an API call would work when stepping through the VBA code but would intermittently fail crashing Access or give an "out of memory" error. (Out of memory is Access's default error message when it doesn't know what else is the problem.)
Someone just posted a similar problem in the comp.databases.ms-access newsgroup.
From time to time I strike a problem where Access will suddenly shut down while running some code (Dr Watson message), wiping out any forensics (diagnostic traces such as debug.prints, etc).
Instead of using debug.print use a call to the following procedure. Change the file name to suit your environment of course.
Sub LogToTextFile(Comment As String)
Open "Q:\1 access\Fleet Mgmt\zzz Service log.txt" For Append As #1 ' Open file for output.
Write #1, Comment
Close #1
End Sub
Note that you have to close the file after writing each line. The file doesn't get closed properly if Access crashes and you won't be able to see anything in the text file.
Tony