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

[Hack]

Your On Error GoTo should be the first line in the procedure. It should always be the first line in the procedure.

I suspect it is returning the 0's and, no error description, because the error trap isn't catching the error soon enough to tell you what is going on.

Also, there may, in fact, be no error at all. Your code will ALWAYS hit the error trap because you have no exit in your procedure.

Immedately before the line: PROC_ERR

there should be an Exit Sub. This way, if there is no error it will exit before it hits your error coding.

[eranfox]

Hello ksalholm,
Did you try to debug?

try to run your code step by step and you will find the line of code that is wrong.

Best Regards,
ERAN

[techgnome]

Hack's got it right, and a few more things:

1) the On Error Goto needs to be the first line in the sub
2) There is no error. Your lack of Exit Sub before the error handler causes the code in the error handler to run
3) You will always get 0 for the Erl even when there is an error. That's because you have no line numbers in the sub.

-tg

[ksalholm]

Aaah, I see. I didn't realise that the code executes even if no error is thrown. Thought it was like a basic try-catch statement.
Excellent to know.
Thanks a lot guys, you've been very informative.

Kate