troubleshooting Question

Cross-Tab Report

Avatar of ExcelKid1081
ExcelKid1081 asked on
Microsoft Access
2 Comments1 Solution497 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros