"Allen Browne" wrote
Better to use a local variable if possible.
Not only are local variables safer and less
likely to be affected by other procedures,
but they are also easier to debug. You
probably realise that all globals are lost
whenever you "reset" during development?
I strongly agree with Allen on this issue.
And, in production, they are lost when you encounter an "unhandled error".
In one assignment, correcting and enhancing a database done by someone else,
the client was having difficulties because of "cascading errors" due to the
hundreds of globals used by the original implementer. That is because the
only error handling in the entire database was that added by my colleague
and I in our initial "urgent fixes". They did not desire to (again) pay for
a complete redesign and reimplementation, so what I did was add error
handling to something over 500 VBA procedures -- fortunately, I was able to
do much of the work programmatically, so it wasn't quite as bad, or as
expensive for them, as it would have been had I had to manually add error
handling in every procedure!
Globals are OK, used "judiciously and sparingly" (emphasis should be on the
"sparingly".)
Generally, I try to open a connection to the database for as short a time as
possible, to avoid corruption if the power goes out, or a network connection
drops. That is Open the Database, Open the Recordset, do the necessary
process, Close the Recordset, and Close the Database. When you take this
approach, Procedure-level variables are just what you need.
Larry Linson
Microsoft Access MVP
