Well, this caper has really been puzzling in that the code that ran successfully in A2003 and A2010 failed consistently in A2007. Thanks to June7, her suggestion plus another of her observations has led to a successful resolution.
I'll post the revised code below for those that have nothing better to do with their Sunday evening.
The previously offending form has detectable column heading labels located in the main form positioned over the columns displayed in a sub-form. Previously, any detects of the labels when running in A2007 caused a failure in displaying the otherwise appropriate data in the sub-form. Two problems seemed to be at root cause of the failures. One was that the Main form was bound to the same RecordSource as the sub-form and second was that the OnClick processing code for the labels was changing the RecordSource of the sub-form that also affected the RecordSource of the Main form when a Requery was issued from code.
So, what June questioned was way the Main form being bound to the same RecordSource. The reason was simply the way the original code had used a RecordsetClone to check for zero record count. To resolve that issue, I removed the binding of the Main form and changed the code to clone the recordset of the sub-form container.
Another observation June made was that it might be advisable to filter the RecordSource of the sub-form rather than change the underlying query.
I have revised the code extensively to filter the sub-form Recordset and, as indicated above, removed the Main form binding.
Thanks a bunch to June,
Bill, a.k.a. GraeagleBill
Code:
Option Compare Database
Option Explicit
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' The Directory Items utility module assists the user in reconciling all of the elements that
' are involved in the automatic creation of a Directory. That is, entry into the directory
' require pictorial and print specifications in addition to photographs to be found in the
' image library. With there being potentially hundreds of entries, the task of checking for
' completion of what's required would be a rather formidable were it had to be done manually.
'
' The essential elements are displayed in columns from which it is easy to determine if any
' action is required. Most importantly, three of the column labels are detectable whereby
' the user can isolate potential missing elements by having the overall display filtered to
' the element of interest or concern. The TMS Help text explains the functionality to the
' user accordingly.
'
' The filter expressions corresponding to the three columns of interest are the constants
' Dim'd in the module heading below.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim strTemp As String
Dim HighlightedLable As String 'Name of the label control that is currently highlighted (0 length if none)
Const fltrDirNoPics As String = "[FamilyDirectory] = True AND [FamilyImageID] Is Null"
Const fltrDirOrPics As String = "[FamilyDirectory] = True OR [FamilyImageID] Is Not Null"
Const fltrPicsNoDir As String = "[FamilyDirectory] = False AND [FamilyImageID] Is Not Null"
(snip)
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' The three Subs "InDirectoy_Click", "ImageName_Click" and lblImageFound_Click are OnClick
' Event processors corresponding to the three key Directory elements. Anytime the user clicks
' on any one of the column labels, its highlighting is either "turned on" or "turned off"
' depending on its current state. One can in fact jump from one column heading to another and
' the Sub run will attend to the appropriate recognition of highlighting and whatever filter
' changes are required.
'
' Note: "ContFamDirItems" is the name of the container control for the sub-form "FamDirItems".
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Private Sub InDirectory_Click() ' User has clicked the column label "Directory Specified"
Select Case (HighlightedLable)
Case ""
Highlight ("InDirectory")
Me.ContFamDirItems.Form.Filter = fltrDirNoPics
Case "InDirectory"
Un_Highlight ("InDirectory")
Me.ContFamDirItems.Form.Filter = fltrDirOrPics
Case "ImageName"
Un_Highlight ("ImageName")
Highlight ("InDirectory")
Me.ContFamDirItems.Form.Filter = fltrDirNoPics
Case "lblImageFound"
Un_Highlight ("lblImageFound")
Highlight ("InDirectory")
Me.ContFamDirItems.Form.Filter = fltrDirNoPics
End Select
Me.ContFamDirItems.Form.FilterOn = True
Me.ContFamDirItems.Requery
With Me.ContFamDirItems.Form.RecordsetClone
If .RecordCount = 0 Then
Un_Highlight ("InDirectory")
Me.ContFamDirItems.Form.FilterOn = False
Me.ContFamDirItems.Requery
MsgBox "No Directory checks found that are un-accompanied by an image name."
End If
End With
End Sub
Private Sub ImageName_Click() ' User has clicked the column label "Picture ID"
Select Case (HighlightedLable)
Case ""
Highlight ("ImageName")
Me.ContFamDirItems.Form.Filter = fltrPicsNoDir
Case "ImageName"
Un_Highlight ("ImageName")
Me.ContFamDirItems.Form.Filter = fltrDirOrPics
Case "InDirectory"
Un_Highlight ("InDirectory")
Highlight ("ImageName")
Me.ContFamDirItems.Form.Filter = fltrPicsNoDir
Case "lblImageFound"
Un_Highlight ("lblImageFound")
Highlight ("ImageName")
Me.ContFamDirItems.Form.Filter = fltrPicsNoDir
End Select
Me.ContFamDirItems.Form.FilterOn = True
Me.ContFamDirItems.Requery
With Me.ContFamDirItems.Form.RecordsetClone
If .RecordCount = 0 Then
Un_Highlight ("ImageName")
Me.ContFamDirItems.Form.FilterOn = False
Me.ContFamDirItems.Requery
MsgBox "No image names found that are un-accompanied by Directory checks."
End If
End With
End Sub
Private Sub lblImageFound_Click() ' User has clicked the column label "Image Found"
Select Case (HighlightedLable)
Case ""
Highlight ("lblImageFound")
Me.ContFamDirItems.Form.Filter = fltrDirNoPics
Case "lblImageFound"
Un_Highlight ("lblImageFound")
Me.ContFamDirItems.Form.Filter = fltrDirOrPics
Case "InDirectory"
Un_Highlight ("InDirectory")
Highlight ("lblImageFound")
Me.ContFamDirItems.Form.Filter = fltrDirNoPics
Case "ImageName"
Un_Highlight ("ImageName")
Highlight ("lblImageFound")
Me.ContFamDirItems.Form.Filter = fltrDirNoPics
End Select
Me.ContFamDirItems.Form.FilterOn = True
Me.ContFamDirItems.Requery
With Me.ContFamDirItems.Form.RecordsetClone
If .RecordCount = 0 Then
Un_Highlight ("lblImageFound")
Me.ContFamDirItems.Form.FilterOn = False
Me.ContFamDirItems.Requery
MsgBox "No Directory checks found that are un-accompanied by an image name."
End If
End With
End Sub
Public Function Highlight(strLBL As String)
HighlightedLable = strLBL
Me(strLBL).ForeColor = 16711680
Me(strLBL).FontSize = 14
Me(strLBL).Top = Me(strLBL).Top - 30
End Function
Public Function Un_Highlight(strLBL As String)
Me(strLBL).ForeColor = 13209
Me(strLBL).FontSize = 12
Me(strLBL).Top = Me(strLBL).Top + 30
HighlightedLable = ""
End Function
(snip)