Hi Chuck,
Apparently one of the processes has not completed before another
takes over control.
I don't think this is true because, to the best of my knowledge, Access is
not a multi-threaded application. If you convert your macro to VBA code, you
can sprinkle in some DoEvents statements, which "Yields execution so that the
operating system can process other events" (copied directly from the Access
VBA Help).
Or there is some caviat about macros which I missed in my readings.
In Access 2003 and all prior versions, one cannot trap for errors and handle
them gracefully when using macros. Any errors that occur will cause a really
ugly macro error dialog to be presented to your users. The newest version of
Access, Access 2007 allows for error trapping, so macros are more appropriate
to consider using for Access applications created with Access 2007. However,
that said, macros in 2007 do not even begin to compare with the power
available when using VBA code.
<Begin Quote>
"Macros offer the next level down, extending the functionality of the GUI.
Macros are still limited, however, and do not provide anything like the
enormous flexibility of a programming language. Both the macro and the
programming languages take some effort to learn and, surprisingly, often
require relatively different skills; in other words, a good working knowledge
of macros may not make it much easier to convert to using the programming
language. Perhaps even more surprisingly, I do not believe that programming
is fundamentally more difficult to learn. Macros are easier to use but not by
orders of magnitude."
"If you are new to RDBMSs, I suggest (with as much deference as possible)
that you may well not be in a position to judge whether you need macros or
programming. In that case, my advice is clear. Unless you are sure that your
needs really are simple, don't bother learning to use macros. Once you find
that you need more than the GUI offers, go straight to the programming
language. In this way you avoid the pain of climbing one learning curve only
to discover that the view from the top is unsatisfactory and another climb
awaits you."
</End Quote>
From: "Inside Relational Databases, 2nd Edition, by Mark Whitehorn and Bill
Marklyn, published by Springer, p 151
From your other posts:
My joy was short lived! Tampering with the content of the message boxes
broke it again!..... So thinking that the problem was the same just the work
around was wrong, Looking at the macro code again I noticed that the last
query was opened but not closed.
You shouldn't need to close a query opened via a macro. In VBA code, if you
use the Set statement to set a variable to a QueryDef, then yes, you should
set the variable to Nothing as a part of cleaning up. But you shouldn't need
to close anything opened via a macro.
BTW: I do not know how to convert the macro subroutine into Visual basic,
can you point me to a "how to"?
Point your browser to here:
http://www.seattleaccess.org/downloads.htm
and then look for this download:
DAO - Back To Basics Compilation/Demo by Tom Wickerath, Jan/Feb 2007 --
Download (448 kb) App and Word Doc
The .zip file includes a Word document named "How to Convert Macros to
Visual Basic for Applications Code".
Also is there a code checker other than the one you referenced, which by
the way did point out some things which I fixed to no avail.
Sure. There's Total Access Analyzer, which I consider an excellent product.
http://www.fmsinc.com/products/analyzer/index.html
Access is better than a vidio game, as each time you succeed at one level
the next one which pops up is more complex. But the reward for winning is
keeping your job.
<Smile>
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
