Pulling the record souce object out of Report from a FORM (1 Viewer)

[robertlewisnet]

I have an option group that has many reports being picked using a CASE statement and being fed into a DoCmd.OpenReport command. I am wanting to not actually run the report, but the query (Record Source) and then export the results into a csv file. the issue is I want the query to be run to be as dynamic as the report that is picked.

My question is basically how do you (knowing the report name) go to that report and pull the "Record Source" into a variable so I can feed that variable into a query. I don't know how to export it yet but if this detail get's answered that should not be a problem to find on the forum.

-thanks again.

 

[Jon K]

You can open the report, read the RecordSource and immediately close the report e.g.

Private Sub Command0_Click()

   Dim strRecordSource As String
   
   On Error GoTo ErrHandler
   Application.Echo False
   
   DoCmd.OpenReport "reportName", acViewPreview
   
   strRecordSource = Reports("reportName").RecordSource
   
   DoCmd.Close acReport, "reportName"
   Application.Echo True
   
   Exit Sub
   
ErrHandler:
   MsgBox Err.Description
   Application.Echo True

End Sub

.

 

[robertlewisnet]

99% there...

You are good. the only thing i had to work through was to change the report to Design mode from Prieviw so the RecordSource would be available.

thank you for your help. It works very nicely. Off to searching for the export options!

-Robert