Error Line: 0 / Error: (0)...What is my error??

[ksalholm]

Hi guys

I am using VBA to insert some data into a table should the appropriate record exist on a different table.
I am opening and closing a Recordset to retrieve record values which loops until it gets to the end of the file.
It goes through the entire loop fine, creates files etc., however, it seems to catch an error at the end and no matter what I try I can't seem to figure out what is causing it!
I have included the basics of the code...any help on this would be very much appreciated.
Thanks a lot

Kate

sTableName = "SELECT * FROM tblRestaurants"
 
 rs.Open sTableName, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
 rs.MoveFirst
 
 ' Loop to check firstly if the record exists
 ' If it does exist create the appropriate files
 
While Not rs.EOF
  
    counter = counter + 1
    strSQL = sTableName & " WHERE Number=" & counter
    rs2.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
  
        If rs2.EOF() <> True Then
            recordCount = recordCount + 1
        End If
 
        rs2.Close
        Set rs2 = Nothing
                  
        If recordCount <= 0 Then
    
                rs.MovePrevious
                
        Else
                strNewFileName = DLookup("[NewFileName]", "tblRestaurants", "[Number] = " & counter)
                
                    DoCmd.RunSQL "DELETE tmpAPFiles.* FROM tmpAPFiles;"
                    strSQL = "INSERT INTO tmpAPFiles ( UnitID, DocumentDate, RefNum, DocumentNumber, SupplierName, TotalAmount, Field7, GLCode, GLAmount, GLName, PaymentType, ManChequeNum, SupplierAccountNumber, SupplierID, PaymentType2, LastDayOfPWY )SELECT tblAPFiles.UnitID, tblAPFiles.DocumentDate, tblAPFiles.RefNum, tblAPFiles.DocumentNumber, tblAPFiles.SupplierName, tblAPFiles.TotalAmount, tblAPFiles.Field7, tblAPFiles.GLCode, tblAPFiles.GLAmount, tblAPFiles.GLName, tblAPFiles.PaymentType, tblAPFiles.ManChequeNum, tblAPFiles.SupplierAccountNumber, tblAPFiles.SupplierID, tblAPFiles.PaymentType2, tblAPFiles.LastDayOfPWY FROM tblRestaurants INNER JOIN tblAPFiles ON tblRestaurants.RestNum = tblAPFiles.UnitID WHERE (((tblRestaurants.Number)=" & counter & "));"
                    DoCmd.RunSQL strSQL
                    DoCmd.TransferText acExportDelim, , "qrytmpAPFiles", "w:\unitdata\polling\ap\fixed\" & strNewFileName, False
                    recordCount = 0
                    
                End If                              
        End If
    
 rs.MoveNext
    
Wend
 
 ' Check if the recordset has anything in it
 If (rs Is Nothing) Then
    If rs.State <> 0 Then
        rs.Close
        Set rs = Nothing
    End If
End If
 
 On Error GoTo PROC_ERR    
    
PROC_ERR:
      MsgBox "Error Line: " & Erl & vbCrLf & vbCrLf & "Error: (" & Err.Number & ") " & Err.Description, vbCritical
    Exit Function