I would like to be able to loop through all the report objects in the
database and show the recordsource for each and every report, even if the
report is not actively open. If I know in advance of this analysis that I
all of my reports have queries that start with "qry" I can use the code
below. I wonder if there is a way to do this code if the queries could have
several naming conventions (or no convention at all).
Here is my code for the case of having all the report queries start with the
name "qry"....
This code looks cumbersome. Perhas there is a better way to do this.
Public Sub CheckReports()
Dim db As DAO.Database
Dim cnt As DAO.Container
Dim doc As DAO.Document
Dim prp As DAO.Property
Dim i As Integer
Dim j As Integer
Dim strQryName As String
Dim strCheckThis As String
Set db = CurrentDb
Set cnt = db.Containers("Reports")
Debug.Print
For Each doc In cnt.Documents
Debug.Print "Report Name = " & doc.Name
Debug.Print "Report Property String = " & doc.Properties(9).Value
strCheckThis = doc.Properties(9).Value & " ?"
i = InStr(1, strCheckThis, "qry", 1)
j = InStr(i, strCheckThis, "?", 1)
strQryName = Trim(Mid(strCheckThis, i, j - i))
Debug.Print "Derived Query Name = " & strQryName
Debug.Print "--------------"
Next
On Error Resume Next
Set cnt = Nothing
Set db = Nothing
End Sub
