‘Cannot use the crosstab of a non-fixed column as a subquery’ Error 1

[barrysprout]

‘Cannot use the crosstab of a non-fixed column as a subquery’ Error

I have a dynamic crosstab report that displays a rolling five year sales history. This works fine when the record source is a saved crosstab query. However when I try to set the record source in code, I get the “Cannot use the crosstab of a non-fixed column as a subquery” error. I have removed the ORDER BY statement but this doesn’t make any difference. The code is as follows and works off the Open Report event. The column names are then set with a similar version of this code activated by the On Format event.

Any ideas? Many thanks in advance.


Dim db As DAO.Database
Dim sqlrsrc As String

Set db = CurrentDb
sqlrsrc = "PARAMETERS Forms![ChHist]![Country] Text ( 255 ), [Forms]![ChHist]![Cat] Text ( 255 );" & _
" TRANSFORM Sum(ProdShipped.TSQty) AS SumOfTSQty" & _
" SELECT ProdDist.SortCode, IIf(IsNull([SSize]),[SProduct] & chr(160) & [SCase],[SProduct] & chr(160) & [SCase] & ""x"" & [Pack] & ""x"" & [SSize] & [SSizeUnit]) AS Item, Orders.Country" & _
" FROM (Orders INNER JOIN Shipments ON Orders.OrderNo = Shipments.OrderNo) LEFT JOIN (ProdShipped LEFT JOIN ProdDist ON ProdShipped.SProdCode = ProdDist.ProdCode) ON Shipments.ShipID = ProdShipped.ShipID" & _
" WHERE (((Orders.Country)=[Forms]![ChHist]![Country]) AND ((Format([Shipped],""yyyy"")) Between Year(Now()) And Year(Now())-9) AND ((ProdDist.Category)=[Forms]![ChHist]![Cat]) AND ((ProdDist.DistID)=[orders]![distid]))" & _
" GROUP BY ProdDist.SortCode, IIf(IsNull([SSize]),[SProduct] & chr(160) & [SCase],[SProduct] & chr(160) & [SCase] & ""x"" & [Pack] & ""x"" & [SSize] & [SSizeUnit]), Orders.Country, ProdDist.Category" & _
" ORDER BY ProdDist.SortCode" & _
" PIVOT Format([Shipped],""yyyy"");"

Me.RecordSource = sqlrsrc

'Set the control source for each data field

On Error GoTo ErrHand

Dim rst As DAO.Recordset
Dim i As Integer
Dim j As Integer
Dim sqltxt, sqlrsrc As String
Dim qdf As QueryDef

Set db = CurrentDb

sqltxt = "PARAMETERS [Country] CHAR, [Category] CHAR; " & _
" TRANSFORM Sum(ProdShipped.TSQty) AS SumOfTSQty" & _
" SELECT [SProduct] & [SCase] & [SSize] & [SSizeUnit] AS Item" & _
" FROM (Orders INNER JOIN Shipments ON Orders.OrderNo = Shipments.OrderNo) LEFT JOIN (ProdShipped LEFT JOIN ProdInfo ON ProdShipped.SProdCode = ProdInfo.ProdCode) ON Shipments.ShipID = ProdShipped.ShipID" & _
" WHERE Orders.Country = [Country] AND ProdInfo.Category = [Category] AND ((Format([Shipped],'yyyy')) Between Year(Now()) And Year(Now())-9)" & _
" GROUP BY Orders.Country, [SProduct] & [SCase] & [SSize] & [SSizeUnit]" & _
" PIVOT Format([Shipped], 'yyyy') "

Set qdf = db.CreateQueryDef("", sqltxt)
qdf.Parameters("Country") = [Forms]![ChHist]![Country]
qdf.Parameters("Category") = [Forms]![ChHist]![Cat]
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
j = j + 1
Select Case j
Case 0
Me.Field0.ControlSource = rst.Fields(i).Name
Case 1
Me.Field1.ControlSource = rst.Fields(i).Name
Case 2
Me.Field2.ControlSource = rst.Fields(i).Name
Case 3
Me.Field3.ControlSource = rst.Fields(i).Name
Case 4
Me.Field4.ControlSource = rst.Fields(i).Name
Case 5
Me.Field5.ControlSource = rst.Fields(i).Name

End Select
Next
rst.Clone
Set rst = Nothing

 

[PJStephenson]

Just a thought,is this causing your problems?

End Select
Next
rst.[bold]Clone[/bold]
Set rst = Nothing

[η][β][π]

 

[PHV]

Have you read this KB article ?

http://support.microsoft.com/default.aspx?scid=kb;EN-US;275070

I'd play with a saved QueryDef to avoid this error.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886