Access VBA - operation is not allowed when object is closed

Near the end you'll see >>>>> It's happening here <<<<<<<   what I'm trying to do is check to see if there are any records returned from the sql  if so step into the IF statement and produce an excel spreadsheet.  I think it has to do with open and closing the recordset... not sure though.  Should I just create a new recordset instead of re-using RST from the code above (which works great)

Option Compare Database
Public Sub ExportWorkPapers()
    On Error GoTo ErrorHandler

    ' connection and recordset variables
    Dim rst As ADODB.Recordset
    Dim Cnxn As ADODB.Connection
     ' array variable
    Dim arrAssessors As Variant
    ' detail variables
    Dim strMessage As String
    Dim intRows As Integer
    Dim intRecords As Integer
    Dim strSQL As String
    Dim strTheAssessor As String
    Dim oFSystem As Object
    Dim oFolder As Object
    Dim oFile As Object
    Dim sFolderPath As String
    Dim Processes As String
    Set rst = New ADODB.Recordset
    Set Cnxn = New ADODB.Connection
    Set Cnxn = CurrentProject.Connection
    DoCmd.SetWarnings False
   
' Get Folder for Files
    Set dlgOpen = Application.FileDialog(msoFileDialogFolderPicker)
    dlgOpen.Show
    sFolderPath = dlgOpen.SelectedItems(1)
    Set oFSystem = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSystem.GetFolder(sFolderPath)
         
    '+++++++++++++++++++++++++++++++++++++++++
    'Update workpapers with ReturnAssetID from AMLTDATA
    '+++++++++++++++++++++++++++++++++++++++++
   
    strSQL = "UPDATE AMLTData INNER JOIN Workpapers ON AMLTData.VIN = Workpapers.VIN SET Workpapers.ReturnAssetID = AMLTDATa.ReturnAssetID"

  '  DoCmd.RunSQL strSQL
 
   
   
    '++++++++++++++++++++++++++++++++++++++++
   
    'Create Distinct Assessor List to Loop Through
    'Removes assessors where the ReturnAssetID is null
             
    '++++++++++++++++++++++++++++++++++++++++
   'SQL
   strSQL = "SELECT DISTINCT Workpapers.ActualAssessorID FROM Workpapers WHERE Workpapers.ReturnAssetID Is Not Null OR Len(Workpapers.ReturnAssetID) > 0"
    'Open Connection and and place sql result into an array
'MsgBox strSQL
    rst.Open strSQL, Cnxn, adOpenKeyset, adLockOptimistic
'MsgBox "gets here"
    ' Put Data into Array
    initRecords = rst.RecordCount
    arrAssessors = rst.GetRows(initRecords)
   
   

   ' MsgBox initRecords  ' Assessor Count
   ' MsgBox sFolderPath  ' Destination Path selected
   
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Loop through Assessors and output spreadsheets for each assessor found
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    Dim fileCounter As Integer
    fileCounter = 0
    Dim x As Integer
    For x = 0 To initRecords - 1
        'Get Assessor Name in position X in the arrary arrAssessors
            strTheAssessor = arrAssessors(0, x)
 
        ' this SQL will create new table with contents
 
            strSQL = "SELECT Workpapers.ReturnAssetID, Workpapers.VIN, Workpapers.ParcelAccount, Workpapers.InitialValue, Workpapers.FinallAssessedValue, Workpapers.AssetStatusCodeID, Workpapers.ActualAssessorID INTO UpdateExport FROM Workpapers WHERE (((Workpapers.ActualAssessorID)='" & strTheAssessor & "') AND (Workpapers.ReturnAssetID Is Not Null OR Len(Workpapers.ReturnAssetID) > 0))"

    'MsgBox Sql ' Display SQL stmt
            DoCmd.RunSQL strSQL
       
    'Create Spreadsheet
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "updateExport", sFolderPath & "\WorkPaper_LoadFile_" & strTheAssessor & ".xls", True
            fileCounter = fileCounter + 1 ' Sets count of files created for message box
    Next x
 >>>>> It's happening here <<<<<<<  
    rst.Close
    Set rst = New ADODB.Recordset
   
 'Check for Exceptions
    CurrentDb.Execute "Drop table Exceptions"
    strSQL = "SELECT Workpapers.ReturnAssetID, Workpapers.VIN, Workpapers.ParcelAccount, Workpapers.ActualAssessorID, Workpapers.InitialValue, Workpapers.FinallAssessedValue, Workpapers.AssetStatusCodeID INTO Exceptions FROM Workpapers WHERE Workpapers.ReturnAssetID Is Null OR Len(ReturnAssetID)= 0"
    rst.Open strSQL, Cnxn, adOpenKeyset, adLockOptimistic
    initRecords = rst.RecordCount
    arrAssessors = rst.GetRows(initRecords)
   
If initRecords > 0 Then
DoCmd.RunSQL strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "exceptions", sFolderPath & "\_WorkPaper_Exceptions.xls", True

Else
initRecords = 0
End If
   
    MsgBox fileCounter & "  Files Created. " & initRecords & " Exceptions"
   
   ' clean up
    rst.Close
    Cnxn.Close
    Set rst = Nothing
    Set Cnxn = Nothing
   
    Exit Sub
   
ErrorHandler:
    ' clean up
    If Not rst Is Nothing Then
        If rst.State = adStateOpen Then rst.Close
    End If
    Set rst = Nothing
   
    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing
   
    If Err <> 0 Then
        MsgBox "Error " & Err.Number & " - " & Err.Description & " Line: " & Erl
    End If
End Sub
Start Free Trial