How are you changing the Report Recordset? Are you using VBA?
Please share the way you call the report from the Form

I've tried many methods VBA seems to be the most flexible.  But I'm relatively new at VBA.  Any suggestions?  my last effort at VBA was:  Private Sub NZSelBtn_Click()
    On Error Resume Next
    
    ' Get the ID and SQLData from the table NZMultiReport_T
    Dim recordID As Long
    Dim strSQL As String
    
    ' Assuming the ID field in your form is named "ID"
    recordID = Me.ID
    strSQL = DLookup("SQLData", "NZMultiReport_T", "ID = " & recordID)
    
    ' Check if SQL statement is not empty
    If strSQL <> "" Then
        ' Modify the RecordSource property of the report "NZxxx_R"
        Reports("NZxxx_R").RecordSource = strSQL
        
        ' Requery the report to reflect changes
        Reports("NZxxx_R").Requery
        
        ' Open the report in preview mode
        DoCmd.OpenReport "NZxxx_R", acViewPreview
    Else
        MsgBox "SQL statement is empty.", vbExclamation, "Error"
    End If
End Sub

You won't be able to set the Record Source of your Report from a button on your Form.
Use the Open event of the Report.

For Example:
    Me.RecordSource = Forms!FormName!SQLData

Substitute FormName for the name of your form

I would also add a check to make sure the Form is open. You can use Richard's IsLoaded function for this.

Button Code Example:
    Private Sub NZSelBtn_Click()

        Dim strSQL As String
    
        If IsNull(ID) Then Exit Sub
        strSQL = Nz(DLookup("SQLData", "NZMultiReport_T", "ID=" & ID),"")
    
        If strSQL <> "" Then
            Me.refresh
            DoCmd.OpenReport "NZxxx_R", acViewPreview
        Else
            MsgBox "SQL statement is empty.", vbExclamation, "Error"
        End If

    End Sub

Just to be sure Kevin.  In the Open Event for the Report I use  your example.  And the Command button the rest of the code.

Hi William, if your reports differ mainly in the criteria, you can use that criteria for the OpenReport command, without having to change the report's record source:

      DoCmd.OpenReport "MyReport", acViewPreview, , "[Field1]='Company A' And [Field2]=2023"

If your report has groupings and sortings, they determine the report's sort order, and therefore they ignore the ORDER BY clause of the SQL in the report's record source.  So you may omit writing ORDER BY altogether.

Thank you Kevin.  That worked well.