Doug Steele          Gold Collection 

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:

Private Function GetProcPrefix( _

  ObjectToCheck As Object) As String

On Error GoTo Err_GetProcPrefix

  GetProcPrefix = ObjectToCheck.EventProcPrefix

End_GetProcPrefix:

  Exit Function

Err_GetProcPrefix:

  If Err.Number = 2465 Then

    GetProcPrefix = "Form"

  Else

    Err.Raise Err.Number, "GetProcPrefix", _

      Err.Description

  End If

  Resume End_GetProcPrefix

End Function

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):

varProcSuffix = DLookup("EventProcedureSuffix", _

    "EventProcedures", _

    "EventName = '" & prpCurr.Name & "'")

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 in the Results parameter). I'll discuss what counts as a problem later in this column.

Function CheckAllForms( _

  Results As String) As Boolean

Dim dbCurr As Database

Dim docCurr As Document

Dim strTemp As String

  Results = vbNullString

  Set dbCurr = CurrentDb()

  For Each docCurr In _

    dbCurr.Containers("Forms").Documents

    strTemp = CheckSingleForm(docCurr.Name)

    If Len(strTemp > 0) Then

      Results = Results & _

        "Form " & docCurr.Name & _

        vbCrLf & vbCrLf & strTemp

    End If

  Next

  Set dbCurr = Nothing

  CheckAllForms = (Len(Results) = 0)

  Exit Function

End Function

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

Public Function CheckSingleForm( _

    FormName As String) As String

Dim frmCurr As Access.Form

Dim mdlCurr As Access.Module

Dim ctlCurr As Access.Control

Dim sctCurr As Access.Section

Dim prpCurr As DAO.Property

Dim lngSection As Long

Dim strResults As String

If SysCmd(acSysCmdGetObjectState, acForm, FormName) _

     > 0 Then

    booFormAlreadyOpen = True

  Else

    booFormAlreadyOpen = False

    DoCmd.OpenForm FormName, acDesign, , , _

      acFormReadOnly, acHidden

  End If

  Set frmCurr = Forms(FormName)

  If frmCurr.HasModule Then

    Set mdlCurr = frmCurr.Module

  Else

    Set mdlCurr = Nothing

  End If

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):

Private Function CheckProperties( _

    ObjectToCheck As Object, _

    ModuleToCheck As Access.Module) As String

Dim prpCurr As DAO.Property

Dim strProcName As String

Dim strProcPrefix As String

Dim strResults As String

Dim varProcSuffix As Variant

 strResults = Null

 strProcPrefix = GetProcPrefix(ObjectToCheck)

 For Each prpCurr In ObjectToCheck.Properties

    If prpCurr.Type = vbString Then

     varProcSuffix = DLookup("EventProcedureSuffix", _

               "EventProcedures", _

               "EventName = '" & prpCurr.Name & "'")

   If Not IsNull(varProcSuffix) Then

    strProcName = strProcPrefix & "_" & _

                      varProcSuffix

    If HasProcCode(ModuleToCheck, strProcName) Then

     Select Case Trim(prpCurr.Value)

      Case "[Event Procedure]"

       Case vbNullString

        strResults = strResults & _

         "Event procedure '" & strProcName & _

         "' exists, " & "but associated " & _

         "property is not set for " & _

          prpCurr.Name & "." & vbCrLf

       Case Else

        strResults = strResults & _

         "Event procedure '" & strProcName & _

         "' exists, " & _

         "but associated property is set to '" & _

         Trim(prpCurr.Value) & "' for" & _

         prpCurr.Name & "." & vbCrLf

     End Select

    Else

     Select Case Trim(prpCurr.Value)

      Case "[Event Procedure]"

        strResults = strResults & _

        "Property for " & prpCurr.Name & _

        " set to [Event Procedure], but code for " & _

        "'" & strProcName & "' not found." & vbCrLf

      Case Else

     End Select

    End If

   End If

  End If

 Next prpCurr

 CheckProperties = strResults

End Function

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:

Function HasProcCode( _

  ModuleToCheck As Module, _

  ProcNameToSearchFor As String) As Boolean

  If ModuleToCheck Is Nothing Then

    HasProcCode = False

  Else

    HasProcCode = ModuleToCheck.Find( _

      "Sub " & ProcNameToSearchFor & "(", _

                     0, 0, Empty, Empty)

  End If

End Function

The CheckProperties function is used to check for the events associated with the form with this line of code:

strResults = strResults & _

           CheckProperties(frmCurr, mdlCurr)

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:

For lngSection = 0 To 4

  On Error Resume Next

  Set sctCurr = frmCurr.Section(lngSection)

  If Err.Number > 0 Then

    Err.Clear

  Else

    On Error GoTo Err_CheckSingleForm

    strResults = strResults & _

          CheckProperties(sctCurr, mdlCurr)

  End If

Next lngSection

Finally, I do the same thing for each of the controls on the form:

For Each ctlCurr In frmCurr.Controls

  strResults = strResults & _

        CheckProperties(ctlCurr, mdlCurr)

Next ctlCurr

All I need to do now is close the form that I opened when I started this routine, and I'm done:

If booFormAlreadyOpen = False Then

  DoCmd.Close acForm, FormName, acSaveNo

End If

Set frmCurr = Nothing

Set mdlCurr = Nothing

CheckSingleForm = varResults

Exit Function

End Function

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.

 

Your download file is called 403steele.ZIP in the file SA2004-03down.zip

This is found in the Gold Collection at http://www.vb123.com/smart/