Cross-Tab Report

I am having some trouble with executing a report. The code actually executes just fine, but when I go to open the report I get an error telling me that "Cannot use the crosstab of a non-fixed column as a subquery." Now, when I simply create a crosstab query and base the report off it the report opens just fine. The data feeding the crosstab query is simply a table so no sources of error there . And to answer, the obvious of why not just build it manually then is because I am using VBA to create dynamic reports that will all be based off of similar data and I will have +25 different reports but with the same structure so doing it through VBA will provide more flexibility long-term if any changes need to be made to all or one of the reports. Any idea of what may be triggering the error?
Sub SectorMarketValueReport()
Dim db As DAO.Database ' database object
Dim rs As DAO.Recordset ' recordset object
Dim fld As DAO.Field ' recordset field
Dim grpLevelOne As Variant
Dim grpLevelTwo As Variant
Dim txtNew As Access.TextBox ' textbox control
Dim lblNew As Access.Label ' label control
Dim rpt As Report ' hold report object
Dim lngTop, lngLeft, lngWidth, lngHeight As Long
Dim title, fieldname, Portfolio, strReportName As String
'Recordsource is crossTab query
strSQL = "TRANSFORM Sum(tblReport_SectorDiff_MV.MV_DIFF) AS SumOfMV_DIFF SELECT tblReport_SectorDiff_MV.Report_Order, tblReport_SectorDiff_MV.Sector, "
strSQL = strSQL & "tblReport_SectorDiff_MV.SubSector FROM tblReport_SectorDiff_MV GROUP BY "
strSQL = strSQL & "tblReport_SectorDiff_MV.Report_Order, tblReport_SectorDiff_MV.Sector, tblReport_SectorDiff_MV.SubSector "
strSQL = strSQL & "PIVOT tblReport_SectorDiff_MV.PortName;"

     'set the title
     title = "Portfolio vs Barclay's Index Sector MarketValue Difference"
     ' initialise position variables
     lngLeft = 521
     lngTop = 28
     lngWidth = 583
     lngHeight = 208
     'Create the report
     strReportName = "SectorsMV"
     Set rpt = CreateReport


     ' set properties of the Report
     With rpt
         .Width = 9500
         .RecordSource = strSQL
         .Caption = title
         .OrderBy = False
         .OrderByOn = False
         .FilterOn = False
     End With
     'Set page set-up
     With rpt.Printer
        .Orientation = acPRORLandscape
        .LeftMargin = 0.25
        .RightMargin = 0.25
     End With

     ' Open SQL query as a recordset
     Set db = CurrentDb
     Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
     'Create Report Groupings and Sorting
     grpLevelOne = CreateGroupLevel(rpt.Name, "Report_Order", False, False)
     grpLevelTwo = CreateGroupLevel(rpt.Name, "Sector", True, False)
     'Set the sort order by the report order textbox value
     rpt.GroupLevel(0).SortOrder = False

     ' Create Textbox for Group Header Title
     fieldname = "Sector"
         Set txtNew = CreateReportControl(rpt.Name, acTextBox, acGroupLevel2Header, , fieldname, 0, lngTop, lngWidth + 1000, lngHeight + 50)
         'Format Control
         With txtNew
            .FontSize = 10
            .FontWeight = 600
         End With
       ' Create Textbox for Row title in Detail section
       fieldname = "SubSector"
         Set txtNew = CreateReportControl(rpt.Name, acTextBox, acDetail, , fieldname, lngLeft, lngTop, lngWidth + 1500, lngHeight)
         'Format Control
         With txtNew
            .FontSize = 8
            .FontWeight = 400
         End With
     ' Create corresponding text box controls for each portfolio.
    lngLeft = lngLeft + 1500
    NoOfFields = 21
    For x = 4 To NoOfFields - 1
      Portfolio = rs.Fields(x).Name
         ' Create new text box control and size to fit data.
         Set txtNew = CreateReportControl(rpt.Name, acTextBox, acDetail, , Portfolio, lngLeft, lngTop, lngWidth, lngHeight)

         With txtNew
            .FontSize = 8
            .FontWeight = 400
            .Format = "Percent"
            .DecimalPlaces = 2
         End With

         ' Increment top value for next control
         lngLeft = lngLeft + 650
    Next x

    'Set the Detail Height
    rpt.Section(acDetail).Height = 164
     ' Create datestamp in Footer
     Set lblNew = CreateReportControl(rpt.Name, acLabel, acPageFooter, , Now(), 0, 0)
     ' Open new report.
     DoCmd.Close acReport, title, acSavePrompt
     'reset all objects
     Set rs = Nothing
     Set rpt = Nothing
     Set db = Nothing
End Sub

Open in new window

Who is Participating?
You understand the problem, then simplify it in a sample database that reflects the issu and attach.
Simplify = few fields involved, and only necessary objects.
The "Cannot use the crosstab of a non-fixed column as a subquery" is the key - Access gets hinky when it thinks there *might* be some additional (or fewer) columns the next time it runs. When you're using it directly into a report, the report can see "oh, I've got 5 columns, so I only need to worry about those 5 all the time..."

Try making a temp table out of your crosstab each time you run the report series, and use a select statement against that resulting temp table for the report. You'll want to make sure you're doing regular compact/repairs on the DB if you do, because each creation of the maketable expands the DB size but doesn't auto-contract itself once deleted.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.