Runtime error 91 block variable not set

[vba317]

This program has 5 loops. When it goes through the second loop I get the error 91. The second loop has no records in it and that is what I am expecting. I have moved the end if associated with the following code
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If Not rst.EOF Then
With rst
.MoveLast
.MoveFirst
End With

to many different spots but I can't get the code to work. What I want to happen is if the If Not rst.EOF Then statement is executed I want the code to execute the rstCPT.MoveNext And the next Y statements. I have tried different methods but I can't seem to get it working. Any help is appreciated.

Tom

Private Sub cmdXL_Click()

    Dim boolErr As Boolean
    Dim strSQL As String
    Dim strUCI As String
    Dim strMnth As String
    Dim strCPT As String
    Dim strFullCPT As String
    Dim rstCPT As Recordset
    Dim rst As Recordset
    Dim Z As Integer
    Dim iRw As Integer
    Dim strRefProv As String
    Dim rstDat As Recordset
    Dim Y As Integer
    Dim strRptType As String
    Dim strFileType As String
    ' OPEN EXCEL
    Call xlCreate
    If goxlPresent = True Then
        ' Get List of CPT to Process
        strSQL = "SELECT cpt,cptdesc FROM [_CPTToProcess] ORDER BY cpt;"
        Set rstCPT = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        With rstCPT
            .MoveLast
            .MoveFirst
        End With
        For Y = 1 To rstCPT.RecordCount    ' Cycle through CPT List
            strCPT = (rstCPT![CPT])
            strFullCPT = (rstCPT![CPT]) & " - " & (rstCPT![cptdesc])
            strSQL = "SELECT impID,uci,monasdt,PatName,AcctNu,doschg,chgamt,amt " & _
                     "FROM RPT_Export " & _
                     "WHERE (cptcode = '" & (strCPT) & "') " & _
                     "ORDER BY PatName,doschg;"
            Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
            If Not rst.EOF Then
                With rst
                    .MoveLast
                    .MoveFirst
                End With
           
            ' **********************
            ' *** CREATE INVOICE ***
            ' **********************
            strUCI = (rst![uci])
            strMnth = (rst![impID])
            iRw = 7
            ' Open Template
            With goxl
  [Blue]              .Workbooks.Open FileName:="\\salmfilesvr1\Public\Client Services\AutoRpts\_RptSets\OTHER\" & (strUCI) & "\Tools\Invoice.xlt" [/Blue]
                .Sheets("Invoice").Select
            End With
            With goxl.ActiveSheet
                .Cells(1, 1).Value = strUCI
                .Cells(3, 1).Value = (rst![monasdt])
                .Cells(5, 1).Value = strFullCPT
            End With
            For Z = 1 To rst.RecordCount    ' Add Referring Providers to Report
                With goxl.ActiveSheet
                    .Cells(iRw, 1).Value = (rst![PatName])
                    .Cells(iRw, 2).Value = (rst![AcctNu])
                    .Cells(iRw, 3).Value = (rst![doschg])
                    .Cells(iRw, 4).Value = (rst![chgamt])
                    .Cells(iRw, 5).Value = (rst![amt])
                End With
                ' Move to Next Row
                iRw = iRw + 1
                rst.MoveNext
            Next Z
            rst.Close
            Set rst = Nothing
            ' Delete Extra Rows
            With goxl
                .Rows("" & (iRw) & ":1000").Select
                .Selection.Delete Shift:=xlUp
                .Cells(4, 1).Select
            End With
           
            'Save Workbook
            strFileType = "_Inv_" & (strCPT) & "_"
            strUCI = "PRM"
            Call xlSave(strUCI, strFileType)
            End If
            rstCPT.MoveNext
           
        Next Y
        rstCPT.Close
        Set rst = Nothing
    Else
        MsgBox "Can't create Excel Object", vbOKOnly, "Excel not found"
    End If
    

    ' Close Excel Instance
    Call xlKill
    ' Message it is closed
    MsgBox "Referring Report Completed.", , "Done!"

End Sub

 

[Andrzejek]

You have nicely aligned code, so:

            Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
            If Not rst.EOF Then
                With rst
                    .MoveLast
                    .MoveFirst
                End With
           [red]End If[/red]
            ' **********************
            ' *** CREATE INVOICE ***
            ' **********************

Have fun.

---- Andy

 

[vba317]

Andy,
Thanks for your input. That was the first place I tried and what happens is the code will skip the following part

      With rst
         .MoveLast
         .MoveFirst
      End With

and then I get the error at the .Workbooks.Open FileName:="\\salmfilesvr1\Public\Client Services\AutoRpts\_RptSets\OTHER\" & (strUCI) & "\Tools\Invoice.xlt" part.

Tom

 

[SkipVought]

hi,

Maybe...

On Error Resume Next

With rst

  .MoveFirst

  if err.number = 0 then
     do
     'loop thru rst here
     '........
        .MoveNext
     loop until .eof
  else
     err.clear
     On Error GoTo 0
  end if

End With

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[vba317]

Skip trying your solution now I get a compile error at the Next Y.

 

[SkipVought]

Well i didn't have a y variable.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PHV]

What about this (in your original code)?

...
    Set rstCPT = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    [!]If Not rstCPT.EOF Then[/!]
        With rstCPT
...
        Next Y
    [!]End If[/!]
    rstCPT.Close
...

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

 

[vba317]

PHV,
I removed Skip's suggestion and put the original code in the rst loop. I than tried your solution and now I get a compile error next without for error and the next Y is highlighted.
Tom

 

[vba317]

Sorry for some reason I commented out the end if at this point
strUCI = "PRM"
Call xlSave(strUCI, strFileType)
End If
rstCPT.MoveNext
I removed the comment and now the code compiles OK, but I get the error 91 at the
.Workbooks.Open FileName:="\\salmfilesvr1\Public\Client Services\AutoRpts\_RptSets\OTHER\" & (strUCI) & "\Tools\Invoice.xlt"

again

Tom

 

[PHV]

Well, here your whole procedure:

rivate Sub cmdXL_Click()

Dim boolErr As Boolean
Dim strSQL As String
Dim strUCI As String
Dim strMnth As String
Dim strCPT As String
Dim strFullCPT As String
Dim rstCPT As Recordset
Dim rst As Recordset
Dim Z As Integer
Dim iRw As Integer
Dim strRefProv As String
Dim rstDat As Recordset
Dim Y As Integer
Dim strRptType As String
Dim strFileType As String
' OPEN EXCEL
Call xlCreate
If goxlPresent = True Then
    ' Get List of CPT to Process
    strSQL = "SELECT cpt,cptdesc FROM [_CPTToProcess] ORDER BY cpt;"
    Set rstCPT = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    [!]If Not rstCPT.EOF Then[/!]
        With rstCPT
            .MoveLast
            .MoveFirst
        End With
        For Y = 1 To rstCPT.RecordCount    ' Cycle through CPT List
            strCPT = (rstCPT![CPT])
            strFullCPT = (rstCPT![CPT]) & " - " & (rstCPT![cptdesc])
            strSQL = "SELECT impID,uci,monasdt,PatName,AcctNu,doschg,chgamt,amt " & _
                     "FROM RPT_Export " & _
                     "WHERE (cptcode = '" & (strCPT) & "') " & _
                     "ORDER BY PatName,doschg;"
            Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
            If Not rst.EOF Then
                With rst
                    .MoveLast
                    .MoveFirst
                End With

                ' **********************
                ' *** CREATE INVOICE ***
                ' **********************
                strUCI = (rst![uci])
                strMnth = (rst![impID])
                iRw = 7
                ' Open Template
                With goxl
                    .Workbooks.Open FileName:="\\salmfilesvr1\Public\Client Services\AutoRpts\_RptSets\OTHER\" & (strUCI) & "\Tools\Invoice.xlt"
                    .Sheets("Invoice").Select
                End With
                With goxl.ActiveSheet
                    .Cells(1, 1).Value = strUCI
                    .Cells(3, 1).Value = (rst![monasdt])
                    .Cells(5, 1).Value = strFullCPT
                End With
                For Z = 1 To rst.RecordCount    ' Add Referring Providers to Report
                    With goxl.ActiveSheet
                        .Cells(iRw, 1).Value = (rst![PatName])
                        .Cells(iRw, 2).Value = (rst![AcctNu])
                        .Cells(iRw, 3).Value = (rst![doschg])
                        .Cells(iRw, 4).Value = (rst![chgamt])
                        .Cells(iRw, 5).Value = (rst![amt])
                    End With
                    ' Move to Next Row
                    iRw = iRw + 1
                    rst.MoveNext
                Next Z
                rst.Close
                Set rst = Nothing
                ' Delete Extra Rows
                With goxl
                    .Rows("" & (iRw) & ":1000").Select
                    .Selection.Delete Shift:=xlUp
                    .Cells(4, 1).Select
                End With

                'Save Workbook
                strFileType = "_Inv_" & (strCPT) & "_"
                strUCI = "PRM"
                Call xlSave(strUCI, strFileType)
            End If
            rstCPT.MoveNext

        Next Y
    [!]End If[/!]
    rstCPT.Close
    Set rst = Nothing
Else
    MsgBox "Can't create Excel Object", vbOKOnly, "Excel not found"
End If


' Close Excel Instance
Call xlKill
' Message it is closed
MsgBox "Referring Report Completed.", , "Done!"

End Sub

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

 

[SkipVought]

so do you have a template at...
[tt]
\\salmfilesvr1\Public\Client Services\AutoRpts\_RptSets\OTHER\PRM\Tools\Invoice.xlt
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PHV]

Are you sure that goxl is a valid object correctly instanciated to an Excel.Application ?

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

 

[SkipVought]

...and PHV's question goes for variable goxlPresent as well.

goxl & goxlPresent probably should be declared as Public variables.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[vba317]

I realized the error in the program.
In the beginning
This code the excel spreadsheet
Call xlCreate
If goxlPresent = True Then

But then this code closes the excel instance

rst.Cose
Set rst = Nothing
rstCPT.Close

so on the second loop there is no excel instance to open.

So I am adding code to open a new instance on the second loop.

 

[PHV]

How closing recordsets may close an excel instance ????