crosstab query is causing Error 3265 Item not found in this collection 1

[vba317]

I have a crosstab query that works great in access. I need to create a report from this query and I am getting error 3265. When I run the query I have no problems and there is data in all the cells. I have highlighted in blue where the error comes up. Any help is appreciated.

Tom

 Dim strClient As String
    Dim strFileLoc As String
    Dim strFile As String
    Dim strMonYr As String
    Dim intRpt As Integer
    Dim strUCI As String
    Dim strRptType As String
    Dim strSheet As String
    Dim rst As DAO.Recordset
    
    'Set up Template Information
    strClient = "SPN"
    strRptType = "Denial"
    strFileLoc = "Z:\Adhoc projects\" & strClient & "\" & strRptType & "\"
    strFile = "_Grid_RevCode.xlt"
    strSheet = strClient & "_DenialBalGrid_RevCode"
    
    Call xlOpen(strFileLoc, strClient, strFile, strSheet)
    'Set up Sheet Information
   intRpt = 1
   strUCI = "SPN"
   
   Call MonYr(strMonYr, intRpt, strUCI)
   ActiveSheet.Select
   goXl.Range("AE1").Value = strMonYr
   
   strSQL = "TRANSFORM Sum(CONS_RejSum_CurBal.Bal) AS Bal " & _
            "SELECT CONS_RejSum_CurBal.RevCode, CONS_RejSum_CurBal.RevDesc " & _
            "FROM CONS_RejSum_CurBal INNER JOIN DICT_ReviewRejection ON CONS_RejSum_CurBal.RevCode = DICT_ReviewRejection.RevCode " & _
            "WHERE (((DICT_ReviewRejection.Filter) = 0) And ((CONS_RejSum_CurBal.RptPd) > 372)) " & _
            "GROUP BY CONS_RejSum_CurBal.RevCode, CONS_RejSum_CurBal.RevDesc " & _
            "ORDER BY CONS_RejSum_CurBal.RevCode, CONS_RejSum_CurBal.RevDesc, CONS_RejSum_CurBal.InsRptCat " & _
            "PIVOT CONS_RejSum_CurBal.InsRptCat;"
          Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        If Not rst.EOF Then
            With rst
                .MoveLast
                .MoveFirst
            End With
            iRw = 4
            For Z = 1 To rst.RecordCount ' Add Data to Report
                With goXl.ActiveSheet
   [Blue]           .Cells(iRw, 1).Value = (rst![1])  [\Blue]
                    .Cells(iRw, 2).Value = (rst![2])
                    .Cells(iRw, 3).Value = (rst![3])
                    .Cells(iRw, 4).Value = (rst![4])
                    .Cells(iRw, 5).Value = (rst![5])
                    .Cells(iRw, 6).Value = (rst![6])
                    .Cells(iRw, 7).Value = (rst![7])
                    .Cells(iRw, 8).Value = (rst![8])
                    .Cells(iRw, 9).Value = (rst![9])
                    .Cells(iRw, 10).Value = (rst![10])
                    .Cells(iRw, 11).Value = (rst![11])
                    .Cells(iRw, 12).Value = (rst![12])
                    .Cells(iRw, 13).Value = (rst![13])
                    .Cells(iRw, 14).Value = (rst![14])
                    .Cells(iRw, 15).Value = (rst![15])
                    .Cells(iRw, 16).Value = (rst![16])
                    .Cells(iRw, 17).Value = (rst![17])
                    .Cells(iRw, 18).Value = (rst![18])
                   
                End With
                ' Move to Next Row
                iRw = iRw + 1
                rst.MoveNext
            Next Z
        End If
        rst.Close
        Set rst = Nothing
     
    strFileLoc = "Z:\_RptSets\OTHER\" & strClient
    Call xlSave(strFileLoc, strClient, strFile)

[\code]

 

[PHV]

Why not simply this ?

...
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
goXl.ActiveSheet.Range("A4").CopyFromRecordset rst
rst.Close
...

Anyway, get rid of this line: ActiveSheet.Select

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

I added Dim db As DAO.Database and Set db = CurrentDB

When I tried your code I get a RunTime error 3143 syntax error in transform statement.

 

[vba317]

I figured out what I was doing to get the error 3143 and I fixed it. Now I am missing the first row of information that comes from the column heading InsRptCat. Is there someting I can add to get that column heading?

Tom

 

[PHV]

In which row on the sheet do you the column heading ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

Row 2

 

[PHV]

Like this ?

...
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
With goXl.ActiveSheet
  For iCols = 0 To rst.Fields.Count - 1
    .Cells(2, iCols + 1).Value = rst.Fields(iCols).Name
  Next
  .Range("A4").CopyFromRecordset rst
End With
rst.Close
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

That solves it! Thanks