Macro limited to run only 20 times. How to solve this.

[bvdhaegen]

Hello Everybody,

I got some errors running my access macro. When I run it I get a message saying that I can only run it for a maximum of 20 times. But this macro needs to run many more times since it needs to filter out date.
Any idea to solve this problem?
Thanks a lot, Bernard

 

[namliam]

Use VBA instead

 

[Mike375]

Hello Everybody,

I got some errors running my access macro. When I run it I get a message saying that I can only run it for a maximum of 20 times. But this macro needs to run many more times since it needs to filter out date.
Any idea to solve this problem?
Thanks a lot, Bernard

What is the macro doing. Is the macro's running time based on a number in Repeat Count or are you using a condition to determine how many times the macro runs

Mike

 

[Rich]

Tell us what your code is doing, somebody sensible will give you the code alternative

 

[bvdhaegen]

Thanks a lot for your help and time.
It is a condition that stops the macro from runing. In fact it has to sort out information, and copy some into a new table.
As attached file I send the file. If one of you has time, it would be very nice to have a look at. It is a very small program. The macro I'm talking about is M-Control.
Thanks, Bernard

 

[bvdhaegen]

Here is the file.

 

[bvdhaegen]

Hope this time it will be attached.

 

[Mike375]

Hope this time it will be attached.

I can't import from that file and probably because I have Access 95 and the problem is componed by it being on Windows XP.

I have never seen the type of message you refer to and that could again be due to the earlier version of Access. I have a RunMacro action and there is a problem then messages come up with Halt etc and the problem.

Without seeing it....the first thing I might try is to replace the conditional macro stop expressionwith a Repeat Count, say 30 times and see what happens. But basically, if the macro is stopping after 20 runs then it will be ither because the conditional statement has stopped it or circumstances exist at the 20th run where the macro can't run. If that is the case then a Repeat Count set at 30 will also stop at 20. But if that happens I would expect Halt type messages to be coming up.

A simple example might be a macro that does GoToControl then Copy and then GoToControl (another field) and then Paste and then GoTo Next record and repeat. However, if record number 20 has a blank field then the macro will stop and a message will come up along the lines of Copy not available.

Mike

 

[bvdhaegen]

Dear Mike,

Please find here as attached file the macro converted in VBA.
Maybe you can have a look at it.
Thanks, Bernard

 

[Mike375]

Dear Mike,

Please find here as attached file the macro converted in VBA.
Maybe you can have a look at it.
Thanks, Bernard

Bernard

They are complicated when you look at someone elses

It looks like RunCommand is the modern counterpart of the DoMenuItem.

You have a fair bit of SelectRecord in there. What is this macro doing. Each time it runs is it moving to another record.

Is the macro current running 20 times and then stopping? If so then it suggests your macro is OK but it is running into a problem due to data etc. Have you tried running it with a Repeat Cound instead of using conditions to see how it runs.

By the way, if you print a macro out you can then scan. If you do them from Tools>Analyse>Documentor they come out perfect. What version of Access do you have.

Mike

 

[bvdhaegen]

Macro Problem

Dear Mike,

The program is doing the follow.
I have to accountancies which overlaps eachother. I want to control that everything written in one of the accounts is also taken in the other accounts.
So first the macro copies files from the two tables into one table (also filtering some things out).
Than it sorts the date and control if two lines are the same (from first and from second accountancy) if the lines are the same it goes to the next line. If the lines are the same, it means that it was taken in accountancy 1 and acountancy 2, so it is OK.
If the lines are not the same, it copies this line into a new table.
At the end, I have a table with all lines that are only in one of the accounts.
So that's what the program is doing.
I can not put a limited number of times to run the macro since I have no idea how many lines there are in the different accounts.
I send as attached file the program again ,with enough lines in it to see the problem (more than 20 lines).
Thanks for your help.
I'm running access 2000
OK, Can not send the program The file is 200 K and this forum limits uploads to 100 K

 

[Mike375]

Bernard,

When you said "When I run it I get a message saying that I can only run it for a maximum of 20 times" I was assuming you were using a RunMacro action.

What is the actual message you get or is it a case that the macro stops and brings up a message box you have made.

Have you tried having Echo set for Yes and running the macro on Single Step. Do you know what part of the macro is stopping. If not you might try placing a Stop Macro action at different points in the macro.

I have a very large macro that is similar to yours in principle in that it selects records from a table and transfers etc. so I know what you mean about the number of lines. However, you might try in a copy of your data base to alter the macro so that it does not have to know how many lines.

Has the macro worked in the past or is it a new macro you have made.

Mike

 

[bvdhaegen]

Macro

Dear Mike,

Yesterday I tried everyting to try to find out the problem. I can not find a solution.
The macro is new and never worked. In fact I already had this problem before but I could never solve it.
Please find as attached file a scan of the message I get from Access. Mayb it will tel you something more.
Thanks a lot for your efforts.
Regads, Bernard

 

[Mike375]

Bernard,

Try replacing this:

DoCmd.RunMacro "M-Control.Copy", , ""

with the actual macro details.

In other words go to the macro M-Control.Copy and copy the entire macro and then paste it into wherever you have the RunMacro acrion for M-Control.Cop.

When you paste it in then turn the line black where the action is Runmacro and it will all be replaced by the action line of M-Control.Copy

Also, is woukld appear that the macro itself is called M-Control.Copy and that is problem with calling itself.

So another way is to make another simple macro that just has a Runmacro action and the Macro is M-Control.Copy.

Mike

 

[WayneRyan]

bvd,

You're getting this message because your macro is calling itself. You have
a process of resolving itself it calls itself. Most likely you are performing a
series of actions (run query, run form, run another query). Then it calls
itself. Since none of these calls is resolved (by a RETURN) statement,
they just keep stacking up. Access won't let you stack up more than 20.

The interesting thing is that your process is probably iterative, it doesn't
need to "call itself", it needs to loop back and repeat itself. You can't do
that.

As mentioned before by other folks, you really should convert your
Macros to code because:

1) This would probably have an easier solution.

2) You will have much better error-handling

3) You will have a lot more tools to control your processing ... Macros
don't have Case statements, inline Ifs, etc.

Tools --> Macro --> Convert to VBA

Just my two-cents worth.

Wayne

 

[Mike375]

Bernard,

I just thought that making another macro to run M-Control.Copy will not work because it will still be calling itself.

So if don't want to past in the contents of M-Control.Copy to replace Runmacro you might make a few copies of M-Control.Copy with different names and then use them in the RunMacro part.

Mike

 

[bvdhaegen]

Macro

Thanks.
I'm trying all this out now.
I'll come back to you later.
Bernard

 

[Rich]

You need to get away from this Macro mindset. Very few posters here use them, other than newbies and insurance salesmen.
I suspect most of what you're doing can be achieved with queries, find unmatched and append. Have you tried any of them?

 

[bvdhaegen]

Macro Problem

What I want to do in fact is very simple.
I have a table with different lines.
Some lines are exactly the same (pairs of lines). I don't need them.
Some other lines are not duplicated in the table. This means they only appear one time in the table. I need those lines. The duplicated lines can just be deleted.
It is so simple, but I can not write something like that. I made huge programs with access (some time ago), but I'm stuck for this very simple job.
Also I don't know how I should do that with queries.
If you have any idea, it would be welcome.
Thanks

 

[Mike375]

What I want to do in fact is very simple.
I have a table with different lines.
Some lines are exactly the same (pairs of lines). I don't need them.
Some other lines are not duplicated in the table. This means they only appear one time in the table. I need those lines. The duplicated lines can just be deleted.
It is so simple, but I can not write something like that. I made huge programs with access (some time ago), but I'm stuck for this very simple job.
Also I don't know how I should do that with queries.
If you have any idea, it would be welcome.
Thanks

Did you try replacing the RunMacro for M-Control.Copy with the contents of the macro replacing Runmacro or making some copies of M-Control.Copy and use the different names for the RunMacro action.

Perhaps you could write out what you are basically trying to do. However, if your macro will run all the way through then will that get the job done?

For duplicate records you make a FindDuplicate query from the Access wizard.

The other thing that you can do to knock out duplicates is to make a copy of your table and then delete all the records from the table or alternatively make a copy where you only copy the structure of the table. To do this you just right click on the table name and a menu will come up which will show you what to do once you click copy.

If you now make an append query to append all your table's records to the copy of your table AND set one of the fields in the copy of the table so that it will not accept duplicate records. You will see how to do that at the bottom of the Table/Design page when you click on the field name you wish to set up so as to not accept duplicates.

Mike