Macro cannot run more than 20 times

[RYOUAPHI]

I have seen other people out there with this issue and the answer generally is why do you even need that many macros. I think my situation is different so allow me to explain:

I am in the IT dept for a car manufacturer. We have 65 dealers associated with us. Each week I pull an excel report, manipuate the data and it is linked to an access data base.

In access, I have 65 queries(1 for each dealer) and 65 reports(generated from those queries.

I have 65 macros. Each does a sendobject to a particular email address and attaches that dealers reporting numbers. Each macro runs the next macro until all are completed. Unfortunately after 20 I get the error that a macro cannot call itself more than 20 times. There are 65 different macros. Even by placing them in a Macro Group doesn't resolve it. Does anyone have he answer and/or can anyone tell me if using the repeat expression and count will resolve and what I out in repeat expression.


Adam

 

[namliam]

Do it in VBA...

 

[The_Doc_Man]

More specifically, write VBA code to run your macros. (Yes, there is a way to launch a macro from VBA.)

Then make ONE macro step at the top macro level that does a RunCode of your VBA code. Note that you should make the code a FUNCTION because for some silly reason, some versions of Access only want to run functions. But it would be OK if it always returned 0 or 1 or some convenient constant value. As long as you don't check the returned value, it doesn't really matter what it is.

So you would have a macro running code running macros. But the CODE segment doesn't have the limitation of 20 layers.

Now, here is why you had the problem. You could ALSO have done this by buidling ONE "master" macro that sequentially runs the other macros one at a time. But instead, each macro activates the next macro in turn, sort of a daisy chain of macros.

When you have Macro A run Macro B, and Macro B runs Macro C, and.... what really happens is NESTING of macros. Parent-child contexts. And to prevent macro loops, Access disallows nesting beyond the level you discovered - 20 layers.

Remember, when A runs B, A can't exit until B exits. Then, the next step is that B can't exit until C exits. Which means that A has to wait for C as well as B. And so on...

When that gets 20 layers deep, you have 19 macros above you holding their breaths and waiting to exhale. This is not recursion per se because the individual macros are different in your case, but it IS possible for a macro to trigger itself, at least in theory. That WOULD be recursion. And to prevent runaway recursion loops, Access (and any other thing that permits recursion) needs a way to stop a runaway recursion.

 

[RYOUAPHI]

I'm clear but still unsure

What you said makes perfect sense as to what the issue may be. However, what I am unclear of is how I get 65 different reports emailed to 65 different email addresses with one click of a button without using VBA. I am familiar with VBA, but surely there must be a way to do it without programming for those who don't program. As fun as t is to program, programming is not always the answer

 

[stopher]

I have 65 macros. Each does a sendobject to a particular email address and attaches that dealers reporting numbers. Each macro runs the next macro until all are completed. Unfortunately after 20 I get the error that a macro cannot call itself more than 20 times. There are 65 different macros. Even by placing them in a Macro Group doesn't resolve it. Does anyone have he answer and/or can anyone tell me if using the repeat expression and count will resolve and what I out in repeat expression.

Maybe I'm being a bit thick here as I don't often use macros. But why can't you just create 65 sendobject actions in one macro?

That aside, I assume the report you are sending is basically the same layout for all recipients (just the data and titles change). In which case I'd have a table storing the email address and parameters required for each corresponding recipient. Then you only need one query and one report and you get VBA to cycle through the table to send the report to each recipient according to the parameters. This makes it very easy to maintain and the code will be much less than the 65 lines of macro.

hth
Chris

 

[RYOUAPHI]

Good idea but....

I like that idea, but a couple of quetions from there. I have a one table with all the data. I understand how the report will simply have whatever data the query has, but how do you create one query that requeries 65 times. Is that where the VB comes in. Also, I'm not familar with that code or the code that will send the email. Can you give me a small generic example?

 

[stopher]

Email report to multiple recipients using VBA

I've attached an example (for Outlook)

Take a look at the form frmEmail. The code behind the button basically loops through the records in tblRecipients using recordsets. Then for each record it re-creates a query then mails the report that is based on this query to the current recipient.

You can eloborate on this quite a lot e.g. including more info in your query to include in your report, more criteria in your form, variable messages in your email body etc

Note that there is no error checking whatsoever!

hth
Chris

 

[stopher]

Just one tip. I have some automated emails where as well as sending the report as an attachment, I also include a summary in the message body e.g

Hi Jim,
Your total sales this month are £3500

This is great for our sales people when they are using their blackberrys.

You can use Dlookup to grab info like this very easily again using the parameters from your form and table.

Chris

 

[RYOUAPHI]

Thanks and 2 ?'s

I'll try that coding and thanks.

1. What does the coding "(0) (0)" do after setting db = dbEngine
2.In the While Not loop you say while not bof or eof. Technically you movefirst when entering the loop making it BOF, wouldn't that cut the first record out?

 

[stopher]

Sorry, I just quickly grabbed some old code (which would most likely have originated from some online code) and changed it where necessary. Was doing it quick as I'm off on hols tomorrow

1. What does the coding "(0) (0)" do after setting db = dbEngine

Explanation here. So recommend to use CurrentDb although my book uses the old way (must be an old book)

2.In the While Not loop you say while not bof or eof. Technically you movefirst when entering the loop making it BOF, wouldn't that cut the first record out?

I Agree and it would throw an error if it was empty. Better to use
DO WHILE not rs.EOF
...
rs.MoveNext
LOOP

Chris