Hi Wayne.
This is just personal opinion…
A lot of people ask for the best way to do something but there is no best way without knowing precisely what that something is.
In general:-
1. Errors should be fixed not handled. That may sound rather trite but it is quite correct. By the time it becomes necessary to show an error message to the end user the game is over.
By error message I mean the internal Access error message not what action the program might take in order to keep running. When it becomes necessary to show the end user the internal Access error message the programmer is basically saying “Sorry about that chief; I stuffed up!”
Now because it’s a programmer stuff up, if it wasn’t then the programmer would have fixed it, the error becomes unknown. It then becomes impossible for the code to correct for an unknown error.
If we add into the equation that the most important thing in a database is the data then there is little choice to be made. Reporting an internal Access error to the end user means it’s an unknown error. An unknown error can’t be corrected. The data is the most important thing. The data must be protected at all cost. No stuffing around with trying to save the error to a table; for all we know the network might be down. No stuffing around with trying to e-mail an error; the e-mail server may be down. Be careful even trying to display the error; it might be a reference problem.
The error is unknown. The application must cease operation under an unknown condition. The application must close to protect the data. The game over.
2. Errors should be handled by the caller not the called. It would be nice to think that the called procedure could protect itself but, in general in Access, it can’t. There is a concept, in other languages, that the called procedure should protect itself at all cost. It kind of makes some sense since that the protection is done in one place, it is better to write code in one place than in many places.
But it’s the data types available which make it almost impossible for a VBA called procedure to protect itself. You will see it written many times on the web that we should use the smallest data type available for a particular situation. For example; if you only need a Byte then use a Byte, if you only need an Integer use an Integer, blah, blah, blah. That type of posting on the web should be questioned. It is either being posted by someone from another language, or, it is being posted by someone who is simply reposting something they have read from another person who has worked in another language. The propagation of rubbish is rampant on the web.
VBA is a different bucket of bolts and it’s the Variant data type which makes it different. No other data type can handle the possibility that a passed argument is Null, only a Variant can.
So unless all data types, received by a procedure, are of type Variant then the procedure can not protect itself. If they are not Variants then the failure occurs in the caller. Such a failure will not even get through the pass to the called and therefore the called can not possibly handle it.
Hence, unless all received arguments are of type Variant then the calling procedure must do the protection because the called procedure can’t when passed a Null. And who writes all received arguments as type Variant? (Maybe we should???)
So there is no best way, only a precise definition of what is required under a particular circumstance.
Chris.