ACC2000: How to Print the RecordSource Property Value for All Reports

This article shows you how to create a procedure that prints a list of each report in a database and its RecordSource property value to the Immediate window.

The Reports collection is the set of all open reports. One method to print the RecordSource property value of each report is to go through the set of report documents, open each one in Design view, and get the RecordSource property value. This article presents a procedure that uses that method. It automatically processes all the reports defined in the database. To create the procedure, follow these steps:

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. Create a new module, and then type or paste the following sample code:

   Sub ListReports()
      Dim db As DAO.Database, doc As Document, con As Container
      Set db = CurrentDb()
      Set con = db.Containers("Reports")
      Application.Echo False
      For Each doc In con.Documents
         Debug.Print "Report Name:", doc.Name
         DoCmd.OpenReport doc.Name, acViewDesign
         Debug.Print "RecordSource:", Reports(doc.Name).RecordSource
         Debug.Print
         DoCmd.Close acReport, doc.Name
      Next
      Application.Echo True
   End Sub
   
   					

3. To see a list of reports and their RecordSource property values, type the following line in the Immediate window, and then press ENTER:
   ListReports