Macro Cannot Run Itself More Then 20 Times

[Pap]

I have a recurring macro that I need to run more than 20
times. At the moment I am getting the above bug
message. Any ideas how to get around this?

Thanks,

Paul

 

[Steve Schapel]

Paul,

Are you using a RunMacro action? Is this in a separate macro to the
other actions you are trying to repeat? Is this in a separate macro
altogether, or within the same macro group? Maybe it would be good if
you could give full details of the setup you have with this macro.

 

[Guest]

Sorry, I am still learning!!

The macro is executed from a RunMacro action within the
code of the form that runs when the form is loaded. The
macro undertakes certain tasks then closes down the
form. It will re-load the form until all the tasks have
been completed. This means the form needs to re-open at
least 50 times, running the macro every time it opens.

I hope this helps and that you can help me!

Paul

 

[Steve Schapel]

Paul,

I may be able to help, but I will need some more specific and explicit
detail before I can know what is going on. Can you please give a full
listing of the macro that is being run via the RunMacro action, also
what event the RunMacro action is triggered by (is it the On Load event
of the form?), and full details of where the RunMacro action is located.
Thanks.

 

[Pap]

Steve,

I seemed to have fixed the problem through coding all the
commands into the on-load of the form (see below).

Private Sub Form_Load()

If Me.Ind = "A" Then
MsgBox "Process Complete. Please Exit Screens."
Else
DoCmd.OpenReport "R_Monitoring", acViewPreview
DoCmd.SendObject acSendReport, "R_Monitoring",
acFormatRTF, [Reports]![R_Monitoring]!
[E_Mail], , , "Monitoring Report", "Test", False, False
DoCmd.Close acReport, "R_Monitoring", acSaveNo
DoCmd.Close acForm, "F_MMSAC2", acSaveNo
DoCmd.OpenQuery "Q_Val+1", acViewNormal, acEdit
DoCmd.OpenForm "F_MMSAC2", acNormal, , ,
acFormEdit, acWindowNormal, False
End If

End Sub

However, it would be useful to know if there is a way
around the "20" limit?

Thanks,

 

[Steve Schapel]

Sorry, Paul, I don't know anything about the limit of 20 repeats for a
RunMacro, and normally no such limit applies, I have no idea why you
were experiencing this.

A few comments on the code... As far as I know, there is no need to
preview the report before emailing it via SendObject method, so probably
the opening and closing of the report is making your code unnecessarily
complicated. The rest of the code I don't understand, but it appears
that Q_Val+1 is a Select Query rather than an action query, is this
right? If so, there would normally be no purpose served in opening a
query datasheet in this way, so once again it is probably a redundant step.