[X]My Assistant

[aggiemarine07]

So I'm pretty new to building error handlers within my functions (and its actually been kind of eye opening personally as its allowed to find problem BEFORE they become a problem

I have the below code that I cobbled together that is supposed to import a bunch of excel files into one table in MS Access and I tried to put an error handler in it so that I could identify any files that were not able to be imported but for some reason, it's not flagging the import error that I should be getting from one file (the file in question has a typo in the field "Minimum Warehouse Order"; it says Mininum instead of Minimum). What am I not doing correctly in my code below? Thanks in advance!

CODE

Option Compare Database

Function Import_Excel(myPath As String, myFile As String, myExt As String, myTable As String, Optional myRange As String, Optional mySQLTable As String, Optional mySQLField As String)

DoCmd.RunSQL "DELETE * FROM WrongSheetErrorsTable"

On Error GoTo ErrHandler:
    errnum = Err.Number
    errtxt = Err.Description

Do While myFile <> ""
  If myFile Like myExt Then
    DoCmd.TransferSpreadsheet acImport, , myTable, myPath & myFile, True, myRange
    End If
  myFile = Dir()
Loop

If DCount("*", "WrongSheetErrorsQuery") > 0 Then
    DoCmd.OpenQuery "WrongSheetErrorsQuery"
        Else: MsgBox "All Good"
    End If

Exit Function

ErrHandler:
    Select Case errnum
        Case 0 'no error.
            Resume Next
        Case 91
            DoCmd.RunSQL "INSERT INTO [WrongSheetErrorsTable] ([FileName],[ErrorNumber],[ErrorText]) VALUES ('" & myFile & "','" & errnum & "','" & errtxt & "');"
            Resume Next
        Case 2391
            DoCmd.RunSQL "INSERT INTO [WrongSheetErrorsTable] ([FileName],[ErrorNumber],[ErrorText]) VALUES ('" & myFile & "','" & errnum & "','" & errtxt & "');"
            Resume Next
        Case Else 'All other errors.
            Resume Next
    End Select

End Function

[GroverParkGeorge]

This part is the problem:

CODE

On Error GoTo ErrHandler:
    errnum = Err.Number
    errtxt = Err.Description

It does nothing, as a matter of fact.

What you need to do is something like this:

CODE

Function Import_Excel(myPath As String, myFile As String, myExt As String, myTable As String, Optional myRange As String, Optional mySQLTable As String, Optional mySQLField As String)

    On Error GoTo ErrHandler

    .....code goes here

ErrHandler:
    errnum = Err.Number
    errtxt = Err.Description
    ...the rest of your error handler code goes her

End Function

[GroverParkGeorge]

The reason for this is that Err gets its value AFTER an error is encountered, not before.

[cheekybuddha]

First thing:

Always use Option Explicit

This will mean you have to declare your variables.

Then, with your code:

Your On Error GoTo ErrHandler: line has a colon at the end. This actually makes a label (though how it works with spaces in the label name, I don't know!).

So:

CODE

Option Compare Database
Option Exlicit

Function Import_Excel(myPath As String, myFile As String, myExt As String, myTable As String, Optional myRange As String, Optional mySQLTable As String, Optional mySQLField As String)
On Error GoTo ErrHandler

  Dim errnum As Long
  Dim errtxt As String

  DoCmd.RunSQL "DELETE * FROM WrongSheetErrorsTable"

  Do While myFile <> ""
    If myFile Like myExt Then
      DoCmd.TransferSpreadsheet acImport, , myTable, myPath & myFile, True, myRange
    End If
    myFile = Dir()
  Loop

  If DCount("*", "WrongSheetErrorsQuery") > 0 Then
    DoCmd.OpenQuery "WrongSheetErrorsQuery"
  Else
    MsgBox "All Good"
  End If

Exit_Here:
  If DCount("*", "WrongSheetErrorsQuery") > 0 Then
    DoCmd.OpenQuery "WrongSheetErrorsQuery"
  Else
    MsgBox "All Good"
  End If
  Exit Function

ErrHandler:
    errnum = Err.Number
    errtxt = Err.Description
    Select Case errnum
        Case 91
            DoCmd.RunSQL "INSERT INTO [WrongSheetErrorsTable] ([FileName],[ErrorNumber],[ErrorText]) VALUES ('" & myFile & "','" & errnum & "','" & errtxt & "');"
        Case 2391
            DoCmd.RunSQL "INSERT INTO [WrongSheetErrorsTable] ([FileName],[ErrorNumber],[ErrorText]) VALUES ('" & myFile & "','" & errnum & "','" & errtxt & "');"
        Case Else 'All other errors.
            MsgBox errtxt
    End Select
    Resume Exit_Here

End Function

hth,

d

[tina t]

...and, btw, you're missing the Option Explicit statement at the top of your code, as

Option Compare Database
Option Explicit

declaring Option Explicit at the top of every module - including form and report modules - is generally considered to be a best practice. you can set the option at the software level, so that every new module you create in every database you work in will automatically add the statement. in the VBE window, from the menu click Tools | Options, then in the Options dialog, make sure the box is checked next to Require Variable Declaration.

 OptionExplicit.PNG ( 11.84K )Number of downloads: 0


for existing modules, you'll need to add the statement manually.

hth
tina

EDIT: ha, David, you're too quick for me! :)
This post has been edited by tina t: Nov 19 2019, 03:02 PM

[GroverParkGeorge]

And me, too. Missed the Option Explicit problem entirely.

[aggiemarine07]

@GroverParkGeorge, tina-t, and cheekybuddha - thank you all for your suggestions!

When I make yalls recommended changes though, it gives a "runtime erro '3075'" because its trying to import one of the columns in the imported spreadsheet into the error table. I dont need that information, just the filename, error number, and error description. Any idea why?

Specifically, this is happening at the

DoCmd.RunSQL "INSERT INTO [ImportErrorLog] ([FileName],[ErrorNumber],[ErrorText]) VALUES ('" & myFile & "','" & errnum & "','" & errtxt & "');"

code under Case 2391
This post has been edited by aggiemarine07: Nov 19 2019, 03:24 PM

[Daniel_Stokley]

Hello, is errNum a number or a string? If it is a number, then you might try replacing

CODE

VALUES ('" & myFile & "','" & errnum & "','" & errtxt & "')

with

CODE

VALUES ('" & myFile & "'," & errnum & ",'" & errtxt & "')

i.e. remove the two single quote characters surrounding errnum.

[cheekybuddha]

OK, So, you want to keep looping even when there s an error:

CODE

Function Import_Excel(myPath As String, myFile As String, myExt As String, myTable As String, Optional myRange As String, Optional mySQLTable As String, Optional mySQLField As String)
On Error GoTo ErrHandler

  Dim errnum As Long
  Dim errtxt As String

  DoCmd.RunSQL "DELETE * FROM WrongSheetErrorsTable"

  Do While myFile <> ""
    If myFile Like myExt Then
      DoCmd.TransferSpreadsheet acImport, , myTable, myPath & myFile, True, myRange
    End If
Resume_Here:
    myFile = Dir()
  Loop

Exit_Here:
  If DCount("*", "WrongSheetErrorsQuery") > 0 Then
    DoCmd.OpenQuery "WrongSheetErrorsQuery"
  Else
    MsgBox "All Good"
  End If
  Exit Function

ErrHandler:
    errnum = Err.Number
    errtxt = Err.Description
    Select Case errnum
        Case 91, 2391
            DoCmd.RunSQL "INSERT INTO [WrongSheetErrorsTable] ([FileName],[ErrorNumber],[ErrorText]) VALUES ('" & myFile & "','" & errnum & "','" & errtxt & "');"
            Resume Resume_Here
        Case Else 'All other errors.
            MsgBox errtxt
            Resume Exit_Here
    End Select

End Function

Also, note Daniel's comment about the ErrorNumber field datatype - I left your INSERT statement as is, but change it if Daniel's comment applies.

hth,

d

[aggiemarine07]

@Daniel_Stokley - within the table the field errnum is "short text"; the same goes for the filename and errtext fields. I tried your suggestion and it still gives me the same error as before.

@cheekybudha - I do want it to keep looping through all of the import files. then when I am done importing, I want it to open the table to show me which files it was unable to import

This is the exact syntax I get when I try yalls code:

"Run-time error '3075':
Syntax error (missing operator in query express "Field: 'Mininum Warehouse Order' doesnt exist in destination table 'PLOGImportTable.");'.

That field (mininum warehouse order) is the error I am trying to log in my table. So Access is catching the error like it is supposed to and is prompting me that there is an error but its not writing anything to the table.

Let me know if this doesnt make sense.

[cheekybuddha]

Hmmm...

Try using this in the error handler:

CODE

' ...
            DoCmd.RunSQL "INSERT INTO [WrongSheetErrorsTable] ([FileName],[ErrorNumber],[ErrorText]) VALUES ('" & myFile & "','" & errnum & "','" & Replace(errtxt, "'", "''") & "');"
' ...

hth,

d

[aggiemarine07]

@cheekybuddha - that did it! what did you do?

[cheekybuddha]

I noticed that you got the error when the errtxt contained single quotes.

Since you are using single quotes to delimit the string passed on your SQL INSERT statement then you must also escape any single quotes contained within the string.

This is done by doubling them up, which is what the Replace() function is doing.

hth,

d

[aggiemarine07]

awesome, thanks so much for your help!

[cheekybuddha]

[cheekybuddha]

Hi again,

If you are doing any amount of coding you may wish to consider an add-in for the VBA editor like MZ-Tools.

This is a great tool that includes the ability to add an error handler to your procedure at the click of a button (plus a myriad of other really helpful features).

It used to be free but unfortunately costs a little now. Other members here may be able to suggest other similar tools which are still free.

hth,

d

[gemmathehusky]

the real problem with errors is how you react to them

so given your handler - you cannot just assume you can continue to run code after encountering an error, so resume next after an error may not be sensible.

note that you won't get to the error handler with no error (an error of zero)

CODE

ErrHandler:
    Select Case errnum
        Case 0 'no error.
            Resume Next
        Case 91
            DoCmd.RunSQL "INSERT INTO [WrongSheetErrorsTable] ([FileName],[ErrorNumber],[ErrorText]) VALUES ('" & myFile & "','" & errnum & "','" & errtxt & "');"
            Resume Next
        Case 2391
            DoCmd.RunSQL "INSERT INTO [WrongSheetErrorsTable] ([FileName],[ErrorNumber],[ErrorText]) VALUES ('" & myFile & "','" & errnum & "','" & errtxt & "');"
            Resume Next
        Case Else 'All other errors.
            Resume Next
    End Select

with regard to reporting errors then you can use the error object
err is the default case of err.number

msgbox "Error encountered " & vbcrlf & "Error: " & err & " Description: " & err.description