Results 1 to 11 of 11
  1. #1
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171

    Hiding Subreports when thre is no data

    I have a main report with 5 subreports. I would only like the subreports with data to appear on the report. Right now I get a set of headers for each report when there is not data and it takes up a lot of space. I have tried using Can Shrink/Can Grow. I have also put this code in and it doesn't seem to work either. Any Suggestions!


    Code:
    Private Sub subreportPerfIssuesIssueKeyP_Enter()
    On Error Resume Next
    If Me.subreportPerfIssuesIssueKeyP.Report.HasData Then
    Me.subreportPerfIssuesIssueKeyP.Visible = True
    Else
    Me.subreportPerfIssuesIssueKeyP = False
    End If
    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The appropriate event for that code is probably the format event for the section containing the subreport control.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    Changed to this and it did not work.
    Code:
    Private Sub subreportPerfIssuesIssueKeyP_Format()

  4. #4
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    Where does this kick off? OnLoad? No Data?

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Look at the events of the section, not the events of the report. In other words, the detail section, report footer, wherever.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    I put the code in the both sections of the subreport. I have also attached a picture of the report screen.

    Code:
    Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    On Error Resume Next
    If Me.subreportPerfIssuesIssueKeyP.Report.HasData Then
    Me.subreportPerfIssuesIssueKeyP.Visible = True
    Else
    Me.subreportPerfIssuesIssueKeyP = False
    End If
    End Sub
    
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    On Error Resume Next
    If Me.subreportPerfIssuesIssueKeyP.Report.HasData Then
    Me.subreportPerfIssuesIssueKeyP.Visible = True
    Else
    Me.subreportPerfIssuesIssueKeyP = False
    End If
    End Sub
    Attached Thumbnails Attached Thumbnails Report.PNG  

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you attach the db here to play with?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    My data base is too big and has proprietary information in it. Any other suggestions on what I can provide?

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You would put code in the Format event of section of the MAIN report that contains the subreport controls.

    Copy the db and remove confidential data. Remove objects not needed for the issue. Leave just enough records to test the issue.
    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.

  10. #10
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    I reduced the size and zipped it and tried to reload it but I am getting an error on your webpage that an error has occurred. Not sure if this is on my end but is very possible!
    This is the code for the Detail section of the Main Report. Upon further reading people were saying it was the labels that needed hiding to I added that as well and it is still not working. Appreciate any help! I posted a picture of my report in a previous post.
    Code:
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
     
     Me.Description_Label.Visible = Me.subreportPerfIssuesIssueKeyP.Report.HasData
     Me.ContractorIssues_Label.Visible = Me.subreportPerfIssuesIssueKeyP.Report.HasData
     Me.ContractorOnset.Visible = Me.subreportPerfIssuesIssueKeyP.Report.HasData
     Me.ContractorResolved_Label.Visible = Me.subreportPerfIssuesIssueKeyP.Report.HasData
     Me.ContractorComments_Label.Visible = Me.subreportPerfIssuesIssueKeyP.Report.HasData
     
     If subreportPerfIssuesIssueKeyP.HasData Then
     subreportPerfIssuesIssueKeyP.Visible = True
     Else
     subreportPerfIssuesIssueKeyP.Visible = False
     End If
     
     If subreportPerfIssuesIssueKeyV.HasData Then
     subreportPerfIssuesIssueKeyV = True
     Else
     subreportPerfIssuesIssueKeyV.Visible = False
     End If
     If subreportPerfIssuesIssueKeyH.HasData Then
     subreportPerfIssuesIssueKeyH.Visible = True
     Else
     subreportPerfIssuesIssueKeyH.Visible = False
     End If
     If subreportPerfIssuesIssueKeyC.HasData Then
     subreportPerfIssuesIssueKeyC.Visible = True
     Else
     subreportPerfIssuesIssueKeyC.Visible = False
     End If
    
     If subreportPerfIssuesIssueKeyT.HasData Then
     subreportPerfIssuesIssueKeyT.Visible = True
     Else
     subreportPerfIssuesIssueKeyT.Visible = False
     End If
    End Sub

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    If you have the code behind the main report then you are not correctly referencing the controls of the subreport.

    The Me qualifier is just referencing the main report and since the main report does not have those label controls, of course that code would not work.

    I very much doubt that setting any control (label or otherwise) to not visible will reduce space used because controls are still there.

    I just did a quick test in my db. Without data the subreport shrinks to nothing. With data it grows and pushes other controls down. I did not use any code in this test.

    However, I had to remove all calculated textboxes. UNBOUND textbox with expression causes a single record to display.

    If you can't figure out how to attach file to post or send to pbaldy, can upload to a fileshare site such as Box.com and post link to 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.

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

Similar Threads

  1. Replies: 3
    Last Post: 01-27-2017, 03:58 PM
  2. Hiding Duplicate Data in a Query
    By swb1 in forum Queries
    Replies: 12
    Last Post: 06-22-2014, 03:55 PM
  3. Subreports - Different
    By frousseau in forum Reports
    Replies: 1
    Last Post: 09-07-2010, 05:14 PM
  4. Using Subreports
    By jonesy29847 in forum Access
    Replies: 5
    Last Post: 04-20-2010, 11:16 AM
  5. Hiding subreports
    By aouellette in forum Reports
    Replies: 0
    Last Post: 09-12-2008, 08:02 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