468,161 Members | 1,972 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,161 developers. It's quick & easy.

Create List of Report Recordsource Items

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
1 3148
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.

Similar topics

4 posts views Thread by MSD | last post: by
reply views Thread by Brian | last post: by
1 post views Thread by gcdp | last post: by
reply views Thread by kamranasdasdas | last post: by
reply views Thread by gcreed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.