2

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?

Community
  • 1
  • 1
BSoD'n'Syntaxes
  • 35
  • 8
  • Use the `Currentdb().Execute` method and pass the `dbFailOnError` flag. – Kostas K. Nov 09 '21 at 13:59
  • @KostasK. that did the trick, thank you! Could you please post this as an answer so I can accept it? Also just for my reference, database errors don't qualify as vba errors so we have to force it with the flag, is that the issue? – BSoD'n'Syntaxes Nov 09 '21 at 14:11
  • Indeed, that is the purpose of the `dbFailOnError` flag (plus it rolls back changes). See the remarks on the method's doc page. – Kostas K. Nov 09 '21 at 14:57

1 Answers1

2

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
Kostas K.
  • 8,293
  • 2
  • 22
  • 28