Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    120

    Unanswered: Suppress Totals in Footer Based on Group

    Hi Everyone,

    Is it possible to Supress a Group Footer based on the Group being printed?

    I posted previously regarding Sub Totals on an 11 row report but as of now got no responses.

    I have a report that prints 11 rows. I need a sub total of Rows 5,6,7 only and another Sub Total of rows 1 thru 10, then a Grand total at the very end.

    I have a Footer with totals in it now, but they shows for Every Row, The rows 1,2,3,4,8,9,10,11 look messy because it just repeats the same information that is above it.

    Is there a way to Suppress the individual Fields or the Footer entirely based on the Group Level???

    I've attached a PDF of what the report looks like now and an Word File of what I'd like it to be.

    Any assistance would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    much like a form theres an awful lot going behind the scenes in an Access report, you can attach code to that report using various event hooks/triggers.

    what I suspect you need to do is implement your business logic in the relevant events

    ferinstance you coudl determine that you need a sub toital when the number of rows in the group was more than one
    so you need a mechanism to count the number of rows
    have a look at the reports events. I suspect you need to look at the detail eventsm and the footer events
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    120
    Thanks healdem. How would I do this? I know what you mean by Events (ie. On Print; On Format etc).

    Your logic sounds as if it would work for my sub total for Depts 5 thru 7 as that group contains more then 1 line. What about the Sub Total for Deptartments 1 thru 10? How would I get that one?

    I don't know all these tricks am self Taught and I stumble through these things, have learned a lot though over time and I ask questions here a lot but some of the answers go right over my head as I don't know SQL or anything language wise, I do all i Queries and Macros.
    Last edited by CHI Brian; 03-23-11 at 12:18.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Some VBA code will help here, and it's not that hard.

    In this example I have a form with a field named A. This field contains numeric values (long integers) and I want to display the sums of the values in A for the lines 5, 6 and 7 in the footer of the report in a field named Sum567, and also the sum of values in A for the lines 1 to 10 in the footer of the report in a field named Sum1to10.

    Here is the code to be added in the module of the report:

    a) Open the form in Design mode.

    b) Click on the Detail selector of the report then in the properties window click on the Event tab and create an event for the On Format event. When the VBA Editor opens or becomes active, paste this code between the lines
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) and End Sub:
    Code:
        lngLineCount = lngLineCount + 1
        If lngLineCount = 5 Or lngLineCount = 6 Or lngLineCount = 7 Then lngSum567 = lngSum567 + Me.A.Value
        If lngLineCount <= 10 Then lngSum1to10 = lngSum1to10 + Me.A.Value
    c) Now click on the Report Footer selector of the report then in the properties window click on the Event tab and create an event for the On Format event. When the VBA Editor opens or becomes active, paste this code between the lines Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer) and End Sub:
    Code:
        Me.Sum567 = lngSum567
        Me.Sum1to10 = lngSum1to10
    d) Now click on the Report selector then in the properties window click on the Event tab and create an event for the On Open event. When the VBA Editor opens or becomes active, paste this code between the lines Private Sub Report_Open(Cancel As Integer) and End Sub:
    Code:
        lngLineCount = 0
        lngSum567 = 0
        lngSum1to10 = 0
    e) In the VBA editor, position the cursor at the top of the module (just after the lines Option Compare Database and (possibly) Option Explicit and past this code there:
    Code:
    Private lngLineCount As Long
    Private lngSum567 As Long
    Private lngSum1to10 As Long
    Summing all together, the module of the formm should now look like:
    Code:
    Option Compare Database
    Option Explicit
    Private lngLineCount As Long
    Private lngSum567 As Long
    Private lngSum1to10 As Long
    
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
        lngLineCount = lngLineCount + 1
        If lngLineCount = 5 Or lngLineCount = 6 Or lngLineCount = 7 Then lngSum567 = lngSum567 + Me.A
        If lngLineCount <= 10 Then lngSum1to10 = lngSum1to10 + Me.A  
    End Sub
    
    Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
    
        Me.Sum567 = lngSum567
        Me.Sum1to10 = lngSum1to10
    
    End Sub
    
    Private Sub Report_Open(Cancel As Integer)
        lngLineCount = 0
        lngSum567 = 0
        lngSum1to10 = 0    
    End Sub
    That's it: the textboxes named Sum567 and Sum1to10 in the Report Footer section should now display both sums when you open the report in View mode.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •