Search code examples
sqlms-accessvbams-access-2016

Need help correcting my SQL for a crosstab query

I'm using the below code to export a crosstab query to excel.

I've tried Set rs1 = CurrentDb.OpenRecordset("qry_Comparison_Bulk") but I always get Error: Too few parameters. Expected 19. There are 19 parameters stored in that query. My next thought was to just recreate the SQL in VBA. Now I'm getting Error: Too many crosstab column headers (424). 424 is the total number of items in my db so I'm thinking that the filter isn't working appropriately.

Set rs1 = CurrentDb.OpenRecordset("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;")

There are '" & ... & "'delimiters to avoid the use of parameters as the values for each of the fields are text.

Further, my form is set up with 15 text boxes for "Item", 3 text boxes for "BillType", and 1 text box for "BP"

Solution

  • I'm guessing your qry_Comparison_Bulk query references form controls like this tbl_Formulas.BillType=[Forms]![frm_BulkComparison_Select]![bill1]

    And the query works when run from the Access query designer. The problem is that when you try to use that named query with OpenRecordset, each of those controls is treated as a parameter for which you have not supplied a value.

    So supply the values ...

    Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rs As DAO.Recordset
Set qdf = CurrentDb.QueryDefs("qry_Comparison_Bulk")
For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next
Set rs = qdf.OpenRecordset()

    prm.Name will be what Access interprets to be the parameter name ... like [Forms]![frm_BulkComparison_Select]![bill1] ... so Eval(prm.Name) is equivalent to Eval([Forms]![frm_BulkComparison_Select]![bill1]) ... and returns the value of that control ... which you then assign to prm.Value