Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Iterate Through Report Sections

Status
Not open for further replies.

maxhugen

Programmer
May 25, 2004
498
AU
Does anyone know of a way to iterate through the sections of a report? Oddly, each section is NOT part of a collection, but is stored in the array "me.section"

However, I tried to loop through this array, using UBound(me.section()), but that doesn't work either.

I've even resorted to trying an ugly method, iterating through index numbers until I hit an error, but this fails too:
Code:
On Error Resume Next
For i = 0 To 99
    Debug.Print i, Me.Section(i).Name
    If Me.Section(i).Tag = "Hide" Then _
        Me.Section(i).Visible = False
    If err <> 0 Then Exit For
Next i
On Error GoTo err_fNoData

It only iterates through:
0 Detail
1 ReportHeader
2 ReportFooter
3 PageHeaderSection
4 PageFooterSection
The rest of the sections are missing!

I'm stumped here. Any ideas would be most appreciated!
MTIA


Max Hugen
Australia
 
Why would you want to do this? The first Group Header would be 5 with the its footer being 6 (only if you have sorting and grouping levels)

If you want to hide a report section, consider canceling it during the On Format event of the section.

Duane
Hook'D on Access
MS Access MVP
 
At first I thought this had to be wrong, that there has to be a sections(s) collection, but there is not. Then the section property is really weird because it is a property. It does not return a section array like listItems for a listbox.

I also think that 1 through 8 are reserved, and then 9 ... is for additional group levels. If you do not include 1 through 8 the first group still starts at 9. That is why your code does not fail. You hit an index without a section, but there are more sections at index 9 and beyond.

0 acDetail Report detail section
1 acHeader Report header section
2 acFooter Report footer section
3 acPageHeader Report page header section
4 acPageFooter Report page footer section
5 acGroupLevel1Header Group-level 1 header section
6 acGroupLevel1Footer Group-level 1 footer section
7 acGroupLevel2Header Group-level 2 header section
8 acGroupLevel2Footer Group-level 2 footer section

Note
If a report has additional group-level sections, the header/footer pairs are numbered consecutively beginning with 9.

The Section property is an array of all existing sections in the form or report specified by the section number. For example, Section(0) refers to a form's detail section and Section(3) refers to a form's page header section

So if you really wanted a collection of sections here is how.

Code:
Public Function getSections(rpt As Access.Report) As Collection
  On Error GoTo errlbl
  Dim colTemp As New Collection
  Dim i As Integer
  Dim tempName As String
  For i = 1 To 100
     colTemp.Add rpt.Section(i)
     If tempName = rpt.Section(i).Name Then Exit For
     tempName = rpt.Section(i).Name & " " & i
  Next i
  Set getSections = colTemp
  Exit Function
errlbl:
  If Err.Number <> 2462 Then
    MsgBox Err.Number & " " & Err.Description
  Else
    Resume Next
  End If
End Function

Public Sub testSections()
  Dim rpt As Access.Report
  Dim sct As Section
  Dim scts As Collection
  Set rpt = Reports("rptOne")
  Set scts = getSections(rpt)
  For Each sct In scts
    Debug.Print sct.Name
    Debug.Print sct.BackColor
  Next sct
End Sub
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top