Retrieving the recordsource property of a report

I need to get the recordsource property value (or even the SQL string itself
if possible) for any given report. I have tried all day and cannt figure it
out.

For what its worth, I have a custom report db that doesnt need to be linked
to the main db. I get the SQL string, create a make table query and output it
to the custom report db and the report runs. This keeps the record locks
down.

Any help is WAY appreciated.
--
Sean Henry

Sean Henry wrote:

I need to get the recordsource property value (or even the SQL string itself
if possible) for any given report. I have tried all day and cannt figure it
out.

For what its worth, I have a custom report db that doesnt need to be linked
to the main db. I get the SQL string, create a make table query and output it
to the custom report db and the report runs. This keeps the record locks
down.

Any help is WAY appreciated.


You have to open the report to get the report's properties.
If you don't want the report to run, open it in design view.
OTOH maybe you can place the code to make the table in the
report's Open event??

--
Marsh
MVP [MS Access]

On Fri, 18 Nov 2005 20:01:03 -0800, Sean Henry wrote:

I need to get the recordsource property value (or even the SQL string itself
if possible) for any given report. I have tried all day and cannt figure it
out.

For what its worth, I have a custom report db that doesnt need to be linked
to the main db. I get the SQL string, create a make table query and output it
to the custom report db and the report runs. This keeps the record locks
down.

Any help is WAY appreciated.

If you want just the record source of one known report:

DoCmd.OpenReport "ReportName", acViewDesign, , , acHidden
Debug.Print "Record Source: " & Reports!ReportName.RecordSource
Docmd.Close acForm, 'ReportName"

Or you can use the built in documenter (Tools + Analyze + Documenter)

or you can cycle through the containers and just print the
recordsource.

Public Sub RecordSource()
Dim doc As DAO.Document
Dim cont As DAO.Container
With CurrentDb
For Each cont In .Containers
If cont.Name = "Reports" Then
For Each doc In cont.Documents
DoCmd.OpenReport doc.Name, acViewDesign, , , acHidden
Debug.Print "Report Name: " & doc.Name
Debug.Print "Record Source: " &
Reports(doc.Name).RecordSource
Debug.Print
DoCmd.Close acReport, doc.Name
Next doc
End If
Next cont
End With
End Sub


--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Thanks for the input

I cannot open the report in design mode because this is an mde file.

Cycling through I saw, but I only want to do this for one report at a time.
Is it possible to not cycle but find the specific report via this containers
method?

--
Sean Henry


"fredg" wrote:

On Fri, 18 Nov 2005 20:01:03 -0800, Sean Henry wrote:

I need to get the recordsource property value (or even the SQL string itself
if possible) for any given report. I have tried all day and cannt figure it
out.

For what its worth, I have a custom report db that doesnt need to be linked
to the main db. I get the SQL string, create a make table query and output it
to the custom report db and the report runs. This keeps the record locks
down.

Any help is WAY appreciated.

If you want just the record source of one known report:

DoCmd.OpenReport "ReportName", acViewDesign, , , acHidden
Debug.Print "Record Source: " & Reports!ReportName.RecordSource
Docmd.Close acForm, 'ReportName"

Or you can use the built in documenter (Tools + Analyze + Documenter)

or you can cycle through the containers and just print the
recordsource.

Public Sub RecordSource()
Dim doc As DAO.Document
Dim cont As DAO.Container
With CurrentDb
For Each cont In .Containers
If cont.Name = "Reports" Then
For Each doc In cont.Documents
DoCmd.OpenReport doc.Name, acViewDesign, , , acHidden
Debug.Print "Report Name: " & doc.Name
Debug.Print "Record Source: " &
Reports(doc.Name).RecordSource
Debug.Print
DoCmd.Close acReport, doc.Name
Next doc
End If
Next cont
End With
End Sub


--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

On Sat, 19 Nov 2005 07:12:02 -0800, Sean Henry wrote:

Thanks for the input

I cannot open the report in design mode because this is an mde file.

Cycling through I saw, but I only want to do this for one report at a time.
Is it possible to not cycle but find the specific report via this containers
method?

If it's an mde you can't open the report in design view, nor can you
write any code, so .... unless you have access to the original mdb I
can't suggest a way to do this.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

I thought that might be the case. It is weird, I can set the property via
code int he repor,t but I cannot read the property without the object in
design mode.

Thanks for the help
--
Sean Henry


"fredg" wrote:

On Sat, 19 Nov 2005 07:12:02 -0800, Sean Henry wrote:

Thanks for the input

I cannot open the report in design mode because this is an mde file.

Cycling through I saw, but I only want to do this for one report at a time.
Is it possible to not cycle but find the specific report via this containers
method?

If it's an mde you can't open the report in design view, nor can you
write any code, so .... unless you have access to the original mdb I
can't suggest a way to do this.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail