Avatar of bejhan
bejhan asked on

On Error GoTo

I'm using MS Access and am trying to do some error handling on my import:

        On Error GoTo ImportError
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ntblNewItems", GetInputDirectory("New Business") & lstFileList, True

However, it seems that any code following the DoCmd.TransferSpreadsheet that causes an error also uses this same On Error GoTo ImportError. Is there anyway to make this statement just apply to the DoCmd.TransferSpreadsheet?
Microsoft Access

Avatar of undefined
Last Comment
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jim Horn

>Is there anyway to make this statement just apply to the DoCmd.TransferSpreadsheet?

On Error GoTo ImportError
DoCmd.TransferSpreadsheet blah, blah, blah
On Error Resume Next

Unlike SQL Server, the error handling in Access is more like an error trap, and will apply to all rows below wherever you set 'On Error Goto x'
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Or ..


        On Error GoTo ImportError
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ntblNewItems", GetInputDirectory("New Business") & lstFileList, True
        Err.Clear
        On Error Goto SomeOtherErrorHandler    ' reset to a different error handler

or


        On Error GoTo ImportError
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ntblNewItems", GetInputDirectory("New Business") & lstFileList, True
         
        Select Case err.number
             Case   nnn   "some error number related to import"
                   msgbox 'Import Error
             Case Else
                   msgbox "Some other error occurred"
        End Select
        Err.Clear
        On Error Goto 0   '** or Some other error handler -   reset error handler
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck