How do I get the number of sections in a report? 1

[HFloyd]

Hello, all,

I am trying to get the total number of sections on a report via VBA. Does anyone have experience doing this?

This code doesn't work:

iTotalSections = rpt.Sections.Count

because there is no "Sections" collection. According to MS Help, report sections are an array, but this code doesn't work either:

iTotalSections = UBound(rpt.Section())

I keep getting an "argument not optional" error.

Does anyone know an efficient way to get the count of the sections on a report?

Thanks,

Heather







Floyd Innovations

http://www.floydinnovations.com

 

[dhookom]

You could use code in the On Open event (or other) like

Private Sub Report_Open(Cancel As Integer)
    Dim intSectCount As Integer
    Dim intX
    Dim strTag As String
    On Error Resume Next
    For intX = 0 To 20
        strTag = Me.Section(intX).Tag & ""
        If Err = 0 Then
            intSectCount = intSectCount + 1
            
        End If
        Err.Clear
    Next
    Debug.Print intSectCount
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[HFloyd]

Thanks, Duane,

That did occur to me, and since I was desperate to finish the report I ended up writing a custom function:

Function GetLastSection(Rpt As Report, Optional PrintToImmediate As Boolean = False)
' Created 3/22/2005 [URL unfurl="true"]www.floydinnovations.com[/URL]
On Error GoTo ErrorCode

Dim lngCount As Long
Dim strTemp As String
Dim strTemp2 As String
        
'loop until error
    For lngCount = 0 To 100
        strTemp = Rpt.section(lngCount).Name
        If lngCount - 4 > 0 Then
            strTemp2 = " : " & Rpt.GroupLevel(lngCount - 4).ControlSource
        Else
            strTemp2 = ""
        End If
        If PrintToImmediate Then
            Debug.Print "Section(" & lngCount & ") : " & strTemp & strTemp2
        End If
    Next lngCount
    
ExitCode:
    Exit Function

ErrorCode:
    Select Case Err.number
        Case 2462   'last section reached
            GetLastSection = lngCount - 1
            Resume ExitCode
        Case Else
            MsgBox "Error " & Err.number & ": " & Err.Description, vbCritical
            'Debug.print "Error " & Err.Number & ": " & Err.Description
            Resume ExitCode
    End Select

End Function

I guess there's no built in way to access it directly, though?

Heather

Floyd Innovations

http://www.floydinnovations.com

 

[dhookom]

I'm not aware of an easier method. It also isn't clear why you would want to determine this with code?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[NorthNone]

Yes, I'd like to know how this information would be helpful to you. Maybe it is something I could use.

---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[HFloyd]

Sure,

Well, you see, I create a lot of different Access databases for different clients, so I have been in the mind set to make any functions and code I develop to be as easily resuable between projects as possible.

One of the things I've developed is a flexible reporting system. There is a table to hold the reports info: Display Title, report name, and other stuff, along with 10 fields for the names of "criteria forms." each of these forms is developed based on a template, but each one can be created to pull data from tables or calculate values. Each of the forms is for choosing a single bit of criteria - I have one for date range, ones created for individual database items - choosing a person, or a category of some sort, etc. And one is for chossing whether to show Details and/or Summary information on a report. (Two check boxes)

The way the system works is that the forms (using the ReportID) can string themselves together like a wizard, and to add a new report to my menu, I just need to add the report info to my reports table. And to each of the criteria fields, if there is a need for criteria, I put the name of the criteria form in.

Anyway, the detail/summary criteria form's code needed to be flexible enough to take various arguments to show/hide various sections of the report, and I found that certain fields needed to be hidden, if for instance, the detail records weren't going to be displayed, even though those fields were in a section that should remain displayed (because it had a header). So, The problem I was facing is that just hiding a field doesn't close up the empty vertical space - you actually need to change the height of the section to tighten it up. So, the correct section height had to be calculated minus the field that had been hidden, and the way I was finding the controls that need to be hidden for a "Summary only" report was by putting certain text in the "TAG" property of those controls on the report, then having the code loop through the controls in each section to hide certain ones and subtract the height of the hidden control from the height of the section, and set the new height.

Phew...
Anyway, the reason i wanted to get the number of sections was so that when I loop through the sections on the page, I know when to stop.

If people are very interested I might be able to upload a stripped down version of this one report I was working on so it might make more sense...

Thanks, I have learned a lot in these forums!

Heather

Floyd Innovations

http://www.floydinnovations.com

 

[NorthNone]

Very innovative, Floyd!

---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[NorthNone]

Oops... Very innovative, Heather! (At Floyd Innovations)

---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[HFloyd]

Hey, Thanks for the Star!:-X

Heather

Floyd Innovations

http://www.floydinnovations.com