The following procedure can be used to change the RecordSource of a Report.

'---------------------------------------------------------------------------------------
' Procedure : RedefRptSQL
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Redefine an existing report's recordsource
'             Requires opening the Report in design mode to make the changes
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sRptName    ~ Name of the Report to redefine the Record Source
' sSQL        ~ Table name, Query name or SQL Statement to be used to refine the 
'               Record Source with
'
' Usage:
' ~~~~~~
' RedefRptSQL "Report1", "SELECT * FROM tbl_Contacts ORDER BY LastName;"
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-07-13              Initial Release
' 2         2017-10-11              Cleanup of the Procedure header
'---------------------------------------------------------------------------------------
Function RedefRptSQL(sRptName As String, sSQL As String)
On Error GoTo Error_Handler
    Dim Rpt     As Report

    DoCmd.OpenReport sRptName, acViewDesign, , , acHidden 'Open in design view so we can
                                                          'make our changes
    Set Rpt = Application.Reports(sRptName)
    Rpt.RecordSource = sSQL                               'Change the RecordSource
    DoCmd.Close acReport, sRptName, acSaveYes             'Save our changes

Error_Handler_Exit:
    On Error Resume Next
    Set Rpt = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
            "     Error Number: " & Err.Number & vbCrLf & _
            "     Error Source: RedefRptSQL" & vbCrLf & _
            "     Error Description: " & Err.Description, _
            vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function