This is part two of two-parts on maximizing code reuse. Part one focused on assigning more than one event handlers to same object’s event or sharing an event handler for several objects. We now turn to looking at how we can write design-time automation to help us save time in coding.

If you enjoy lots of typing, stop reading now

SQL Server Hosting

Sometime we get an inherited Access database and we find that there are lot of work we need to perform. For starter, let’s pretend we want to add KeyNavigator from the part one to 100 of continuous forms scattered around in the Access database. Even though we showed you how we can reduce the setup required in each form to only 4 lines of code, that’s still 400 lines of code to be added to each form. That’s lot of typing still.

But why type when you can get VBA to do it for you?

Note that before you can run the code, you must have a reference to “Microsoft Visual Basic for Applications Extensibility 5.3” library. You only need that reference for the duration and should remove it afterward.

Using Module object, we can write a procedure in a scratch module to perform the work for us. We typically have one scratch module added to the project — maybe named “zzScratchPad” so that it sorts to the bottom of the list. We also prefer to make all procedures within the module private, because the intention is not use them at runtime. The only way to run them is to run them from the VBA editor, which is appropriate for our purposes.

Since we want to add the needed 4 lines of code to continuous forms only, we need to set up a loop to transverse the forms in the application. To reduce the errors associated with multiple forms being open (e.g. you can’t open a form in design view when it’s already open as a subform in another form), we will close all objects and work with only one object at a time. Furthermore, because we may not have module for a form, we need to be sure we make one.

Now we have the right form and its module acquired, there are few more considerations. What if a form has a Open event handler already created? We don’t want to duplicate and thus write erroneous code that won’t compile. So we need to examine the module, determine if there are event handler and edit it or add new one as appropriate.

Now we have a means of automatically adding the needed 4 lines to all forms. Because the code also checks for existence of those 4 lines, we can run it again and again so if we added 10 new continuous forms but didn’t add the needed 4 lines, it’s one-click run. This is also useful when you might not have 100 forms in one Access file but maybe do have that many in several different Access files.

Obviously, you could adapt the code to tailor your needs. Building a set of routines to use for taking over an old database and populate it with your libraries and fitting various objects with the needed calling code can be now made simpler. Happy automating!