Report Name as Variable to Get Record Source

[DougAtAvalon]

How do I use code to get the record source of a report and assign it to a string ex:

****************
variable = Me.ReportName
stDocName = Reports![variable].RecordSource
****************


FYI then I will:
DoCmd.OutputTo acOutputQuery, stDocName, acFormatXLS, , False
-Doug

 

[codestorm]

From memory you can only get the recordsource when the report object is open

perhaps something like

Sub Report_Open()
on error goto err
DoCmd.OutputTo acOutputQuery, Me.RecordSource
, acFormatXLS, , False
ex:
exit sub
err:
msgbox error$
resume ex
End Sub

not sure if sub is called that .. did it from memory ..
also not sure if recordsource will be set by that stage
codestorm

 

[RickSpr]

I'm guessing the code you gave is in a form, and ReportName is a control that contains the name of a report whose records you want to write to an Excel file, right?

When you're using a variable to index the Reports (or any other) collection, you don't use the "!" operator. You only use the "!" operator when you're typing the actual name directly into your code. For your purpose, you want this:

    variable = Me.ReportName
    stDocName = Reports(variable).RecordSource

Note that you can only do this this way if the report is in the Reports collection, that is, when the report is open.

Your OutputTo usage will only work if the report's recordsource is an Access query. Reports that are based on tables, or on SQL statements, will give you an error. Rick Sprague

 

[DougAtAvalon]

Is it impossible to refer to the properties, specifically Record Source of a report that is not open? Is there any way to "trick" the user and open it in the "background"?

-Doug

 

[RickSpr]

I don't think you can access the properties via its Document object, but here's something that might work for you:

    Function ReportRecordSource(ReportName As String) As String
        Dim rpt As Report
        Set rpt = New Report_<report name>
        ReportRecordSource = rpt.RecordSource
        Set rpt = Nothing
    End Sub

This is like creating non-default instances of a form (search the help file for "non-default instances&quot. Note: This may not work well for you. The report's Open, Load, and Close events will fire when you do this, and that might be a problem. Rick Sprague