Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    What error number for Key Violations (A2000 (SR-1))

    I'm trying to find out how to trap the error message about being unable to append all the data to the table because records were lost due to key violations. The error message does not provide an error number. When I searched this forum, I found the following post that mentioned that the error number for key violations is 3022.

    http://www.wopr.com/cgi-bin/w3t/showflat.p...ew=&sb=&o=&vc=1

    However, 3022 is not trapping my key violations. Any help will be appreciated.

    Thanks,

    Randy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: What error number for Key Violations (A2000 (SR-1))

    Error numbers 10011 and 10015 are about not being able to append records due to key violations, 10509 and 10515 same for update queries, and 10512 and 10518 same for delete queries.

  3. #3
    Star Lounger
    Join Date
    Feb 2002
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What error number for Key Violations (A2000 (SR-1))

    Thanks for the reply Hans, but none of those numbers work either. Here's the function... maybe it's something that I'm doing wrong. I can't seem to trap the error number - have tried to display it in a msgbox and tried to debug.print it, but it doesn't seem to generate one.

    Function AppendCashControlExcel()
    On Error GoTo AppendCashControlExcel_Err

    ''''DoCmd.SetWarnings False
    DoCmd.TransferSpreadsheet acImport, 8, "CashControl", "C:BCCRDatabaseCashControl.xls", True, "ALL!"
    DoCmd.SetWarnings True

    AppendCashControlExcel_Exit:
    Exit Function

    AppendCashControlExcel_Err:
    If Err.Number = 3022 Then
    MsgBox "One or more records were omitted from the import because they had " & _
    "the same 'Ref ID', 'Total', and 'GL TransactionDate'." & vbCrLf & vbCrLf & _
    "You may have attempted to load the prior day's file again. Ensure that you " & _
    "have replaced the prior 'CashControl.xls' file with the current one.", , "Processing Error"
    Resume AppendCashControlExcel_Exit
    End If
    MsgBox Err.Number & " " & Error
    Resume AppendCashControlExcel_Exit
    End Function

    Thanks,

    Randy

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: What error number for Key Violations (A2000 (SR-1))

    Here's maybe another way to solve this problem, just my 2 cents worth.

    1. Input the spreadsheet to a 'temporary' table with no indexes.
    2. Do a join between both tables (temporary table and your normal table CashControl) and check for any matches and alert the user if there are any, and stop the process here.
    3. Then, if all ok, do an append query to add the records from the temporary table to the CashControl table.

    If you keep having problems, send the database and spreadsheet (deleting all sensitive data of course) and I'm sure someone can shed some light on it.

    Cheers,
    Pat

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: What error number for Key Violations (A2000 (SR-1))

    Actually, I believe that's an error that you aren't going to be able to trap except with an On Error Resume Next before the DoCmd.TransferSpreadsheet line and a test in the next line after it (If Err<>0 Then ... etc.). If you put a breakpoint in at that point, you can bring up the immediate window and type in

    ? Err

    That should print the specific error number. It won't do you much good though, because you will only know that some of the records you're trying to import already exist in the target table. It will NOT tell you which ones.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •