Results 1 to 10 of 10
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919

    VBA statements pertaining to group footer not working

    I have a report, sub-report actually, that I want to tailor a bit when the group only has one record. Below is a screenshot of the sub-report's design view.



    Click image for larger version. 

Name:	004.jpg 
Views:	13 
Size:	111.5 KB 
ID:	28312

    When the ControlSource function of a text box located in the group footer's section runs, the intent is to make the two text boxes in the footer non-visible and reduce the "white-space" of the blank footer's height to half its normal height. If the group's record count is greater than one then revert to a normal configuration.

    When I run the code in debug, the setting of the height to 195 is seemingly ignored, as evidenced by inspection. Also, on the first exercise of the code, only the tbCatLabel is made un-visible.

    Any explanation of this behavior?

    Code:
    Public Function GetCatLabel(ID As Variant) As String
    
    If Me.TBCount > 1 Then
        Me.GroupFooter1.Height = 390
        Me.tbCatLabel.Visible = True
        Me.tbCatTotal.Visible = True
        
        If Len(ID & "") = 0 Then
            GetCatLabel = "Total Other: "
        Else
            GetCatLabel = "Total " & GetCatName(ID) & " : "
        End If
    Else
        Me.GroupFooter1.Height = 195
        Me.tbCatLabel.Visible = False
        Me.tbCatTotal.Visible = False
    End If
    
    End Function

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not sure about this: set the textboxes to very thin, with a small footer, with Can Grow set to Yes. You would have to remove the control source and set it in the code.

    Another way would be to have those fields on the main report instead.

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I abandoned the approach of messing with heights, as it seemed much simpler to have a thin empty header and toggle visibility of the headers and footers. The code below runs as expected in debug and visibility properties seemingly set accordingly, but none of the properties set are reflected in the display. It's as though the code didn't run at all.

    (ControlSource of tbCount is "=Count(*)"; ControlSource of text box in group footer is "=GetCatLabel([CatID])" )

    Code:
    Option Compare Database
    Option Explicit
    Dim bolNextGrpHdr As Boolean      'We'll toggle visibility of the header on when previous footer made invisible.
    
    < Snip >
    
    Public Function GetCatLabel(ID As Variant) As String
    
    If Me.tbCount > 1 Then
        bolNextGrpHdr = False
        Me.GroupFooter1.Visible = True
        If Len(ID & "") = 0 Then
            GetCatLabel = "Total Other: "
        Else
            GetCatLabel = "Total " & GetCatName(ID) & " : "
        End If
        
    Else
        Me.GroupFooter1.Visible = False
        If bolNextGrpHdr Then _
            Me.GroupHeader0.Visible = True
        bolNextGrpHdr = True
    End If
    
    End Function

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    One reason the footer's height doesn't change is that the textboxes are preventing it from getting smaller. Just because you make something invisible doesn't change the size of them, you would have to remove them completely. That is why I suggested making them very small, without a control source, and only putting data into them in the code. This may not solve the problem but it is worth trying.

  5. #5
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    It seems that either I'm expecting too much of Access 2013 or there's something that I'm missing. If I run the sub-report by itself, the visibility of the group header displays as set but the footer does not. If I run the same sub-report subordinate to the main report then none of the visibility properties set take effect.

    Not sure where I can go from here.

  6. #6
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I understand the notion of text box heights effecting the shrink limits of their section container's height. I'm just now seeing there's an OnFormat event for the group headers/footers so I see what you mean about setting height properties and ControlSource in code. I'll play with that shortly.

    (This is why I hate apps with "sophisticated" report formatting requirements........... UGH!)

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Lol, I agree - the more "sophisticated" they make things the harder it is to use.

    Don't use reports much, not sure if I provided much help. Good luck!

  8. #8
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Code ran properly in debug but DID NOT affect display at all.

    Code:
    Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
    
    If Me.tbCount > 1 Then
        Me.GroupFooter1.Height = 360           'Set height to 0.25"    (1/4")
        Me.lblCatLabel.Height = 340            'Set height to 0.236"
        Me.lblCatLabel.Visible = True
        Me.tbCatTotal.Height = 340             'Set height to 0.236"
        Me.tbCatTotal.Visible = True
        
        If Len(CatID & "") = 0 Then
            Me.lblCatLabel.Caption = "Total Other: "
        Else
            Me.tbCatTotal = "Total " & GetCatName([CatID]) & " : "
        End If
        
    Else
        Me.lblCatLabel.Height = 115            'Set height to 0.08"
        Me.lblCatLabel.Visible = False
        Me.tbCatTotal.Height = 115             'Set height to 0.08"
        Me.tbCatTotal.Visible = False
        Me.GroupFooter1.Height = 144           'Set height to 0.1"
    End If
    End Sub

  9. #9
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Finally! The code below works as expected. What turned out to be critical was the requirement that the footer be visible. I.e., one cannot successfully toggle the visibility of the footer, at least not from invisible design to visible in code.
    tbCatTotal remained bound to =Sum function and lblCatLabel set in code.

    I now know more about the use of groups in sub-reports than I ever wanted to know

    Code:
    Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
    
    If Me.tbCount > 1 Then
        Me.GroupFooter1.Height = 360           'Set height to 0.25"    (1/4")
        Me.lblCatLabel.Height = 340            'Set height to 0.236"
        Me.lblCatLabel.Visible = True
        Me.tbCatTotal.Height = 340             'Set height to 0.236"
        Me.tbCatTotal.Visible = True
        
        If Len(CatID & "") = 0 Then
            Me.lblCatLabel.Caption = "Total Other: "
        Else
            Me.lblCatLabel.Caption = "Total " & GetCatName([CatID]) & " : "
        End If
        
    Else
        Me.lblCatLabel.Height = 86            'Set height to 0.06"
        Me.lblCatLabel.Visible = False
        Me.tbCatTotal.Height = 86             'Set height to 0.06"
        Me.tbCatTotal.Visible = False
        Me.GroupFooter1.Height = 100          'Set height to 0.07"
    End If
    
    End Sub

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Congratulations! And thanks for posting the solution. Now we know who to call on for our problems, the expert!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Totalling group footer fields
    By Carbontrader in forum Reports
    Replies: 4
    Last Post: 02-23-2014, 10:36 PM
  2. Replies: 2
    Last Post: 12-21-2013, 02:09 PM
  3. Replies: 2
    Last Post: 06-21-2012, 07:40 PM
  4. Calculation in a group footer.
    By stupesek in forum Reports
    Replies: 11
    Last Post: 09-29-2010, 07:30 AM
  5. Totaling a value in group footer...
    By hodgy20 in forum Reports
    Replies: 0
    Last Post: 11-14-2008, 08:28 AM

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