Compile a report list

[Adrian Turner]

Does anyone know of a way to compile a list of reports and more importantly
which are subreports of other reports. We have an adp which could with some
housekeeping. A manual trawl seems a bit old fashioned. Any help would be
appreciated.

Thanks

Adrian

 

[Allen Browne]

To get a list of the reports in the database, loop through
CurrentProject.AllReports, or using a query like this:
SELECT [Name] FROM MsysObjects
WHERE ([Name] Not Like "~*") AND ([Type] = -32764))
ORDER BY [Name];

To programmatically discover which reports are subreports of others, you
would need to open each report in design view, loop through the Controls of
the Report until you find a subform/subreport control, and examine its
SourceObject to find out if it is a subreport. This isn't foolproof, since
the Open event procedure of a report can assign something else to the
SourceObject of a subreport. Additionally, some reports may be opened in
their own right as well as acting as a subreport.

 

[Adrian Turner]

Can opening in Design view be done programmatically. An example showing what
you suggest would be great. Thanks for the prompt reply.

Adrian

To get a list of the reports in the database, loop through
CurrentProject.AllReports, or using a query like this:
SELECT [Name] FROM MsysObjects
WHERE ([Name] Not Like "~*") AND ([Type] = -32764))
ORDER BY [Name];

To programmatically discover which reports are subreports of others, you
would need to open each report in design view, loop through the Controls of
the Report until you find a subform/subreport control, and examine its
SourceObject to find out if it is a subreport. This isn't foolproof, since
the Open event procedure of a report can assign something else to the
SourceObject of a subreport. Additionally, some reports may be opened in
their own right as well as acting as a subreport.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Does anyone know of a way to compile a list of reports and more
importantly
which are subreports of other reports. We have an adp which could with
some
housekeeping. A manual trawl seems a bit old fashioned. Any help would be
appreciated.

Thanks

Adrian

 

[Adrian Turner]

Thanks for your response Allen. Figured it out for myself (quite easy really).
I thought I would list it here in case anyone is interested.

Sub ListReports()
Dim filesys, fname, tstream

'query to return report names
' SELECT [Name] FROM MsysObjects WHERE ([Name] Not Like "~*") AND ([Type] =
-32764)) ORDER BY [Name];


Set filesys = CreateObject("Scripting.FileSystemObject")
Set tstream = filesys.CreateTextFile("c:\temp\Reports.txt", True)

For Each r In CurrentProject.AllReports
tstream.writeline r.Name

Application.DoCmd.OpenReport r.Name, acViewDesign

For Each ctl In Reports(r.Name).Controls
If ctl.ControlType = acSubform Then 'subform/subreport
tstream.writeline " Sub Report - " & ctl.SourceObject
End If
Next
Application.DoCmd.Close acReport, r.Name, acSaveNo
Next

tstream.Close
Set filesys = Nothing

End Sub

Thanks again for the pointer

Ade

Can opening in Design view be done programmatically. An example showing what
you suggest would be great. Thanks for the prompt reply.

Adrian

To get a list of the reports in the database, loop through
CurrentProject.AllReports, or using a query like this:
SELECT [Name] FROM MsysObjects
WHERE ([Name] Not Like "~*") AND ([Type] = -32764))
ORDER BY [Name];

To programmatically discover which reports are subreports of others, you
would need to open each report in design view, loop through the Controls of
the Report until you find a subform/subreport control, and examine its
SourceObject to find out if it is a subreport. This isn't foolproof, since
the Open event procedure of a report can assign something else to the
SourceObject of a subreport. Additionally, some reports may be opened in
their own right as well as acting as a subreport.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Does anyone know of a way to compile a list of reports and more
importantly
which are subreports of other reports. We have an adp which could with
some
housekeeping. A manual trawl seems a bit old fashioned. Any help would be
appreciated.

Thanks

Adrian

 

[Allen Browne]

If you want an example of looping through the reports and opening them in
design view, this code does it:
http://allenbrowne.com/vba-CountLines.html

Scroll down to the "Modules behind reports" block.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your response Allen. Figured it out for myself (quite easy
really).
I thought I would list it here in case anyone is interested.

Sub ListReports()
Dim filesys, fname, tstream

'query to return report names
' SELECT [Name] FROM MsysObjects WHERE ([Name] Not Like "~*") AND ([Type]
=
-32764)) ORDER BY [Name];


Set filesys = CreateObject("Scripting.FileSystemObject")
Set tstream = filesys.CreateTextFile("c:\temp\Reports.txt", True)

For Each r In CurrentProject.AllReports
tstream.writeline r.Name

Application.DoCmd.OpenReport r.Name, acViewDesign

For Each ctl In Reports(r.Name).Controls
If ctl.ControlType = acSubform Then 'subform/subreport
tstream.writeline " Sub Report - " & ctl.SourceObject
End If
Next
Application.DoCmd.Close acReport, r.Name, acSaveNo
Next

tstream.Close
Set filesys = Nothing

End Sub

Thanks again for the pointer

Ade

Can opening in Design view be done programmatically. An example showing
what
you suggest would be great. Thanks for the prompt reply.

Adrian

To get a list of the reports in the database, loop through
CurrentProject.AllReports, or using a query like this:
SELECT [Name] FROM MsysObjects
WHERE ([Name] Not Like "~*") AND ([Type] = -32764))
ORDER BY [Name];

To programmatically discover which reports are subreports of others,
you
would need to open each report in design view, loop through the
Controls of
the Report until you find a subform/subreport control, and examine its
SourceObject to find out if it is a subreport. This isn't foolproof,
since
the Open event procedure of a report can assign something else to the
SourceObject of a subreport. Additionally, some reports may be opened
in
their own right as well as acting as a subreport.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Does anyone know of a way to compile a list of reports and more
importantly
which are subreports of other reports. We have an adp which could
with
some
housekeeping. A manual trawl seems a bit old fashioned. Any help
would be
appreciated.

Thanks

Adrian

 

[Wei Lu [MSFT]]

Hi ,

How is everything going? Please feel free to let me know if you need any
assistance.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.