Change RecordSource of Report when output is Excel

[FoxProProgrammer]

My form opens a report based on the selection in an option group. The user can select from one of many reports. Another option group on the form allows the user to direct the output to a screen preview, printer, or Excel. One of the reports requires two different RecordSources. The RecordSource for the report is a Query. I can use the OpenArgs parameter to pass a value to the report and set the RecordSource in the Open event of the report. No problem there. However, the command that I am using to direct the output to Excel doesn't have an openArgs parameter.

DoCmd.outputTo acOutputReport, reportName, acFormatXLS, , True

Is there a way to use the same report or do I have to create separate reports for each Query? Maybe there's another command to direct the output to Excel?

Thank you!


dz

 

[Remou]

How about building a query?

Dim strSQL As String
Dim qdf As DAO.QueryDef

    strSQL = "Select * From tblA Where TextField='" _
    & Me.txtText & "'" 
    If DLookup("Name", "MSysObjects", "Name= 'tmpQry'") <> "" Then
        Set qdf = CurrentDb.QueryDefs("tmpQry")
        qdf.SQL = strSQL
    Else
        Set qdf = CurrentDb.CreateQueryDef("tmpQry", strSQL)
    End If

 

[dhookom]

You might be able to modify the SQL of the the query the report is based on. For instance, if the report's record source is "QUERYA" and you want the records to be the result of either QUERYB or QUERYC you could use code like:

Currentdb.QueryDefs("QUERYA").SQL = "SELECT * FROM QUERYB;"

Then open your report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[FoxProProgrammer]

I'm not really following either of your examples. Here's some of the code in my report form. The problem occurs with report3. I need to change the RecordSource depending on the value of a checkbox (chk_all). I pass the value of mode to the report and evaluate it in the Open event...then set the RecordSource appropriately. For whatever reason, the syntax to send the report to Excel doesn't have an openArgs parameter. How would I use your sample in the code below, or do I need to restructure it?

Thank you,

Select Case reportType
    Case 1
        reportName = "report1..."
    Case 2
        reportName = "report2..."
    Case 3
        reportName = "report3..."
        
        If chk_all.Value = False Then
            mode = 1
        Else
            mode = 2
        End If
End Select
        
Select Case outputTo
    Case 1
        DoCmd.OpenReport reportName, acViewPreview, , , acDialog, mode
    Case 2
        DoCmd.OpenReport reportName, acViewNormal, , , acDialog, mode
    Case 3
        DoCmd.outputTo acOutputReport, reportName, acFormatXLS, , True
End Select

dz

 

[Remou]

Perhaps like so:

Select Case reportType
    Case 1
        reportName = "report1..."
    Case 2
        reportName = "report2..."
    Case 3
        reportName = "report3..."
        
        If chk_all.Value = False Then
            mode = 1
        'qryRpt1 is the query the report is based on
        'Query1 is the print / preview query ...
        Set qdf = CurrentDb.QueryDefs("qryRpt1")
        qdf.SQL = "Select * From Query1"

        Else
            mode = 2
        'Query2 is the Excel query
        Set qdf = CurrentDb.QueryDefs("qryRpt1")
        qdf.SQL = "Select * From Query2"
        End If
End Select
        
Select Case outputTo
    Case 1
        DoCmd.OpenReport reportName, acViewPreview, , , acDialog, mode
    Case 2
        DoCmd.OpenReport reportName, acViewNormal, , , acDialog, mode
    Case 3
        DoCmd.outputTo acOutputReport, reportName, acFormatXLS, , True
End Select