Navigation:  Bronze Collection > Jun-1998 > Template Your Error Handling

Ashish Nanda          Bronze Collection   
 
You know you should put more error handling in your code. Here's a tool that will let you add your error handling code in less time than you're spending now.
 
Are you getting tired of typing or pasting the same error handling code in your Access functions? I know I am. Generating the same code over and over again was becoming so tedious that I found myself wondering if I really needed error handling in the function I was working on.
 
I do know that a function template wizard is available from Microsoft for creating functions with standard error handling code. The problem is that I'm such a fervent believer in error handling that I use a central error handler in all my code. As a result, I can't use wizards that insist doing things their own way and won't let me modify the code to meet my needs. The new fashion for delivering Access utilities as compiled Access MDE is particularly annoying because, if the utility doesn't quite do what you want, there's no way to get at the code and fix it.
 
There was nothing for it but to create my own error handling code builder. If I kept on wondering whether my functions needed error handling, it was only matter of time before I'd decide they didn't -- and that was too dangerous a scenario to entertain. Besides, I was itching to start an Access Library database of my common VBA functions. Until this point I'd been copying common functions from one project to the next and was starting to incur some higher maintenance costs because of that practice. My error-handling template would be the first addition to my VBA library.
 
Anatomy of error-handling code
To create a template builder, you must begin with a template. Most subroutines and functions with error handling follow this template:
 

 
 
So my Error Handling Template Builder builds exactly this generic format both for new and existing functions/subroutines. About the only thing that changes from one routine to another is the label used as the target of the On Error GoTo. Later, you could come back and enhance the template to provide a more sophisticated routine. Or, you can modify the template to get the error-handling code that you want.
 
Using the Template Builder
When adding a coding tool to Access, you have a number of different choices. [See the sidebar, "Wizards, Builders, Add-ins, and Library Databases," and the upcoming series from Helen Feddema -- Ed.] The method most commonly used by programmers is the Menu Add-in. An Add-in has the benefit of being easy for the user to install -- all he or she needs to do is go to the Add-in choice on the Tools menu.
 
I, however, wanted to invoke my Add-in from the Visual Basic toolbar. This is quicker than going through the Tools menu to get to the Add-ins list. With the builder on the VBA toolbar, you just click once to start the builder. If you want to use my builder, you'll have to customize the toolbar yourself, but my feeling was that it shouldn't present much of a challenge to a Smart Access reader.
 
For maximum flexibility, I decided to have the builder copy the template code to the clipboard rather than insert it into the modules. For one thing, adding code to the middle of a module is a pain, as VBA only provides support for adding text to the end of a module. Once the builder puts the code on the clipboard, you can paste it wherever you want.
 
While I intended to start with a simple version of the builder and then enhance it later, I couldn't stop myself from adding the ability to generate the headers that I use in documenting VBA code to my template.
 
I wanted to make the user interface for my builder as easy as possible. I decided the easiest method was to have the user copy the text to be used as the label for the error handler and then click on the builder button to start the utility. The builder would then present the user with the dialog box shown in Figure 1.

Figure 1
 
Once the Template is displayed, the user presses Ctrl-V to paste the label into the template. Clicking the appropriate button on the Error Template dialog box generates the error handling code for subroutines or functions and moves the code to the clipboard. The code in the clipboard can then be pasted at the required location in the program using Ctrl-V again.
 
How do I generate the Error Template? I use a set of VBA functions organized in three layers:
 
1. The top layer generates the required code as a text string using the information gathered in the dialog box.
 
2. The next layer combines some Win 32 API functions to append data to the clipboard (or empty it, as the case requires).
 
3. The bottom layer consists of another set of Win 32 API functions. These include some more clipboard functions from the library User32 DLL and some global memory functions in Kernel32 DLL.
 
If you're interested in the inner workings of these functions, feel free to browse the routines in the database available in the accompanying Download file.
 
Adding the builder to the project
The normal way to invoke a library database is to add a reference to the library to your database and then call the required functions from your code. In Access 2.0, which supported global modules, referencing a library was simply a matter of adding a line to the Libraries section of the Microsoft Access initialization file (typically, MSACC20.INI).
 
In Access 97, there are a number of different ways to reference library databases. You can use special keys in the Registry, for instance. However, these are a little obscure and tend to change with each new version of Access.
 
I've found that explicit references added using the Tools/References menu work fine for me. If an attached file is missing because someone moved the database, Access will automatically check in a number of places to try to fix the problem. Access looks in these locations, in this order:

•Directory of the database

•Directory in which Access is installed

•Windows directory

•Windows system directory

•Any directory in the Current Path

 
To prevent problems when a file that was created on my computer is opened on a user's computer, I put my add-ins in the same directory on my production and support computers. That way, the path to the database doesn't change as I move the application from the test system to the production system. Still, every new installation does require me or someone like me to sit down at the new computer and install the new software. I don't have to do it this way. Access 97 also allows me to programmatically create and delete references via the new Reference objects, and that will be one of the future enhancements to this builder.
 
No references
If you prefer to avoid references, you can use the Run method of the Application object to execute my routines from another module or form. There's a restriction: Your library must have an MDA extension and be located in the same folder as the built-in Microsoft Access wizards. If you decide to go this route, follow these steps:

•Copy ETemplate.mda to your Access directory

•Customize the Visual Basic toolbar in Access to add an ETemplate button

•Set the "On Action:" property of the ETemplate button to "=Etemplate()"

 
All you need to do then is add the following function to an Access MDA database, preferably your library of common Access functions:
 

 
 
With that done, you'll be able to invoke the Template Builder whenever the Access Visual Basic toolbar is visible.
 
Conclusion
The Error Handling Template Builder is a good place to start a simple library, and I'm sure it will save you a lot of typing. The next stage for me is to add a central error handler to the library to go with the Template Builder. Look for me to come back with that enhancement in a future article.
 
Download ETEMPL.ZIP on this page
 
Ashish Nanda is a Director of Calibre Computer Company, which specializes in custom software solutions. Ash@calibre.demon.co.uk.
 
 
Sidebar: Wizards, Builders, Add-ins, and Library Databases
A number of different terms are used to describe tools that developers can use to extend the native Access environment. Although different terms are used to describe these extensions, fundamentally they're all the same -- VBA code loaded as a library.
 
Wizards
Wizards handle complex operations and generally have a comprehensive user interface with a number of forms and dialog boxes to walk the user through creating an object.
 
Builders
Builders are similar to wizards, but they handle simple operations and have a simpler interface that generally consists of a single form or dialog box.
 
Menu add-ins
While wizards and builders are context-sensitive, menu add-ins are general tools that are independent of the current operation. Menu add-ins are available through the add-ins submenu of the Tools menu and can be invoked whenever the Tools menu is visible.
 
Library databases
These are typically a set of shared functions and database objects that perform common functions and can be called from any Access database.