Trapping macro error in VBA

[Robbie]

Hey,

Does anybody know of a way in Access 97 to trap an error thrown by a macro. The macro is been called in vba by docmd.runmacro so I presumed that the error thrown by the macro would be caught by VBA error handling but it is not.

Any ideas?

Ps. I know this is easier in the newest versions of access but unfortunately on 97 is available.

Public Function RunMacro(ByVal strMacName As String, Optional ByVal strLogFile As String = "") As Boolean
'Runs a maco and writes whether the macro completed or not to a log file
'strMacName: The name of the macro to run
'strLogFile: Name of a specific log file to write to. If this is left out the standad log file is used.... <current dir>\Access Macro Logs\<Database Name>\<macro name>\<current date & time(yyyymmdd_hhnn)>.txt
On Error GoTo RunMacro_End
    Dim strTimeStamp As String
    
    strTimeStamp = Format(now(), "yyyymmdd_hhnn")
    
    If Len(strLogFile) = 0 Then
        CreateStandardLogFile strMacName, strTimeStamp, strLogFile
    End If
    
    DoCmd.RunMacro strMacName

RunMacro_End:
    If Err.Number <> 0 Then
        RunMacro = False
        WriteLineToText strLogFile, strTimeStamp & ": ***ERROR***: " & Err.Description
    Else
        RunMacro = True
        WriteLineToText strLogFile, strTimeStamp & ": Completed Normally"
    End If
End Function

 

[vbaInet]

To save yourself the stress, why not convert the macro to code and trap it that way?

 

[Robbie]

To save yourself the stress, why not convert the macro to code and trap it that way?

Well the thing is that we have loads of access macros that are running as scheduled tasks. The whole process was set up by people who are not very experienced with access and has now snowballed a bit. We are not even sure what macros are even needed any more and lots of them are throwing errors and hanging open instead of closing out nicely.

I am relatively new here and I have been given the task of trying to clean this up. The idea I had was that I could wrap each of these macros in this code and trap and log errors for macros that don't run, instead of just having them hanging open if they fail.

I think it would be quicker to do it my way, and I was disappointed to see that the error were not being caught, but if this is not possible I am going to have to convert them all to vba, like you suggested!

 

[vbaInet]

I think it would be quicker to do it my way, and I was disappointed to see that the error were not being caught, but if this is not possible I am going to have to convert them all to vba, like you suggested!

Yea, I don't think you can trap macro errors as they are independent from VBA.

You've got a huge task ahead of you, so good luck

 

[Robbie]

Yea, I don't think you can trap macro errors as they are independent from VBA.

You've got a huge task ahead of you, so good luck

Oh well, thanks anyway! At least I know it can't be done now.

 

[vbaInet]

Let me have another look before you begin.

 

[vbaInet]

Ah, there's an OnError action in a macro. So you can actually trap the error within the macro.

 

[gemma-the-husky]

If only certain macros are failing, i would convert those to code, as and when, and then add some proper error handling. I find it much easier to handle code rather than macros.

i suppose that a single error code for what could be a complex macro is a bit of a blunt instrument. one failing macro could cause a daisy chain of fails.

 

[MSAccessRookie]

Gemma has a good idea here. I once had a project that had some Macros failing, and we converted those Macros to VB Code to handle the issues. Once the Users discovered how much faster (and more secure) the VB Code was, they requested conversion of all of the remaining Macros.

 

[Robbie]

Thanks for all of the replies!

I believe the OnError action is only available in the later versions of access. Unfortunately I am restricted to 97 for now.

I also had the idea of trying to find a way of looping through actions in a macro so I could parse them and then handle each action as appropriate. No luck there either!

It should actually be pretty easy to convert them to VBA because most of them are just a series of calls to queries. Just a lot of repetitive work that I would rather not have to do!

Cheers!

 

[vbaInet]

I also had the idea of trying to find a way of looping through actions in a macro so I could parse them and then handle each action as appropriate. No luck there either!

There's no Macros collection so you can't loop through macros. You would have to scrap that bright idea