By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Create List of Report Recordsource Items

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Thu, 05 Feb 2004 04:06:23 GMT, Mike Wiseley wrote:

If you are wanting to get the recordsource of all reports, why be concerned
with what it starts with? Also, try to refer to collection items (property
name in this case) by it's name and not the index number. If you moved this
code to a different version of Access, you may find that property 9 is no
longer the same thing as it was - each version has had some added or
removed!

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

--
Mike Storr
veraccess.com
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.