barrysprout
MIS
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.
‘set the record source for the report
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())-4) 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())-4)" & _
" 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
End Select
Next
rst.Clone
Set rst = Nothing
