report based on crostab query with parameters

[phleduc]

I am trying to build a report based on a crosstab query, to that purpose
(according to Access developers handbook)
I use the reports recordsource (a crosstab query) to get the recordset
needed to bound my (unbound) controls on the "on open" event of of the
report

This works fine but my problem is that I need to launch the report from a
form, grab a couple of parameters of that form and filter the recordsource
accordingly.

To that purpose I would like to build a SQL string including the parameters.

How can I modify the code below to make this work?
let say strSQL =

TRANSFORM Sum(QryBookingsGRCstep1.bkiQuantity) AS SumOfbkiQuantity
SELECT QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode AS property,
QryBookingsGRCstep1.bkgID AS bkg, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat AS Cat, Avg(QryBookingsGRCstep1.rbkUnitPrice) AS
[Avg Price]
FROM QryBookingsGRCstep1
WHERE (((QryBookingsGRCstep1.proid)=3))
GROUP BY QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode,
QryBookingsGRCstep1.bkgID, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat
ORDER BY QryBookingsGRCstep1.proCode, QryBookingsGRCstep1.bkgID,
QryBookingsGRCstep1.rbkDate
PIVOT QryBookingsGRCstep1.rbkDate;
WHERE


Private Sub Report_Open(Cancel As Integer)
' You didn't know how many columns, or what
' their names would be, until now.
' Fill in the label captions,
' and control ControlSources.

Dim intColCount As Integer
Dim intControlCount As Integer
Dim i As Integer
Dim strName As String

On Error Resume Next

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open _
Source:=Me.RecordSource, _
ActiveConnection:=CurrentProject.Connection, _
Options:=adCmdTable

 

[Duane Hookom]

I think the solution you are attempting is an inefficient method. I prefer
the crosstab report method found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. It uses
much less code, is faster, and is more flexible.