Loop through sections and objects in a report

[Haoran]

Can anyone give an idea how I can loop through the objects of a report, every
row?
And, how to go to the next page. So, I'm trying to get the data in the
details section.

Partial Code:

Public Function SaveReportDetail() As Boolean
Dim strObjectName As String
Dim oRPT As Report
Dim oCTRL As Control, oPROP As Property
Dim oPAGE As Page, strQuery As String

strObjectName = Application.CurrentObjectName
If Application.CurrentObjectType = acReport Then
Set oRPT = Application.Reports.Item(strObjectName)
For Each oCTRL In oRPT.Section(0).Controls

Next
Else
MsgBox "not a report"
End If
End Function


Thanks!!

 

[Marshall Barton]

Can anyone give an idea how I can loop through the objects of a report, every
row?
And, how to go to the next page. So, I'm trying to get the data in the
details section.

Partial Code:

Public Function SaveReportDetail() As Boolean
Dim strObjectName As String
Dim oRPT As Report
Dim oCTRL As Control, oPROP As Property
Dim oPAGE As Page, strQuery As String

strObjectName = Application.CurrentObjectName
If Application.CurrentObjectType = acReport Then
Set oRPT = Application.Reports.Item(strObjectName)
For Each oCTRL In oRPT.Section(0).Controls

Next
Else
MsgBox "not a report"
End If
End Function

Reports do not have "rows". They have sections. And a page
is a (simulated) piece of paper, so a page doesn't contain
anything except space where things can be displayed.

Here's some air code to give you a general idea of how to
cycle throught all reports, their controls and each
control's properties:

Dim db As Database
Dim doc As Document
Dim ctl As Control
Dim prp As Property

Set db = CurrentDb()
For Each doc In db.Containers!Reports.Documents
DoCmd.OpenReport doc.Name, acDesign
For Each ctl In Reports(doc.Name).Controls
Debug.Print ctl.Name; Spc(5);"Section ";ctl.Section
For Each prp In ctl.Properties
Debug.Print Spc(3); prp.Name & " = " & prp.Value
Next prp
Next ctl
DoCnd.Close acReport, doc.Name, acSaveNo
Next doc
Set db = Nothing

 

[Haoran]

Thanks for the reply Marshall,

How then, does a report save it's data to a file if it is how you have
outlined?

I'm trying to save just the details section of the report to disk as a CSV
file.

Thanks,

 

[Marshall Barton]

What? You want to save the report's data? I thought you
wanted to save some parts of the report's design.

Generally, a report is a lousy mechanism for exporting data.
Instead, you should export the report's record source
table/query. See the TransferText method in VBA Help.

 

[Haoran]

Thanks Marshall,

But I have too many reports to do it this way. I really need a way to just
take the details section at preview and save it to disk... Can you think of
any way I can loop through the details section at runtime/preview?

Thanks,

What? You want to save the report's data? I thought you
wanted to save some parts of the report's design.

Generally, a report is a lousy mechanism for exporting data.
Instead, you should export the report's record source
table/query. See the TransferText method in VBA Help.
--
Marsh
MVP [MS Access]

How then, does a report save it's data to a file if it is how you have
outlined?

I'm trying to save just the details section of the report to disk as a CSV
file.

 

[Marshall Barton]

There usually is a way, but it's still a lousy way to do
things. If you have too many report's, you will still have
to add all the code to all the reports and that sounds like
a lot more work than writing a procedure to export a bunch
of queries.

To do It in the report's, use the report's Open, Close and
detail section's Print eventd with a binch of file I/O
statements. See the Open, Print, etc, statements in VBA
Help.

This is just vague air code to provide a general outline.

Open event:
fn = FreeFile
Open "path to somefile" For Output As fn

detail Format event:
For Each ctl In Me.Section(0).Controls
If ctl.ControlType = acTextbox Then
Print ctl.ControlSource & "=" ctl.Value & ",";
End If
Next ctl
Print

Report Close event:
Close fn