Error loop when I misscode in a function?

[stumped]

Hi All, when I write a function to use in an Access query more often than not I'll get it wrong at first and I get stuck in a loop and prompted by various dialogue boxes and basically I struggle to break out of the error loop. Any pointers please?

[orange]

Don't start by having a major loop ie 500 iterations.
Step through your code -- use F8 -- research it.
Get familiar with DEBUG.Print statements

see debugging at http://www.cpearson.com/excel/debug.htm

[John_G]

Hi -

If you code is stuck in a loop, you can often use Ctrl-Break to stop it, at which point you can get into debug mode to see what the problem is.

for me, I find that an infinite loop is often caused by a missing .movenext while processing a recordset.

HTH

John

[stumped]

Hi Both, thanks for getting back. appreciate the advice about a smaller dataset but the problem was only occurring deep within a large number of records. In this case it was working with dates and I have to write small functions to deal with the fact that the dates were in a text format and sometimes missing, null's, zero length strings, spaces etc. So it wasn't until I ran the query with a return of several thousand records that any errors in my function are found. the Microsoft VB for Applications Windows ap[pears with the error highlighted yellow, but I just want to break out of the query which i can seem to do. I can't recall exactly but i get dialogs with end buttons etc and prompted "this will stop the debugger", eventually something works but it is very scientific and I would like to understand better. My keyboard does not have a 'break', key!

[John_G]

One thing you can (and should) do is implement proper error handling procedures in your VBA functions. Doing that gives you complete control (well almost, anyway) over what happens when MS Access generates a run-time error. Most importantly, if done right, it prevents MS Access from going into debug mode and stopping the query. In your case, since the functions are being used to return values in a query, you might want the error handler to return a distinct "error" value. Your functions would look something like this, in skeleton form:

Function MyFunction(...parameter list...)
Dim...
On Error GoTo ErrorProcedure
...
... Function code
...
MyFunction = normal return value
Exit Function
ErrorProcedure:
...
... Error processing code
...
MyFunction = Error value
End Function

I would not recommend putting a MsgBox in the error handling code, especially if the errors are frequent, but using debug.print to record information about the bad data values would help.

John

[stumped]

Thanks John, very helpful.