[SOLVED] Primary key violation triggers warning, but does not trigger error handler

Primary key violation triggers warning, but does not trigger error handler

I have a button on a form that runs an insert statement that takes data from some textboxes on the form and puts them in a table.

In the event that the user enters duplicate information, in the primary key (a concatenation of 2 of the textboxes), I want to pop a msgbox saying that's what happened and possibly remove the standard Access warning (but that's not that big a deal).

I thought I could throw in an On Error GoTo and then put my msgbox but that doesn't work. The Access warning comes up and the new record isn't added, but the On Error is ignored. The "Success" msgbox even still pops up.

Here's the gist of my code:

Dim strSQL As String
strSQL = [INSERT STATEMENT]

On Error GoTo Duplicate:
DoCmd.RunSQL (strSQL)
MsgBox "Ticket Completed!", vbOKOnly, "Success!"

JumpShip:
    Exit Sub

Duplicate:
Select Case Err.Number
    Case 3022
        MsgBox "This ticket has already been completed!", vbOKOnly, "Error!"
        Resume JumpShip
    Case Else
        Resume JumpShip
    End Select

End Sub

I also tried just On Error without specifying the code (just to try to make finding the issue easier) but the result was the same. Every time, the "1 record was not appended due to primary key violation blah blah" msg pops up, but the error handler is ignored. The success msgbox pops and then it's done. Is the PK violation not a regular error?

Solution

You need to use the Database.Execute() method, passing the dbFailOnError option flag which will rollback changes and raise a runtime error if the query fails for whatever reason.

Currentdb().Execute strSQL, dbFailOnError