Using Custom Error Messages

[dlblack]

I have created a form that captures Capital Project Request data for review. After the reviewer has made their decision as to approve or deny the project request, they click an approval button or a denial button. When the button is clicked, an append query runs and writes the data to the appropriate table. I have the project ID set as the primary key and the project ID field is set to not allow duplicates. This process is working correctly, but I want to replace the standard access message with a custom message telling the reviewer the project has already been saved and that duplicates are not allowed. I thought I found the code that would enable me to add the custom message but I have not been able to get a custom message of any type to appear. The code I have written is as follows:

Private Sub Approve_Click()
On Error GoTo Err_Approve_Click
 
    DoCmd.GoToRecord , , acNewRec
 
    Dim stDocName As String
 
    stDocName = "Qry_Final Capital Budget Approval"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
 
 
Exit_Approve_Click:
    Exit Sub
 
Err_Approve_Click:
   Select Case Err.Number
 
    Case 3022
        MsgBox "Data has already been saved. Duplicates not allowed."
 
    Case Else
        MsgBox "This is an unexpected error.  Please report this to the administrator."
End Select
Response = acDataErrContinue
Resume Exit_Err_Approve_Click
 
 
End Sub

Any help would be appreciated as my knowledge of VB is very limited and I have taken most of my code from samples and online fourms.

[mandanarchi]

Try changing this line

Select Case Err.Number

to this

Select Case Err

[dlblack]

Mandanarchi,
I made the suggested change, but the error message displaying is still the default message. Any other suggestions would be appreciated.

[ChipR]

Personally I do a DLookup on the table's primary key with the entered data and see if it returns anything other than null. If so, that data is already in the table, and I give a message and exit the function. I don't know whether others find this preferable to generating an error.
Also, your Resume label doesn't match anything.

[dlblack]

Chip,
Thanks for the tip. I will try implementing the Dlookup to see if this solves my issue.

[ADezii]

You are simply writing your code in the wrong Event. To specifically trap Errors generated within the context of a Form, use a combination of the Form's Error() Event and the Response Argument. The following demo should point you in the right direction:

 
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Const conNO_ERROR = 0
Const conERR_DUPLICATE_KEY = 3022
 
Select Case DataErr
  Case conNO_ERROR              'do nothing
  Case conERR_DUPLICATE_KEY     'Duplicate Value in Index/Key
    MsgBox "This Record duplicates the Primary, try another Value", _
            vbExclamation, "Duplicate Entry"
  Case Else                     'Unknown
    MsgBox "Unspecified Error", vbExclamation, "Error in Form_Error()"
  End Select
    Response = acDataErrContinue        'suppress Access's built in Error Message
End Sub