Using VBA to populate dynamic crosstab report

[dayna]

I am attempting to employ the oft-recommended MS solution for creating a dynamic crosstab report. The solution involves an unbound parameter collector form, a crosstab query, and report based on that query which is full of unbound textboxes. If the code was working properly, the report’s textboxes would become populated with data from the parameterized crosstab upon opening.

The form and the query are working together beautifully. Both of the controls on my form are unbound comboboxes that display values from a lookup table. The parameters on my crosstab, which come from the form, are defined as integers. I don’t have any problems when I run the query while the form is open. But, when I try to open the report, I get this error message:

“The Microsoft Access database engine does not recognize “ as a valid field name or expression.”

Now, this isn’t the first time that I’ve danced with this lovely little error message. I believe I encountered it when I was first building the crosstab query. Thanks to advice found in this forum, I finally realized that unlike other types of queries, it is necessary to explicitly define the data type of any parameter used in a crosstab. Once I did that, the problem went away. This leads me to believe that the problem could lie with this part of my code:

Private Sub Report_Open(Cancel As Integer)

   '  Create underlying recordset for report using criteria entered in
   '  parcolPrintAttendanceReport form.    
   Dim intX As Integer
   Dim qdf As QueryDef
   Dim frm As Form

   '  Set database variable to current database.
   Set dbsReport = CurrentDb
   Set frm = Forms!parcolPrintAttendanceReport

   '  Open QueryDef object.
   Set qdf = dbsReport.QueryDefs("AttendanceCrosstab")

   [B]' ***PROBABLE CULPRIT!***[/B]   
   ' Set parameters for query based on values entered
   ' in EmployeeSalesDialogBox form.  
   qdf.Parameters("Forms!parcolPrintAttendanceReport!Combo2") _
     = frm!Combo2
   qdf.Parameters("Forms!parcolPrintAttendanceReport!Combo4") _
     = frm!Combo4

   '  Open Recordset object.
   Set rstReport = qdf.OpenRecordset()

   '  Set a variable to hold number of columns in crosstab query.
   intColumnCount = rstReport.Fields.Count

 End Sub

The parameters in my crosstab are still defined. I have gone to great pains to make sure that there are no misspellings anywhere, and I’ve played around with the syntax in the code. Again, the problem seems to be with the code, since the form and the query work just fine. Any ideas?