ACC: How to Print RecordSource Property Value for All Reports

• Microsoft Access 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article describes a procedure you can use to output a list of each report in a database and its RecordSource property value to the Debug window (or Immediate window in version 2.0).

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

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:

1. Create a new module.

2. Type the following sample code:

   In Microsoft Access 7.0 and 97:

         Sub ListReports()
            Dim db As 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, A_DESIGN
               Debug.Print "RecordSource:", Reports(doc.Name).RecordSource
               Debug.Print
               DoCmd.Close A_REPORT, doc.Name
            Next
            Application.Echo True
         End Sub
   
      In Microsoft Access version 2.0:
   
         Sub ListReports ()
            Dim db As Database, doc As Document, con As Container
            Set db = DBEngine.Workspaces(0).Databases(0)
            Set con = db.Containers("Reports")
            Application.Echo False
            For i = 0 To con.Documents.Count - 1
               Set doc = con.Documents(i)
               Debug.Print "Report Name: " & doc.Name
               DoCmd OpenReport doc.Name, A_DESIGN
               Debug.Print "RecordSource: " & Reports(doc.Name).RecordSource
               Debug.Print
               DoCmd Close A_REPORT, doc.Name
            Next i
            Application.Echo True
         End Sub
   
   

3. To see a list of reports and their RecordSource values, type "ListReports" (without the quotation marks) in the Debug window (or Immediate window in version 2.0), and then press ENTER.

Keywords          : kbprg PgmHowTo PgmObj MdlGnrl RptProp
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto