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 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.
| Value | Constant | Description |
| 0 | AcDetail | Form detail section or report detail section |
| 1 | AcHeader | Form or report header section |
| 2 | AcFooter | Form or report footer section |
| 3 | AcPageHeader | Form or report page header section |
| 4 | AcPageFooter | Form or report page footer section |
| 5 | AcGroupLevel1Header | Group-level 1 header section (reports only) |
| 6 | AcGroupLevel1Footer | Group-level 1 footer section (reports only) |
| 7 | AcGroupLevel2Header | Group-level 2 header section (reports only) |
| 8 | AcGroupLevel2Footer | Group-level 2 footer section (reports only) |
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.
