Programmatic listing of reports

[Biggles]

I have to rewrite a database, and so I want a list of the existing reports to
setup a project tracking spreadsheet or MS project task list. I want the
name of the report and the recordsource. I have found some code to create a
list of the reports, but I don't know how to do the second part. In the code
below, I am simply using the debug.print, but once I can get that to work, I
will write the code for the insert sql.

Public Sub createrptlist()

Dim dbCurr As DAO.Database
Dim rst As DAO.Recordset
'Dim tdfCurr As Reports
Dim x As Integer
' Dim Y As Variant

Dim icount As Integer

Dim strSQL As String

Set dbCurr = CurrentDb()
On Error GoTo handleerror

strSQL = "SELECT * FROM MSysObjects WHERE Type=-32764 ORDER BY Name"

Set rst = dbCurr.OpenRecordset(strSQL)

x = rst.RecordCount

For icount = 0 To 5 'x - 1
Debug.Print rst!Name
'FOR EACH OF THESE, ONCE YOU FIND THE NAME, FIND THE QUERY OR SQL THAT
IS THE REPORT RECORDSOURCE
Debug.Print rst!Name.RecordSource
'of course, the above does not work, how could I get the recordsource
information, whether from this recordset or another.
rst.MoveNext
Next icount
'dbCurr.Execute strSQL

Set dbCurr = Nothing


exithere:
Exit Sub

handleerror:
Debug.Print Err.Description
Debug.Print vbCr
Resume Next
End Sub

 

[Klatuu]

Here is a procedure that will work for you

Public Sub ListReportInfo()
Dim objReports As Object
Dim rpt As Report
Dim strName As String
Dim strSource As String

For Each objReports In CurrentProject.AllReports
strName = objReports.Name
DoCmd.OpenReport strName, acViewDesign, , , acHidden
strSource = Application.Reports(0).RecordSource
DoCmd.Close acReport, strName
Debug.Print "Report Name " & strName & " Record Source " & strSource
Next
End Sub