Access2016: Primary key violation triggers warning, but does not trigger error handler?-CodePudding

I thought this would be pretty simple, but I can't seem to find the issue despite quite a bit of google-research. 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. Simple right? So 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)

So I thought I could just throw in an On Error goto and then put my msgbox but that doesn't work. For some reason 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've 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?

CodePudding user response：

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