Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2009
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi guys, I'm trying to count the number of groups under each master record in a report. Does anyone have any ideas on how to store this information in a text box control on the report?

    HansV helped me put =Count(*) in a text box to get the total number of records in each reported detail section. is there a way to get the number of groups?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    You can simply sum the value of each of the controls displaying the number of records in each detail section, or if you want the count of the master records, you can do much like you did on the records in the detail section.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Another option, perhaps more robust, is to do something like this.

    In the Declarations section of the report module, define a variable of suitable size.

    Code:
    Dim mlngGroupCount as Long
    Then, either the Group Header or the Group Footer (it doesn't matter which), you add an OnFormat event handler.

    Code:
    Private Sub Group1_Header_OnFormat()
    
    	mlngGroupCount  = mlngGroupCount  + 1
    
    End Sub
    Finally, in the report footer, another OnFormat event updates a field in the report footer.

    Code:
    Private Sub Report_Footer_OnFormat()
    
    	Me.txtGroiupCount.Text = mlngGroupCount
    
    End Sub
    There may be a more elegant way to do this, but I've done it this way, several times, with good results, and almost no overhead.

    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    The OnFormat even often executes more than once for a given section, so I find the method here can give invalid answers.

    I attach a demo in which the count at the bottom is twice the right answer. There are two versions- one gives the wrong count, the other the correct count.
    I would have thought that testing for FormatCount =1 before incrementing the count would fix the count, but it does not.

    If I put the code in the OnPrint event, it gives the right answer, but I am not sure why this makes any difference.

    Another thing that can go wrong is that Print Preview shows the correct answers, but if you then Print the report the count keeps incrementing again.
    So it is useful to reset the count to 0, in the OnPrint event of the Report Header.
    [attachment=86906:Reportcount.zip]
    Attached Files Attached Files
    Regards
    John



  5. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    John,

    You are absolutely right about the OnFormat event, and I forgot that critical detail.

    Your point about reinitializing the count in the OnPrint event of the report header is also on the mark, and is another detail that I neglected to mention.
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

Posting Permissions

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