how to trap primary key violation error

[MilaK]

Hello,

I import data into a temp table and then run an Update query (below) to add data to the accrual table. I want to display a custom message instead of "Primary key violation error" if the user tries to import the same file twice and terminate the program. The "run_name" is the primary key.

Is there a way to trap the error message and changes it to a custom message?

SQL = "INSERT INTO tbl_Projects ( run_name, run_date )"
    SQL = SQL & " SELECT DISTINCT tbl_Variants_temp.run_name, tbl_Variants_temp.run_date"
    SQL = SQL & " FROM tbl_Variants_temp"
    Debug.Print SQL
    db.Execute SQL, dbFailOnError

Thanks

 

[Frothingslosh]

One of the properties available in every form is 'On Error'.

In that event, put something like this:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
'Custom error message for error 3022 (primary key violation) because the system error message is terrible.
     If DataErr = 3022 Then      'Primary key violation: value already exists in primary key.
        Beep
        MsgBox "NO SOUP FOR YOU!", vbInformation, "TRY AGAIN"
        Response = acDataErrContinue
    Else
        Response = acDataErrDisplay
    End If
 End Sub

Note that acDataErrContinue suppresses the default error popup in the indicated situation, while acDataErrDisplay makes it appear.

You can also put something similar in a standard error trap (On Error Goto Wherever) in any procedure if you expect to encounter the issue during the execution of that procedure.

On Error Goto FormName_Err
  
<Assorted VBA>
  
FormName_Exit:
    Exit Sub
    
FormName_Err:
    Select Case Err.Number
        Case 3022   'Primary key violation: value already exists in primary key.
            Beep
            MsgBox "NO SOUP FOR YOU!", vbInformation, "TRY AGAIN"
            Resume FormName_Exit    'Or, if appropriate, Resume Next
        Case Else   'Any other error.
            Resume FormName_Exit
    End Select