How to force macro to end based on certain conditions?

[jlm att]

We have a Macro that runs many queries and we want to force it to abort based on the values we find in the database. Any ideas on the best method for doing this?

More details: The database is being populated by another system so we want to make sure that their update finished. If we set up a table with a status value in it then we can just read that table and see if their update finished properly. If so then the macro can run all of the rest of the quries in it. If the status show us the update did not finish then we want our macro to skip to the end or just stop.

[Stewart Ross]

Hi. You'd need to convert your macro's steps to the equivalent VBA code to do this, as you cannot perform the checks needed at each step from within a macro.

In VBA code you have full control of the way you check the status value and can prevent the other steps from executing relatively straightforward ly. A skeleton of what I mean is listed below:

Public Function fRunUpdates() as Boolean
  'Returns TRUE if all actions run to completion
  'FALSE if not
  DoCmd.SetWarnings False
  DoCmd.OpenQuery "qryYourUpdate1"
  if YourStatusValue = 0 then goto exit_function
  DoCmd.OpenQuery "qryYourUpdate2"
  if YourStatusValue = 0 then goto exit_function
  DoCmd.OpenQuery "qryYourUpdate3"
  if YourStatusValue = 0 then goto exit_function
  DoCmd.OpenQuery "qryYourUpdate4"
  if YourStatusValue = 0 then goto exit_function
  ...
  DoCmd.OpenQuery "qryYourUpdate9"
  if YourStatusValue = 0 then goto exit_function
  DoCmd.SetWarnings True
  fRunUpdates = True
  Exit Function
exit_function:
  Docmd.SetWarnings True
  fRunUpdates = False
End Function

All this does is to test the status value after each action query is run. If the status value is not as expected then the function is exited cleanly returning False as its output value to indicate that it did not run to completion.

I do not know what your status value is or how you would access it, so I've put a dummy test in the skeleton. You'd have to work out how to test the value as appropriate for yourself.

-Stewart

[jlm att]

Thanks Stewart,
I'll probabaly convert this to VBA at a later date. For now I've found an quick interim solution to controlling macros in access 2007 via the condition column in the macro design page. I put this in the condition column:
[Reports]![StatusRpt]![Status Indicator]="Started"
Which reads a value from a table as defined by the report. If it = Started or if it = Done I can trigger the Macro to do different things. Not as robust as VBA but it'll do for now.... Thanks for responding, John