However, what seems to happen is that every time I get an error message (which happens a lot when I program
) the variable goes out of scope ...
That is a common misconception among developers. Errors do not cause variables to be reset or go out of scope. This happens when you click the End button in the dialog. In fact, by default, VBA warns that will happen at that point, not before.
Instead, you can correct or bypass the error and press the Go button.
- In a similar vein but more concerning to me: any good programmer has proper error handling routines in all (or almost all) blocks of code that properly issue an error message and exit the Sub rather than resulting in a runtime error.
Another common myth. Error handling is vastly overused by some developers, so much so that it becomes hard to see the code among the error handling.
On encountering an error in a procedure without handling, the error is passed upwards through the chain of calls until it encounters an error handler. Only when no handler is encountered anywhere in the chain will the program Break.
Consequently it is often adequate or even desirable to just put error handling in the top procedures of the major execution pathways. In some cases it will provide less precise detail about exactly where the problem occurred but usually enough to point in the right direction.
However, where a common procedure is called from multiple execution paths, having error handling on the common procedure can be a disadvantage because the error handler message can't indicate where the call to it had originally come from. Letting the error pass back up to the calling procedure will show where the problem actually started.
Traditional error handling in functions called from queries is generally a really bad idea. The error is displayed and the function exits, only to be called again for the next record in what looks to the user like an endless fault loop. Far better to return an out of scope value from the function or simply "Error" to be displayed in the query results.
I am not sure, but I would speculate that if one of these errors is tripped, that the DB declaration would go out of scope and the user won't be able to use much of the program without restarting.
No. See first comment above.
As I said, I'm probably missing something.
Yes.