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

    Suppressing portions of a report

    I have an app that creates a report essentially comprised of three sections. The user has the ability via a simple yes/no setting in the apps properties settings to suppress the third section. In doing so, the typical report only requires a single page, whereas if all three sections are included the report flows to a second page.

    In the OnActivate event of the report the code looks up the setting that stipulates the inclusion or exclusion of the 3rd section. When the 3rd section is to be excluded, the OnActivate code calls the Sub "SuppressResv" which, as you can see, toggles the visibility of the controls for the 3rd section.

    So what's the problem you ask? The report still un-necessarily produces a second page. While I'm not surprised that the "white space" is still preserved for the invisible controls, the question becomes how to suppress the controls and effectively release the space they would otherwise occupy if visible. I could have two versions of the report but that kind of duplication is normally ill advised.

    Are there any methods other than the "SuppressResv" to accomplish what I'm asking? Maybe put the controls in a sub-report and either include or exclude the sub-report?



    Code:
    Private Sub SuppressResv()
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    ' Display of reserved accounts are to be suppressed.  Just hide all the
    ' controls.
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    Me.PB2.Visible = False
    Me.Ln2.Visible = False
    Me.lblRA1.Visible = False
    Me.Child29.Visible = False
    Me.lblRA2.Visible = False
    Me.tbRAB30.Visible = False
    Me.lblBottomLine.Visible = False
    Me.tbB3.Visible = False
    Me.lbl28.Visible = False
    Me.tbRAB3.Visible = False
    Me.Line26.Visible = False
    Me.tbTotal3.Visible = False
    
    End Sub

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    perhaps use the can grow/shrink properties?

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    As applied to each of the controls OR the Detail section of the report?

  4. #4
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    While the Detail section does have the "Can Shrink" property, it did not solve my issue to simply set that property to "Yes". However, if I set the "Can Shrink" property of each of the text box controls and the sub-report container "Child29", the amount of data shrunk enough that the report was contained on one page. Now, the "Can Shrink" property does not apply to the label controls "lblxxxx" so they continue to require vertical space even though invisible. In this VERY SPECIFIC instance the labels aggregate amount is about 1-3/8 inches of vertical space. It just happens to be the nature of this app that it is more-or-less likely that that 1-3/8 inches will get pushed to a second page. So, while I very much dislike having two versions of essentially the same report, I will solve this issue by creating a second version without the 3rd section and let the user's settings determine which is used.

    Thanks Ajax for the thought,
    Bill

  5. #5
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    I see that this is already marked as solved but no real solution.

    Just in case anyone reads this I will offer a solution that is very simple and requires very little code. I have been using this trick for over 15 years.

    TRICK: Use a group footer!

    Place all the controls you want to be able to turn on and off in the group footer.

    To hide them simple set the group footer section's visible property to No.

    I like to use the group footer section's On Format event to for the VBA code to turn on or off the sections visible property. . This way it will work with data that has multiple groups.


    TIP: You can group on the detail record's primary key so every record has a group footer.

    TIP 2: You can actually create multiple group footer sections on the same field. That will allow you to selectively turn on and off more than one section of data.

  6. #6
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I'll revisit this if you're telling me that the group footer has the "Can Shrink" property and in fact frees up the vertical white space when set to invisible?
    Bill

  7. #7
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Quote Originally Posted by GraeagleBill View Post
    I'll revisit this if you're telling me that the group footer has the "Can Shrink" property and in fact frees up the vertical white space when set to invisible?
    Bill
    Bill,

    All sections except for page header and page footer have can grow and can shrink.

    My trick do not depend on the Can Shrink.

    When you hide controls, the section is still there. Thus the blank space.

    By setting the section's .Visible = False it makes it disappear. There is NO blank space to be seen because it is basically gone. You do not even have to worry about hiding individual controls within the section. When you hide the section, everything in the section is automatically is hidden.

  8. #8
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Place all the controls you want to be able to turn on and off in the group footer.
    I can find no Access 2013 HELP to cut/paste a set of controls from the body of a report to a group. Also, I'm not seeing anywhere how to select a group footer as the section into which controls can be placed. As you can see in the screenshot below, I have selected several controls. I want to put those controls in a group footer as you suggested. Can you give me a hint or two as to how I can accomplish that task?

    Thanks,
    Bill

    Click image for larger version. 

Name:	001.jpg 
Views:	20 
Size:	189.1 KB 
ID:	27630

  9. #9
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    First you must create a grouping . I don't see any group created in the screen shot above.

    In the screen shot where it says Group On, you need to select something. If I want a group on the detail record, I select the primary key field.

    Once you have a grouping create you can copy-and-paste, drag, or use the arrow keys to move the controls to the group section.

    See:

    Create a grouped or summary report



    Scroll down towards the end to the section:
    Add grouping, sorting, and totals by using the Group, Sort, and Total pane

  10. #10
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    There's nothing to group on. I.e., the report does not have a RecordSource. There are three sections within the Detail section of the report, each having a sub-report bound to appropriate queries. The whole point is to accommodate situations where the user wants to suppress the third of the three sections I've mentioned. I suppose I could bind the report to one of my tables with no references to any of its fields? Does it make any sense to do that?

  11. #11
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Quote Originally Posted by GraeagleBill View Post
    There's nothing to group on. I.e., the report does not have a RecordSource. There are three sections within the Detail section of the report, each having a sub-report bound to appropriate queries. The whole point is to accommodate situations where the user wants to suppress the third of the three sections I've mentioned. I suppose I could bind the report to one of my tables with no references to any of its fields? Does it make any sense to do that?
    From the screen shpt it look likes a lot of unbound controls.

    FWIW, It is rare that I have an unbound report or more hat a few unbound controls. I will write all the data to a temp table(s). I base the report on the temp table(s). This allows me to take advantage of the Access Report Writer's features that only work with bound reports.

    I do create dashboard reports where the main/parent is just a placeholder for sub reports that are bound. I have a dummy/temp table with one record and one field as the primary key. I use this field as a hidden bound control just for grouping.

  12. #12
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I do create dashboard reports where the main/parent is just a placeholder for sub reports that are bound.
    That's what I have. Each of the three sections I've already mentioned sub-form containers bound to sub-reports. All the unbound controls you see could be made bound using your technique of a temporary table but I didn't bother to save all the calculated values.

  13. #13
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Quote Originally Posted by GraeagleBill View Post
    ... but I didn't bother to save all the calculated values.
    Me too. I agree tht is best. That is why I calculate the totals to a temp table to make the reporting easier. The temp tables are emptied and repopulated as needed.

    FWIW: I show the same data from the temp tables on a form first. That is where they can print the report.

  14. #14
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Well you've certainly aroused my curiosity, so as time allows I'll bind the report innocuously to one of my tables and create a group followed by attempts to get the third section of my report in the footer of the group. If I can get that to work, I'll look at storing the calculated values in a temporary table and bind the controls. I'm not a big fan of unbound controls, so that effort is really warranted.

    I'll leave this thread "unsolved" until I can create time to work on the revised report.

    Thanks,
    Bill

  15. #15
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Bill,

    Thanks for the update.

    Please let us know how it goes.

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

Similar Threads

  1. pulling portions of data
    By mike02 in forum Queries
    Replies: 2
    Last Post: 08-02-2012, 02:22 PM
  2. Suppressing records in a report
    By snowboarder234 in forum Reports
    Replies: 1
    Last Post: 07-11-2012, 07:52 PM
  3. Suppressing Access messages
    By Reaper in forum Forms
    Replies: 1
    Last Post: 11-06-2011, 04:02 PM
  4. Suppressing a msgbox
    By alphabetsoup in forum Forms
    Replies: 3
    Last Post: 10-04-2011, 10:30 AM
  5. Suppressing #Name? Error
    By Jess_Wundring in forum Programming
    Replies: 2
    Last Post: 04-15-2010, 12:43 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