Custom error message problem

[theKruser]

I am trying to create a custom error message. I am getting a very odd response. On error, I get an Access-generated Runtime error '3022'. If I hit End then try to close the form, I get my custom error message.

The first text box on the form is for Employee ID. After update, it should be validated and if duplicate, show the custom error message. The Access message should not show at all. Can someone please have a look at this an help me understand where I am going wrong?

Here is what I have:

Option Compare Database
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Const conErrDuplicateKey = 3022
Select Case DataErr
   Case conErrDuplicateKey
      MsgBox "The Employee Number you entered already exists."
      Response = acDataErrContinue
   Case Else
      ' It's an unexpected error.  Let Access handle it.
       Response = acDataErrDisplay
End Select
End Sub
 
Private Sub cmdContinue_Click()
Dim rs As Object
Dim lngBookmark As String
    'set a variable to the current record
    lngBookmark = Me.EmployeeID
    
    'open the new form
    DoCmd.OpenForm "frmIndividualDataEdit"
    'take it to the selected record
    Set rs = Forms!frmIndividualDataEdit.RecordsetClone
    rs.FindFirst "EmployeeID = '" & lngBookmark & "'"
    Forms!frmIndividualDataEdit.Bookmark = rs.Bookmark
    Set rs = Nothing
    DoCmd.Close acForm, "frmAddNewJoin"
    
End Sub
 
Private Sub EmployeeID_AfterUpdate()
    If Me.Dirty Then Me.Dirty = False
End Sub

To duplicate the problem, open frmAddNew and enter an EmployeeID that exists. You get a runtime error. The custom error message only shows after you close the Access error and try to close the form.

'Break on Unhandled Errors' is selected in the Tools>Options menu of the VBE.

Thanks in advance for your time and help. Have a good day!

 

[MStef]

Here it is (attachment, zip).
I added som code in the Sub EmployeeID_AfterUpdate,
I added Sub Form_Current, and Sub Form_Load.
Open form and try. I think it is what you need.

 

[ghudson]

I would advise not to use the Form_Error event. Put all of your code in the events you are controlling and test for the know runtime error numbers to present your custom message based on the users error. Use a DLookup in the forms before update event to test if the number already exists.

 

[theKruser]

@MStef
Thank you very much for your work on this. It will go a long way in my custom error messages in the future. You are a lifesaver.

@ghudson
After @MStef added the code, I am not sure that I even need those lines anymore.

@ghudson and @MStef
If the code I had entered previously is no longer needed, please let me know. I do not know very much about VBA and am trying desperately to learn. If you have a moment to explain what the commands do and/or what I was doing wrong, I would really appreciate it. If not, no big deal.

Thank you both very much for your time and help. Hope you have a great day!