Module EOJ Rtn codes 1

[Trudye]

Is there anyway to check the successful completion of a module using a macro?

If you run a module from a macro and other modules depend on its success how do you abort processing if the primary module is not successfull? On large mainframes you can pass return codes from one JCL step to the next, can you do something like that with macros?

Thanks
Trudye

 

[andylec]

In VBA use a function instead of a sub.

Function MyFunction(parameters) as Boolean
On Error Goto ErrorHandler
.
Do the work
.
If it all went OK then
MyFunction = True
Else
MyFunction = False
End If
Exit Function

ErrorHandler:
MyFunction = False

End Function

(Apologies for the pseudo code!)

You can then do something like:
If MyFunction then
Do your other code
Else
msgbox "Ooops. Something went wrong"
End If

rgds
Andy

 

[Trudye]

Andy:

Where do I put;
If MyFunction then
Do your other code
Else
msgbox "Ooops. Something went wrong"
End If

in my macro? I'm running 2 queries, 2 modules and opening a report from my macro. Is there some code I can put in my macro to exit the macro if one of the steps fails? I know that you can have conditional statements in macros. But I do not know how to send return codes from the module to the macro so I can use it to test the condition.

I hope that made sense, I tend to rattle on.


Thanx
Trudye

 

[cmmrfrds]

Macro's can have conditions. When you are in macro design then go to the menubar and insert condition. In the condition column you can execute a function. The function will return a value which you can check to conditionally execute your steps.

 

[andylec]

cmmrfrds, you are a saint. I was just typing all this in when you posted. Your method is far easier. For the record (so we can all laugh about it later):

Alt F11 and Create a new module. Put functions in there that run your queries, other code modules and the report from there. I assume that, ultimately, you only want to show the report if all the preceding steps work OK, so it would work something like this

Option Explicit

Private Function RunQuery(strQuery as string) as Boolean

'Run the query here. Either use code to open an existing
'query or execute an SQL statement.

'Check for success
'If OK then RunQuery = True
'Else RunQuery = False

End Function

Public Function ShowReport() as Boolean
'Run the first query
If Not RunQuery(query1) Then 'You could use an SQL
'statement here
ShowReport = False
Exit Function
End If
'Run the second query
If Not RunQuery(query2) Then
ShowReport = False
Exit Function
End If
'Now call the two modules and check for success
.
.
.
'If OK then
' Open the report
' ShowReport = True
'Else
ShowReport = False
'End If

End Function

All you now need to do is call ShowReport (from a button/macro/other code)

cmmrfrds's method is easier though

rgds
Andy

 

[cmmrfrds]

Here is an example if you haven't done this before. Notice the threee ... combine the actions with the above condition. This is a partial list of conditions in the autoexec macro of an access program that can be run from the commandline or GUI.

Condition Action Comment
(CheckCommandLine()="850IN&quot Echo
… Hourglass
… RunMacro
… RunCode
(CheckCommandLine()="855OUT&quot Echo
… Hourglass
… RunMacro
Not (CheckCommandLine()="Batch&quotMsgBox


Function CheckCommandLine() As String
' Check value returned by Command function from the command line.
' ----- Command is the value from the command line ---------
If (Command = "850IN&quot Then
'-- Return "850IN"
CheckCommandLine = "850IN"
End If
If (Command = "855OUT&quot Then
'-- Return "855OUT"
CheckCommandLine = "855OUT"
End If
If Not ((Command = "855OUT&quot Or (Command = "850IN&quot) Then
'-- Stop the Marco
CheckCommandLine = "GUI Look"
End If
End Function

 

[cmmrfrds]

It is hard to line up columns here, I will try again. There should be nothing under comment. Echo and Msgbox are actions.

Condition Action (CheckCommandLine()="850IN&quot Echo
… Hourglass
… RunMacro
… RunCode
(CheckCommandLine()="855OUT&quot Echo
… Hourglass
… RunMacro
Not (CheckCommandLine()="Batch&quot MsgBox