In reponse to:
You received the following message from: benmay14 (email@example.com)
Did you ever get report using a pass through query with arguments
problem solved? I am trying to do this myself... The problem I am describing
is available here:
Any help you could provide me would be greatly appreciated. Thanks!
Sorry about the delay in responding, here goes:
1. Make the record source of the report TArrivalsByRegion.
2. In report open, create a querydef, to which you will append your form parameters.
Private Sub Report_Open(Cancel As Integer)
Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form
' Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Forms!DateRangePreviousCurrentYear
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("TArrivalsByRegion")
Dim rstReport As DAO.Recordset
qdf.SQL = "EXEC TArrivalsByRegion " & Forms!DateRangePreviousCurrentYear!PrevYear & "," & Forms!DateRangePreviousCurrentYear!CurrYear
qdf.ReturnsRecords = True
' Open Recordset object.
Set rstReport = qdf.OpenRecordset()
3. Create a Passthough query which contains the following statement:
4. Create a Stored Procedure with the following:
CREATE PROCEDURE TArrivalsByRegion
@PrevYear1 varchar(4),@CurrYear1 varchar(4)
SELECT regionorder, Regions3, 1 As Quarter, January as A, February as B, March as C FROM dbo.RegionofResidenceCrosstabFunc(@PrevYear1,@Curr Year1)
Interesting thing to note, after I run the code and check the Passthrough query it has:
EXEC TArrivalsByRegion 2001,2003
if I used the parameters 2001, 2003.
Looks like if I hardcoded the values but this isn't so because if I try different parameters, e.g 2001, 2002 the Passthough query is updated with these parameters.
Let me know if this helps.