UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> New To Building Error Handlers, Access 2016    
 
   
aggiemarine07
post Nov 19 2019, 01:42 PM
Post#1



Posts: 75
Joined: 7-January 11



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 smile.gif

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
Go to the top of the page
 
GroverParkGeorge
post Nov 19 2019, 02:48 PM
Post#2


UA Admin
Posts: 36,165
Joined: 20-June 02
From: Newcastle, WA


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

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Nov 19 2019, 02:49 PM
Post#3


UA Admin
Posts: 36,165
Joined: 20-June 02
From: Newcastle, WA


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

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cheekybuddha
post Nov 19 2019, 02:51 PM
Post#4


UtterAccess Moderator
Posts: 11,857
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
tina t
post Nov 19 2019, 03:00 PM
Post#5



Posts: 6,175
Joined: 11-November 10
From: SoCal, USA


...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.

Attached File  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

--------------------
"the wheel never stops turning"
Go to the top of the page
 
GroverParkGeorge
post Nov 19 2019, 03:03 PM
Post#6


UA Admin
Posts: 36,165
Joined: 20-June 02
From: Newcastle, WA


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

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
aggiemarine07
post Nov 19 2019, 03:23 PM
Post#7



Posts: 75
Joined: 7-January 11



@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
QUOTE
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
Go to the top of the page
 
Daniel_Stokley
post Nov 19 2019, 04:11 PM
Post#8



Posts: 332
Joined: 22-December 14
From: Grand Junction, CO, USA


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.


Go to the top of the page
 
cheekybuddha
post Nov 19 2019, 04:16 PM
Post#9


UtterAccess Moderator
Posts: 11,857
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
aggiemarine07
post Nov 19 2019, 04:39 PM
Post#10



Posts: 75
Joined: 7-January 11



@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:

QUOTE
"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.
Go to the top of the page
 
cheekybuddha
post Nov 19 2019, 04:42 PM
Post#11


UtterAccess Moderator
Posts: 11,857
Joined: 6-December 03
From: Telegraph Hill


Hmmm...

Try using this in the error handler:
CODE
' ...
            DoCmd.RunSQL "INSERT INTO [WrongSheetErrorsTable] ([FileName],[ErrorNumber],[ErrorText]) VALUES ('" & myFile & "','" & errnum & "','" & Replace(errtxt, "'", "''") & "');"
' ...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
aggiemarine07
post Nov 19 2019, 05:04 PM
Post#12



Posts: 75
Joined: 7-January 11



@cheekybuddha - that did it! what did you do?
Go to the top of the page
 
cheekybuddha
post Nov 20 2019, 04:53 AM
Post#13


UtterAccess Moderator
Posts: 11,857
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
aggiemarine07
post Nov 20 2019, 08:35 AM
Post#14



Posts: 75
Joined: 7-January 11



awesome, thanks so much for your help!
Go to the top of the page
 
cheekybuddha
post Nov 20 2019, 08:49 AM
Post#15


UtterAccess Moderator
Posts: 11,857
Joined: 6-December 03
From: Telegraph Hill


yw.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Nov 20 2019, 09:05 AM
Post#16


UtterAccess Moderator
Posts: 11,857
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
gemmathehusky
post Nov 25 2019, 10:03 AM
Post#17


UtterAccess VIP
Posts: 4,745
Joined: 5-June 07
From: UK


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






--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th December 2019 - 07:44 AM