Results 1 to 7 of 7
  1. #1
    zarfx4 is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    15

    Error Handling 3421 Codes

    Hello,

    I built a db recently with the goal of processing through a folder of excel forms. There was a manual effort going on where someone was taking 10's of files daily, opening each up to capture a couple of key items from the form.

    I created a db that would allow someone to point the data base to an "import" folder, "completed" folder, and "failed" folder.



    The goal was to process all the excel files from the import folder, open each file in the background, grab the key fields and write to a data set into a table, then append that import to the master data table.

    Each file processed successfully would be move to a completed location. Any Failed imports (due to bad file, or data issues) would be skipped and moved to the failed folder.

    Everything works fine.....except...

    If a file has text for example where a number should be,i get a data type conversion error 3421.

    I have built in Error Handle in the code, but 3421, does not appear to trigger this affect.

    What do i need to do to allow my code to encounter this error, and skip the file to move onward with the code?

    Here is a snapshot of the key aspect of the code.

    'Open Excel in Background and pull data
    Set ExcelApp = CreateObject("Excel.Application")
    Dim WkBk As Excel.Workbook
    Set WkBk = ExcelApp.Workbooks.Open(strPath)
    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset("Auto_Load ")

    On Error GoTo Error_Handling

    'Records data from Excel file and saves to Load Table
    With rst

    .AddNew

    .Fields("Name") = WkBk.Sheets(1).Range("D7")
    .Fields("Company_ID") = WkBk.Sheets(1).Range("D8")
    .Fields("Pro") = WkBk.Sheets(1).Range("D9")
    .Fields("Date") = WkBk.Sheets(1).Range("D10")
    .Fields("Provider") = WkBk.Sheets(1).Range("D11")
    .Fields("Support_Contact_Name") = WkBk.Sheets(1).Range("J8")
    .Fields("Support_Contact_Email") = WkBk.Sheets(1).Range("J9")
    .Fields("Support_Contact_Phone") = WkBk.Sheets(1).Range("J10")
    .Fields("Total_Users") = WkBk.Sheets(1).Range("L23")
    .Fields("EID") = strUserID & "AutoLoader"
    .Fields("File_Name") = strCount & "_" & strNow & "_" & strFile
    .Update
    End With
    'Quit Excel
    ExcelApp.DisplayAlerts = False
    ExcelApp.Quit
    ExcelApp.DisplayAlerts = True
    Pause (1.5)
    'Move processed file to completed director
    Call fso.MoveFile(strPath, strComplete)
    strCount = strCount + 1

    On_Error_Resume:


    'Loop to next file
    strFile = Dir$ 'Next file
    Loop

    'Append Load table to main table
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Auto_Load_Append"
    DoCmd.SetWarnings True

    'Output actions for review
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Auto_Load", "C:\Users" & strUserID & "\Desktop\AUTO_IMPORT_REVIEW_" & strNow & ".xls", True

    MsgBox "Load Complete! There were " & strCount & " Successful files loaded and " & strFailCount & " files that failed." & vbCrLf & "Please review the output file on your desktop and/or the Failed folder."

    Exit Sub

    Error_Handling:

    rst.Close
    ExcelApp.DisplayAlerts = False
    ExcelApp.Quit
    ExcelApp.DisplayAlerts = True
    Pause (1.5)
    Call fso.MoveFile(strPath, strFailedPath)
    strFailCount = strFailCount + 1
    GoTo On_Error_Resume

    End Sub

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Your error handler treats all errors the same. Below specifically handles 3421 and notifies via msgbox any other error. This select case technique enables you to handle any number of error types individually if need be.

    Code:
    Error_Handling:
    Select case err
        case 3421
            rst.Close
            ExcelApp.DisplayAlerts = False
            ExcelApp.Quit
            ExcelApp.DisplayAlerts = True
            Pause (1.5) 
            Call fso.MoveFile(strPath, strFailedPath)
            strFailCount = strFailCount + 1
            Resume On_Error_Resume
        case else
            Msgbox Err.Number & ", " & Err.Description
            'Not sure what you want to do in this case....
     End Select
    End Sub
    Last edited by davegri; 06-04-2017 at 02:44 PM. Reason: Comment addition

  3. #3
    zarfx4 is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    15
    Thanks for the reply. I understand your response. But the issue is that my current code is not even being triggered when it encounters 3421. I get the run time error box every time.

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    my current code is not even being triggered when it encounters 3421
    Then the 3421 error is not generated in this procedure. If it was, the error procedure would catch it. (unless see below)
    If the error is not being handled, the run-time error msgbox appears. The 'Debug' button should take you directly to the line of code in error. Where is that line?

    You have code executing before the On Error declaration.
    All the DIMs should occur first, immediately followed by the On Error Goto...
    Or, have the On Error statement the very first line in the procedure.
    Currently it's possible that an error could occur before the On Error statement and it would not be handled, which would give you the run-time error msgbox.
    And be sure to have "Option Explicit" at the top of each object's code.
    Last edited by davegri; 06-05-2017 at 06:05 AM. Reason: more

  5. #5
    zarfx4 is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    15
    It's being triggered in the .Addnew Record set. FOr example, the first item being pulled from the XLS file is a company ID. It "should" be numeric, but external users will sometimes enter bad data on a form. If an alpha numeric figure is put on the form, then the access vb code above will error, 3421, and highlight that row as it cant write alpha to a numeric data type.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would think the error trap would catch it, and I'd structure it like davegri suggested. That said, you could also test the value with IsNumeric() and code appropriately. It's basically avoiding errors rather than trapping them.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    cross posted here
    http://www.dbforums.com/showthread.p...odes&p=6648237

    zarfx4: please read this page about cross posting - Thanks.
    http://www.excelguru.ca/content.php?184

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error 3421, Set QueryDef
    By FmrVBAJunkie in forum Programming
    Replies: 9
    Last Post: 05-03-2016, 07:55 PM
  2. Replies: 5
    Last Post: 09-06-2015, 12:06 PM
  3. Is there an error with my import codes?
    By hclifford in forum Import/Export Data
    Replies: 6
    Last Post: 12-08-2014, 10:22 AM
  4. Replies: 3
    Last Post: 09-05-2012, 10:23 AM
  5. Replies: 6
    Last Post: 05-10-2012, 10:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums