Through VBA you can add a few lines of code to the Report_Open event - for example;

Private Sub Report_Open(Cancel As Integer)

Dim DB As Database

Dim Q As QueryDef

Set DB = CurrentDb()

Set Q = DB.QueryDefs("MyAccessQuery")

Q.SQL = "exec dbo.mySQLSP " & Forms!frmReportOptions.txtCriteriaField

End Sub

This assumes (1) the Pass Through query has been saved in Access, and as MyAccessQuery, and (2) the query will execute a SQL stored-procedure with a single numeric parameter - a string parameter would need to be enclosed in single-quotes.

Note, the above code permanently changes the Access pass-through query, even though we don't actually 'save' it. For completeness - you can reset the pass-through query again when the report closes (in the Report_Close event), but it's not required.

Private Sub Report_Close()

Dim DB As Database

Dim Q As QueryDef

Set DB = CurrentDb()

Set Q = DB.QueryDefs("MyAccessQuery")

Q.SQL = "exec dbo.mySQLSP 0"

End Sub

😉