I'm receiving the subject error when trying to export a crosstab query to excel. There are 19 parameters on the query that I tried to list in the SQL for rs1 below. Can someone tell me what I'm doing wrong? I tried to follow the MSDN website as closely as I could.
Code:
Dim rs1, rs2, rs3, rs4 As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim cnt As Integer
Dim Lrow, Lrow1 As Long
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rng, rng1 As Excel.Range
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Add
Set wks = wbk.Worksheets(1)
Set rng = wks.Range("A1")
appExcel.Visible = False
cnt = 1
Set qdf = CurrentDb.QueryDefs("Qry_Comparison_Bulk")
Set rs1 = CurrentDb.OpenRecordset("PARAMETERS [Forms]![frm_BulkComparison_Select]![Item 1] TEXT, [Forms]![frm_BulkComparison_Select]![Item 2] TEXT, [Forms]![frm_BulkComparison_Select]![Item 3] TEXT, [Forms]![frm_BulkComparison_Select]![Item 4] TEXT, [Forms]![frm_BulkComparison_Select]![Item 5] TEXT," _
& "[Forms]![frm_BulkComparison_Select]![Item 6] TEXT, [Forms]![frm_BulkComparison_Select]![Item 7] TEXT, [Forms]![frm_BulkComparison_Select]![Item 8] TEXT, [Forms]![frm_BulkComparison_Select]![Item 9] TEXT, [Forms]![frm_BulkComparison_Select]![Item 10] TEXT, [Forms]![frm_BulkComparison_Select]![Item 11] TEXT, " _
& "[Forms]![frm_BulkComparison_Select]![Item 12] TEXT, [Forms]![frm_BulkComparison_Select]![Item 13] TEXT, [Forms]![frm_BulkComparison_Select]![Item 14] TEXT, [Forms]![frm_BulkComparison_Select]![Item 15] TEXT, [Forms]![frm_BulkComparison_Select]![bill1] TEXT, [Forms]![frm_BulkComparison_Select]![bill2] TEXT, " _
& "[Forms]![frm_BulkComparison_Select]![bill3] TEXT, [Forms]![frm_BulkComparison_Select]![BP] TEXT; " _
& "TRANSFORM Sum(tbl_Formulas.Input) AS SumOfInput " _
& "SELECT tbl_Formulas.BillType, tbl_Formulas.RawMaterial " _
& "FROM tbl_Formulas " _
& "WHERE (tbl_Formulas.BillType)=[Forms]![frm_BulkComparison_Select]![bill1] Or (tbl_Formulas.BillType)=[Forms]![frm_BulkComparison_Select]![bill2] Or (tbl_Formulas.BillType)=[Forms]![frm_BulkComparison_Select]![bill3] AND (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 1] Or " _
& "(tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 2] Or (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 3] Or (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 4] Or (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 5] Or " _
& "(tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 6] Or (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 7] Or (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 8] Or (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 9] Or " _
& "(tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 10] Or (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 11] Or (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 12] Or (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 13] Or " _
& "(tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 14] Or (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 15] AND (tbl_Formulas.BP)=[Forms]![frm_BulkComparison_Select]![BP] " _
& "GROUP BY tbl_Formulas.BillType, tbl_Formulas.RawMaterial " _
& "PIVOT tbl_Formulas.Item;")
qdf.SQL = rs1
...