This month, Doug Steele looks at how to have larger checkboxes and how to deal with code that gets unlinked from the control to which it’s supposed to be linked.

This Part 2 of Access Answers for Mar 2004

Sometimes the events associated with controls on my form get “unlinked” from the controls. For example, I’ll have a button named cmdProcess and a routine in my code Private Sub cmdProcess_OnClick(), but clicking on the button doesn’t invoke the code. What can I do?

I find this happens most often when you use cut-and-paste to move controls (for example, to move from one tab in a form to another tab on the same form), although I’ve seen it occur under other scenarios as well. While I’m not sure what causes it, or how to prevent it, it’s not that difficult to write some VBA code that checks to see when this situation has occurred. Once you’ve found the problem, you can fix any such occurrences before you ship your code.

There are three different types of objects that can have event-related code associated with a form:

• The form itself
• The various sections of the form (detail, form header/footer, and page header/footer)
• The controls on the form

It’s necessary to check all of these to do a thorough job. By the way, everything I’m talking about here also applies to reports. To keep things simple, though, I’m only going to talk about forms. Hopefully you’ll be able to figure out how to modify the code to work with reports. If not, drop me a line!

To see what I’m talking about, open a form in Design mode and look at the Properties window. Select the Event tab. Everything on that tab (with the exception of the Key Preview and Timer Interval properties) represents an event that can possibly have a code module associated with it (see Figure 1).

Figure 1

The standard procedure associated with an event will have a name consisting of a prefix, followed by an underscore, followed by the event name, such as Form_Current or chkActive_Click.

In our case, the prefix will be the word Form, the name of a section, or the name of a control. Sections and controls both have a property EventProcPrefix that can be used to determine the appropriate prefix, but forms (and reports, for that matter) don’t have such a property. In fact, trying to refer to their EventProcPrefix property will raise error 2465. I use this fact to create a simple function that, passed an object such as a form, form section, or form control, will return the prefix to use with that object:

If you extend the code I’m providing to work with reports, you’ll need to modify GetProcPrefix to return Report when appropriate.

Getting the procedure suffix is a bit more complicated, since the name of the module isn’t exactly the same as the name of the property. For example, the form’s On Current property (which is named OnCurrent) will have a module named Form_Current associated with it. The “rules” for converting events to property names are quite simple, though: The word On is removed from the Event name, and any spaces trimmed from the result. Just to eliminate any problems, though, I store the input and output values in a table named EventProcedures, with two columns called EventName and EventProcedureSuffix. I use a DLookup function to return the suffix for a given Event Name (or Null if Event Name isn’t a recognized value):

To find unattached code, I open all of the forms in the application one by one, and examine all of the properties in each form. The following module finds those forms and then calls another module to actually do the lookups for each form. If CheckAllForms runs into a problem, it returns the value True (the error details will be found Results parameter). I’ll discuss what counts as a problem later in this column.

As I suggested earlier, the CheckSingleForm function opens the form in design mode (unless it’s already opened):

At this point, frmCurr has been instantiated as a form object representing the form being examined, and mdlCurr has been instantiated as a module object representing the module associated with the form. I’m ready now to look at all of the properties of the form itself to see which of them represent events that can have procedures associated with them.

Properties can be either strings or numeric, but all event properties are strings (though not all string properties are events). For each property that’s a string, I look the property name up in my EventProcedures table. If the property name is in my EventProcedures table, that means there could be a procedure associated with that property.

To see whether the property actually has an event procedure, I set the name of the procedure to “Form_” plus the suffix returned from the table (for instance, Form_Current, Form_Load, and so on), and then determine whether a procedure of that name exists in the module pointed to by mdlCurr.

If the procedure does exist in the module, I check the value associated with the property. With this information, I can now determine whether there is or isn’t a problem:

• If the property is set to “[Event Procedure]” and the procedure exists, then everything is okay.
• If the property is set to “[Event Procedure]” and no procedure exists, I have an error condition.
• If the property is set to something other than “[Event Procedure]” and the procedure exists, I have a non-fatal error condition. The procedure is redundant, and should be deleted, but the application should run without error.
• If the property is set to something other than “[Event Procedure]” (including blank) and no procedure exists, then everything should be correct. This occurs when the property is set to the name of a macro, or a VBA function. Realistically, you should check that the macro or function really exists to be completely sure.

The following function will check all of the event properties associated with a specific object, and will return a string containing the details of any problems (it returns Null if there are no problems):

The preceding snippet has a “helper” function called HasProcCode that I’ll describe now before continuing with the code. Given a reference to the code module associated with the form and the name of a procedure, this function returns True if the procedure exists in the module, or False if it doesn’t. It does this using the Module object’s Find function:

I also use this routine to check both the events associated with the various sections of the form and the events associated with each control on the form. Unfortunately, while there’s a Controls collection associated with the form, there isn’t a Sections collection that I can use. Fortunately, there are only a fixed number of Sections on a form (listed in Table 3), though on most forms only a few of the sections exist.

Table 3. Form and Report section definitions.

Since I’m only dealing with Forms and ignoring Reports, I only need to worry about sections 0 through 4. My procedure is to try to instantiate each section and then trap the error that occurs if the section doesn’t exist. If no error occurs, meaning that the section exists, I then check the properties associated with the section. The name of a procedure for a section starts with the section’s name, but any blanks in the name must be removed first:

This code will only identify the problems in your application–it’s your responsibility to fix them. You may want to extend the code so that it actually corrects the errors (although you may need to prompt for what to do when a procedure exists and the event is associated with VBA code or a macro).

Thanks to fellow Access MVP Henry Habermacher, Phuket, Thailand, for the original idea and most of the code on which this example was based.