Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22
  1. #16
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825

    Send me a Personal Message on the forum and include the url link for the file.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  2. #17
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Left Click June7 in her post and there are some selections.

  3. #18
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    Thanks, I did that a couple of hours ago. I have to assume June's really busy or scratching her head like I've been doing most of the day.

  4. #19
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Testing with Windows 7 / Access 2010 as well as Windows XP / Access 2007. Don't have Windows 7 / Access 2007.

    The PictureID code executes in both combinations but do see different results.

    XP/2007
    The form opens for me with 4 records and after clicking PictureID none show, I click again and I can see only 1 record, click again and only 2 records show. It will now toggle back and forth between the 2 and 1 records.
    Sometimes I get an 'Invalid reference' error from Sub ShiftPB().

    W7/2010
    The form opens for me with 4 records and after clicking PictureID, I see 2 records, click again and the original 4 records show. It consistently toggles back and forth between the 4 and 2 records.

    The PictureID code is changing the RecordSource of subform, switching between parameterized queries. Cannot explain why the results are different with 2007, very bizarre. Think I would use one query and instead of query parameters, set form Filter property. Maybe that will solve issue.

    The main form and subform are not linked (Master/Child link properties are not set) - why even have the main form bound?

    No chance to run the newer db with 2007.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #20
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    June,

    I got the same experience you describe with XP/2007 on Win7/2007. That's the behavior that led to the post in the first place.

    I got the same as you with Win7/2010.

    I will modify the code to use a single query as the RecordSource of the sub-form together with different filters as you suggest and post the results...........that might not happen until later tonight or perhaps even tomorrow.

    I don't follow your comment "why even have the main form bound". The only notion of bound that I'm aware of is that the sub-form container control is bound to the sub-form. Also, since there are no controls bound to table data on the main form, I don't know what kind of Master/Child link properties there would be? Perhaps you might enlighten me about that question?

    I did test the Dirty property of the form in a "grasping for straws" attempt, but that made no difference.

    You've been very helpful and I thank you for the time and effort you expended.

    Bill

  6. #21
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    By 'bound' I mean bound to table or query - RecordSource property. Data controls are 'bound' to fields of RecordSource. The subform is 'linked' to main form.

    The isn't any reason to 'link' the subform to mainform and therefore no reason for the main form to be 'bound' - no RecordSource.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #22
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919

    Resolution of "Works on A2003 & A2010 but fails on A2007

    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)

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 21
    Last Post: 08-29-2013, 11:08 AM
  2. Replies: 0
    Last Post: 07-31-2013, 08:08 AM
  3. Query works but VBA fails
    By dumbledown in forum Queries
    Replies: 11
    Last Post: 03-16-2012, 10:37 AM
  4. A2007 Runtime on same machine as A2003
    By jhollingsh615 in forum Access
    Replies: 4
    Last Post: 05-24-2011, 12:17 PM
  5. Replies: 0
    Last Post: 04-15-2010, 01:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums