Export Code Failing "Too Few Parameters, Expected 19."

[lccrews]

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.

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
...

[Bob Fitz]

What does [Forms]![frm_BulkComparison_Select]![Item 2] refer to

[lccrews]

all parameters are referring to form fields where data is input. Without parameters, the query gets confused and doesn't know the data type.

[CJ_London]

suggest assign your sql string to a string variable and debug.print it before assigning the string variable to your openrecordset. You can then copy and paste it from the immediate window to the query sql window and get a better idea of what the problem is.

There are other issues which you haven't reached yet

qdf.SQL = rs1


rs1 is a recordset and you are trying to assign it to a string, so it will fail

you can avoid the use of parameters by changing your code to

"WHERE (tbl_Formulas.BillType)=" & [Forms]![frm_BulkComparison_Select]![bill1] & " Or (tbl_Formulas.BillType)=" & [Forms]![frm_BulkComparison_Select]![bill2] & "...….

(this example assumes these values are numeric, if text then also include the text delimiters. Also assumes your frm_BulkComparison_Select is open

Also not sure without testing but seem to recall you cannot reference a form as you have when opening a recordset - you use the basis I'm suggesting above

[Micron]

Also note that rs1, rs2, rs3, Lrow, rng are all variables of the type Variant because they are not explicitly declared; e.g.
Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset, rs3 As DAO.Recordset - for any variable type. Best to do so even if all variables are supposed to be variants anyway. That way, anyone trying to follow your code has no doubt about your declaration intentions.

As for qdf.SQL = rs1, it probably will work because of the above. rs1 is a variant, thus will accept a string which user can assign to query object sql property, not that it's good coding to do so. Seems like a lot of code instead of running a stored query. Parameters can be assigned to a query object, which ought to be a workable solution. Otherwise the parameters have to be assigned in the code, or use the concatenation approach as suggested.

[ssanfu]

I was going to mention the need to explicitly declare variables, but Micron beat me again.
But you should be aware that "INPUT" is a reserved word and shouldn't be used for object names.

I reformatted your code (as Ajax and Micron suggested) and added parentheses for AND/OR grouping :

    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim rs3 As DAO.Recordset
    Dim rs4 As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim cnt As Integer
    Dim Lrow As Long
    Dim Lrow1 As Long
    Dim sSQL As String

    Dim appExcel As Excel.Application
    Dim wbk As Excel.Workbook
    Dim wks As Excel.Worksheet
    Dim rng As Excel.Range
    Dim 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")

    sSQL = "TRANSFORM Sum(tbl_Formulas.Input) AS SumOfInput"
    sSQL = sSQL & " SELECT tbl_Formulas.BillType, tbl_Formulas.RawMaterial"
    sSQL = sSQL & " FROM tbl_Formulas"
    sSQL = sSQL & " WHERE ("
    sSQL = sSQL & " tbl_Formulas.BillType = '" & [Forms]![frm_BulkComparison_Select]![bill1] & "' Or"
    sSQL = sSQL & " tbl_Formulas.BillType = '" & [Forms]![frm_BulkComparison_Select]![bill2] & "' Or"
    sSQL = sSQL & " tbl_Formulas.BillType = '" & [Forms]![frm_BulkComparison_Select]![bill3] & "')"
    sSQL = sSQL & " And "
    sSQL = sSQL & " (tbl_Formulas.Item = '" & [Forms]![frm_BulkComparison_Select]![Item 1] & "' Or"
    sSQL = sSQL & " tbl_Formulas.Item = '" & [Forms]![frm_BulkComparison_Select]![Item 2] & "' Or"
    sSQL = sSQL & " tbl_Formulas.Item = '" & [Forms]![frm_BulkComparison_Select]![Item 3] & "' Or"
    sSQL = sSQL & " tbl_Formulas.Item = '" & [Forms]![frm_BulkComparison_Select]![Item 4] & "' Or"
    sSQL = sSQL & " tbl_Formulas.Item = '" & [Forms]![frm_BulkComparison_Select]![Item 5] & "' Or"
    sSQL = sSQL & " tbl_Formulas.Item = '" & [Forms]![frm_BulkComparison_Select]![Item 6] & "' Or"
    sSQL = sSQL & " tbl_Formulas.Item = '" & [Forms]![frm_BulkComparison_Select]![Item 7] & "' Or"
    sSQL = sSQL & " tbl_Formulas.Item = '" & [Forms]![frm_BulkComparison_Select]![Item 8] & "' Or"
    sSQL = sSQL & " tbl_Formulas.Item = '" & [Forms]![frm_BulkComparison_Select]![Item 9] & "' Or"
    sSQL = sSQL & " tbl_Formulas.Item = '" & [Forms]![frm_BulkComparison_Select]![Item 10] & "' Or"
    sSQL = sSQL & " tbl_Formulas.Item = '" & [Forms]![frm_BulkComparison_Select]![Item 11] & "' Or"
    sSQL = sSQL & " tbl_Formulas.Item = '" & [Forms]![frm_BulkComparison_Select]![Item 12] & "' Or"
    sSQL = sSQL & " tbl_Formulas.Item = '" & [Forms]![frm_BulkComparison_Select]![Item 13] & "' Or"
    sSQL = sSQL & " tbl_Formulas.Item = '" & [Forms]![frm_BulkComparison_Select]![Item 14] & "' Or"
    sSQL = sSQL & " tbl_Formulas.Item = '" & [Forms]![frm_BulkComparison_Select]![Item 15] & "')"
    sSQL = sSQL & " AND "
    sSQL = sSQL & " (tbl_Formulas.BP = '" & [Forms]![frm_BulkComparison_Select]![BP] & "')"
    sSQL = sSQL & " GROUP BY tbl_Formulas.BillType, tbl_Formulas.RawMaterial"
    sSQL = sSQL & " PIVOT tbl_Formulas.Item;"
    Debug.Print sSQL

    Set rs1 = CurrentDb.OpenRecordset(sSQL)

    'qdf.SQL = rs1

...

[Micron]

YOU can't always be 1st! While you're at it, why not introduce aliasing? Also, if I'm not mistaken, as long as this is vba and we're using concatenation, we should be able to set a form object variable and get the value of a control pertaining to the form object as long as that form is open. Then code is more like unopened Campbells soup (condensed). Like

'first set of dims
Dim frm As Form
'more dims here
'same with Sets
Set frm = [Forms]![frm_BulkComparison_Select]

sSQL = "TRANSFORM Sum(tbl_Formulas.Input) AS SumOfInput"
sSQL = sSQL & " SELECT tF.BillType, tF.RawMaterial FROM tbl_Formulas AS tF WHERE"
sSQL = sSQL & " (tF.BillType = '" & frm![bill1] & "' Or tF.BillType = '" & frm![bill2] & "' Or"
sSQL = sSQL & " tF.BillType = '" & frm![bill3] & "') And (tF.Item = '" & frm![Item 1] & "' Or"
sSQL = sSQL & " tF.Item = '" & frm![Item 2] & "' Or tF.Item = '" & frm![Item 3] & "' Or"
sSQL = sSQL & " tF.Item = '" & frm![Item 4] & "' Or tF.Item = '" & frm![Item 5] & "' Or"
sSQL = sSQL & " tF.Item = '" & frm![Item 6] & "' Or tF.Item = '" & frm![Item 7] & "' Or"
sSQL = sSQL & " tF.Item = '" & frm![Item 8] & "' Or tF.Item = '" & frm![Item 9] & "' Or"
sSQL = sSQL & " tF.Item = '" & frm![Item 10] & "' Or tF.Item = '" & frm![Item 11] & "' Or"
sSQL = sSQL & " tF.Item = '" & frm![Item 12] & "' Or tF.Item = '" & frm![Item 13] & "' Or"
sSQL = sSQL & " tF.Item = '" & frm![Item 14] & "' Or tF.Item = '" & frm![Item 15] & "')"
sSQL = sSQL & " AND (tF.BP = '" & frm![BP] & "') GROUP BY tF.BillType, tF.RawMaterial"
sSQL = sSQL & " PIVOT tF.Item;"

[ssanfu]

Dang! Now you ruined it.

Aliasing was going to be the next lesson! .......

Hmmmmm... instead of all of those "OR"s, what about using the IN operator??

--- Untested ---

    'Other dim statements
    Dim Or1 As String
    Dim Or2 As String

   "Excel Dim statements
   "Excel Set statements

    appExcel.Visible = False

    cnt = 1

    Set qdf = CurrentDb.QueryDefs("Qry_Comparison_Bulk")
    Set frm = [Forms]![frm_BulkComparison_Select]

    Or1 = " ('" & frm![bill1] & "', '" & frm![bill2] & "', '" & frm![bill3] & "')"

    Or2 = " ('" & frm![Item 1] & "', '" & frm![Item 2] & "', '" & frm![Item 3] & "', '"
    Or2 = Or2 & frm![Item 4] & "', '" & frm![Item 5] & "', '" & frm![Item 6] & "', '"
    Or2 = Or2 & frm![Item 7] & "', '" & frm![Item 8] & "', '" & frm![Item 9] & "', '"
    Or2 = Or2 & frm![Item 10] & "', '" & frm![Item 11] & "', '" & frm![Item 12] & "', '"
    Or2 = Or2 & frm![Item 13] & "', '" & frm![Item 14] & "', '" & frm![Item 15] & "')"

    sSQL = "TRANSFORM Sum(tbl_Formulas.Input) AS SumOfInput"
    sSQL = sSQL & " SELECT tF.BillType, tF.RawMaterial FROM tbl_Formulas AS tF"
    sSQL = sSQL & " WHERE"
    sSQL = sSQL & " (tF.BillType IN " & Or1
    sSQL = sSQL & " AND "
    sSQL = sSQL & " tF.Item IN " & Or2
    sSQL = sSQL & " AND "
    sSQL = sSQL & " AND (tF.BP = '" & frm![BP] & "')"
    sSQL = sSQL & " GROUP BY tF.BillType, tF.RawMaterial"
    sSQL = sSQL & " PIVOT tF.Item;"
    ...

[Micron]

Interesting even if it doesn't work.

Aliasing was going to be the next lesson!

Then I guess getting the value from a child of an object (control object on a form object) wasn't part of the curriculum?
Hope that approach is OK; I don't like the taste of crow.

[ssanfu]

getting the value from a child of an object (control object on a form object) wasn't part of the curriculum?

Well, yes, it is. But next semester.

I don't like the taste of crow

Having had the opportunity (aka forced) to try it a couple of times, it's not bad ... with lots and lots of BBQ sauce.
But I don't recommend trying it often. Maybe once every 20 or 30 years.

Its now time for me to go and get shamed by a group of 7 - 10 year olds. Sigh...