Results 1 to 13 of 13
  1. #1
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596

    Unusual Sub Totals & Totals in Groups.

    Hi,



    I have a report that is grouped by Branch and then by three Account Types within the Branch.

    The first two Account Types have multiple 'Sub Account Types' - so it makes sense to sub total them and then Total the first two Account Types.
    I have these first two Account Types as part of yet another sub group in the report so that I get an overall Total for them.

    There will only, however, be one row of data for the 3rd Account Type - 'Repurchase Agreements' - so I'm being asked to supress the sub total and total for that Account Type and I haven't been able to figure out how to do this.

    I'm attaching a screenshot.
    You'll see that there is only one row of data for 'Repurchase Agreements' - but I am getting a subtotal and a 'grand' total for it as well [outllined in red] - but the values just repeat themselves. Those are the two I want to prevent appearing in the report.

    Any ideas on how I can supress the subtotal and total from appearing for Repurchasing?
    I'd appreciate any suggestions.

    Robeen
    Attached Thumbnails Attached Thumbnails MonthlyDeposits.JPG  

  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
    I assume those are in group footers? Untested, but I would think you could test the count in the format event of the footer, and make it not visible when appropriate. You may need a hidden textbox to track the count in the detail section.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Thanks, Paul.

    Yes - those totals are in group footers.
    Access 2010 has an 'On Format' event - but it appears not to fire when I run the report [by clicking the View -> Report View]!
    After trying a few different things in there, I just put a message box in there to see if it would display. No dice.

    Then . . . I just got this comment by Allen Browne off a pc preview thread [regarding Access 2007 - but I guess it applies to 2010 too]:
    A2007 does not fire these events in the new Report view.
    The should fire in Preview or Normal views.
    http://www.pcreview.co.uk/forums/acc...-t3186017.html

    When I ran the report in Print Preview - the messagebox showed up - so at least now I know how to get into the vba . . .

    How do I use this information to get to where I need, though?

    I want the report to be exported to a pdf during an automated run from AutoExec.
    How would I get the report to export with the Repurchase Agreement group footer hidden?
    I'm thinking something like a docmd - export - AcNormal . . . ? Something like that?

    . . . still trying to wrap my brain around this and I'd appreciate your help.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Correct, the format events don't fire in Report view. Have you tried with the code there and exporting to PDF?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Hi Paul,

    I've been running the report in Print Preview - just to see what my code does and here's where I'm at right now:

    I've put the code in this subroutine into three different events - with the same results:

    Code:
    Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
    If Me.Section("GroupHeader3").Controls("Transaction_Group") = "Repurchase Agreement" Then
        Me.Section("GroupFooter1").Visible = False
    End If
    End Sub
    This:
    Me.Section("GroupFooter1").Visible = False
    removes the subtotal from everywhere - not just from the Repurchase Agreement sub group.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You need an Else clause that makes it visible for other groups.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Actually . . . while you were replying, I DID try that.
    Code:
    Private Sub GroupFooter3_Format(Cancel As Integer, FormatCount As Integer)
    If Me.Section("GroupHeader3").Controls("Transaction_Group") = "Repurchase Agreement" Then
    Me.Section("GroupFooter3").Visible = False
    Else
    Me.Section("GroupFooter3").Visible = True
    End If
    End Sub
    . . . same result - it makes that control invisible throughout the report - regardless of the Transaction_Group.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Let's make sure it's returning what you expect. Add

    debug.print Me.Section("GroupHeader3").Controls("Transaction_G roup")

    and see what prints out to the VBA Immediate window. I'd expect to just have:

    If Me.Transaction_Group = "Repurchase Agreement" Then

    Edit: that space is not in what I posted, it's added by the forum software.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I cannot get anything from Debug.Print.
    I put a messagebox in instead just so I can see what it returns.

    I also tried:
    If Me.Transaction_Group = "Repurchase Agreement" Then
    It does the same thing as
    If Me.Section("GroupHeader3").Controls("Transaction_G roup") = "Repurchase Agreement" Then
    Whichever way I go, the controls that I make not visible - are not visible for all sub groups. They simply don't show on the report.

    Code:
    Private Sub GroupFooter3_Format(Cancel As Integer, FormatCount As Integer)
    If Me.Section("GroupHeader3").Controls("Transaction_Group") = "Repurchase Agreement" Then
    'If Me.Transaction_Group = "Repurchase Agreement" Then           'This works too.
       Debug.Print Me.Section("GroupHeader3").Controls("Transaction_Group")   'This doesn't show anything in the immediate window.
    MsgBox "Tran Group = " & Me.Transaction_Group      'Shows Repurchase Agreement.
    Me.Section("GroupFooter1").Visible = False         'Totally hides the control from all sections.
    '    Me.Section("GroupFooter3").Visible = False    'Totally hides the control from all sections.
    Else
        Me.Section("GroupFooter3").Visible = True
    End If
    End Sub

  10. #10
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Hi Paul,

    I was able to get a suggestion from here:
    http://msgroups.net/microsoft.public...nly-one-record
    and it has given me what I need.
    I don't know if I'm allowed to quote the text of that thread - so I'll give an outline here.

    Create an unbound control in the detail section & name it CountGroup.
    Set Properties:
    Control Source =1
    RunningSum - Over Group.

    Then:
    Code:
    Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
    Cancel = [CountGroup] = 1
    End Sub
    Since the 'Repurchase Agreement' sub group is always a single row of data - the 'Cancel = [CountGroup] = 1' will prevent it being displayed in GroupFooter1.

    Thanks for helping!

    Robeen
    Last edited by Robeen; 03-19-2012 at 03:33 PM. Reason: Clarification.

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you found a solution. I did test my method, and it worked as expected. You would have wanted the Debug line outside the If/Then test. Where you have it, it would only run if the test was True. We already knew the expression was never evaluating to True, so no surprise it didn't print anything out.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Hi Paul,

    I think I wasn't clear enough in the one post where I had this line in my code:
    Code:
    MsgBox "Tran Group = " & Me.Transaction_Group 'Shows Repurchase Agreement.
    The expression WAS evaluating to True - and the msgbox was showing 'Repurchase Agreement'.
    The problem was that when the code executed this command:
    Code:
    Me.Section("GroupFooter1").Visible = False         'Totally hides the control from all sections.
    The text box in question was invisible for ALL sub groups - and not just for the Repurchase Agreement sub group. So I was not seeing any totals.
    . . . [now that I look more closely at that . . . is my code saying 'hide the section named GroupFooter1'?
    I wasn't really paying attention - and I thought I was saying 'hide the control that is in GroupFooter1' . . .

    The solution I found worked in the current situation because there is ALWAYS only one row of data for the Repurchase Agreements.
    However, I'd like to find a way to make this happen the way we were trying [using the sub group value (Repurchase Agreement) to determine whether the subtotals & totals appear on the Report].

    When you said:
    I did test my method, and it worked as expected
    . . . what exactly worked as expected?
    Were you able to make subtotals NOT appear for a certain section based on the group header value for that section?

    ALSO - I just realized that the way I finally got it to work was what you suggested to begin with but I didn't understand what you meant when you said in your first response:
    but I would think you could test the count in the format event of the footer, and make it not visible when appropriate. You may need a hidden textbox to track the count in the detail section.
    . . . so Thanks for that!!

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Both If statements worked correctly:

    Code:
    Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
      'If Me.txtCount < 2 Then  this worked fine
      If Me.txtShift = 2 Then 'this also works fine
        Me.GroupFooter0.Visible = False
      Else
        Me.GroupFooter0.Visible = True
      End If
    End Sub
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Totals not available
    By Hubler in forum Queries
    Replies: 2
    Last Post: 12-20-2011, 04:03 PM
  2. Replies: 5
    Last Post: 12-06-2011, 11:18 AM
  3. Totals Again
    By marksnwv in forum Reports
    Replies: 2
    Last Post: 08-05-2011, 12:17 PM
  4. Month totals and Year totals
    By marksnwv in forum Access
    Replies: 1
    Last Post: 08-05-2011, 10:13 AM
  5. Import a totals value
    By vCallNSPF in forum Access
    Replies: 5
    Last Post: 12-13-2009, 07:01 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