Access 2003 Recordset Compile error invalid or unqualified reference

[vba317]

I am getting a compile error when I try and compile the module I am working in. I have tried to rename the record set. I know the record set is qualified from the query so I don't know why I am getting this error.The insurance in brackets is highlighted when I get the error. I thought I qualified the reference in the following code, I.insdesc & ' (' & I.insmne & ')' AS Insurance . I have highlighted in Blue where the error is happening. Any help is appreciated. Tom

Private Sub cmdXL_Click()
    Dim sUCI As String
    Dim sMon As String
    Dim sCY As String
    Dim sFileLoc As String
    Dim sFile As String
    Dim sSheet As String
    Dim sFileType As String
    Dim iRw As Integer
    Dim sSQL As String
    Dim rstA As DAO.Recordset
    Dim rstP As DAO.Recordset
    Dim I As Integer
    Dim iTRow As Integer
    Dim iLCol As Integer
    Dim iRCol As Integer
    Dim P As Integer
    Dim X As Integer

    'MSP_Assists
    sUCI = "MSP"
    Call CurMonYrL(sUCI, sMon, sCY)
    sFileLoc = "\\salmfilesvr1\public\Client Services\AutoRpts\_RptSets\OTHER\MSP\Tools\"
    sFile = "MSP_Assists"
    sSheet = "Assists_All"
    sFileType = ".xlt"
    Call xlOpen(sFileLoc, sUCI, sFile, sSheet, sFileType)
    Range("A2").Value = sMon & " " & sCY
    iRw = 5
    'Data Assists for all providers
    'Original code
    'Get List of Providers
    sSQL = "SELECT p.provname " & _
           "FROM dbo_rpt_dic_Prov p " & _
           "WHERE p.clntid = 41 " & _
           "ORDER BY p.provname;"
    Set rstP = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
    With rstP
        .MoveLast
        .MoveFirst
    End With
    For X = 1 To rstP.RecordCount
        goXl.ActiveSheet.Cells(iRw, 1).Value = (rstP![provname])
        iRw = iRw + 1
        sSQL = "SELECT D.rptdpt AS Department, z.monstr AS BillMonth, P.rptprov AS Provider, I.insdesc & ' (' & I.insmne & ')' AS Insurance" _
             & ", Sum(A.Proc) AS Assists, Sum(A.ChgAmt) AS Chgs, Sum(A.WorkRVU) AS RVU" _
             & "  FROM (((DATA_AssistData A INNER JOIN z_ProcessPds z ON A.rptpd=z.rptpd) INNER JOIN DICT_Dpt D ON A.Dpt=D.dptid)" _
             & " INNER JOIN DICT_Prov P ON A.Prov=P.provid) INNER JOIN DICT_Ins I ON A.PrimInsMne=I.insmne" _
             & " WHERE z.rptpddiff=1" _
             & " GROUP BY D.rptdpt, z.monstr, P.rptprov, I.insdesc, I.insmne" _
             & " ORDER BY D.rptdpt, z.monstr, P.rptprov, I.insdesc, I.insmne;"
        Debug.Print sSQL
        Set rstA = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
        If Not rstA.EOF Then
            With rstA
                .MoveLast
                .MoveFirst
            End With
        End If
        For I = 1 To rstA.RecordCount
[Blue]      .Cells(iRw + 1, 2).Value = (rstA![Insurance]) [/Blue]
            .Cells(iRw + 1, 3).Value = (rstA![Assists])
            .Cells(iRw + 1, 4).Value = (rstA![Chgs])
            .Cells(iRw + 1, 5).Value = (rstA![RVU])
            iRw = iRw + 1
            rstA.MoveNext
        Next I
    End With
    iRw = iRw + 1

Next X
iTRow = 5
iLCol = 2
iRCol = 5
Call xlFmtBotThinLine(iTRow, iLCol, iRCol)
' Totals
With goXl.ActiveSheet
    .Cells(iRw, 2).Value = "Total "
    .Cells(iRw, 3).Formula = "=SUM(C" & (iRw - 1) & ":C" & (iRw) & ")"
    .Cells(iRw, 4).Formula = "=SUM(D" & (iRw - 1) & ":D" & (iRw) & ")"
    .Cells(iRw, 5).Formula = "=SUM(E" & (iRw - 1) & ":E" & (iRw) & ")"
End With
' Format

Call xlFmtBotThinLine(iTRow, iLCol, iRCol)

rstP.Close            'Close what you opened.
rstA.Close

Set rstP = Nothing
Set rstA = Nothing


MsgBox "WHOO! Source files are created.", , "Completed."

End Sub

 

[vba317]

I just saw the error of my ways I needed to add a with and an End with statement

With goXl
.Cells(iRw + 1, 2).Value = (rstA![Insurance])
.Cells(iRw + 1, 3).Value = (rstA![Assists])
.Cells(iRw + 1, 4).Value = (rstA![Chgs])
.Cells(iRw + 1, 5).Value = (rstA![RVU])
iRw = iRw + 1
rstA.MoveNext
End With



This fixed this issue.

 

[PHV]

Which error message ?
In fact your code is messy (End with without corresponding With)

...
    End If
    [highlight #FCE94F]With goXL.ActiveSheet[/highlight]
        For I = 1 To rstA.RecordCount
...

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