By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,350 Members | 1,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,350 IT Pros & Developers. It's quick & easy.

Using Custom Error Messages

P: 6
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Approve_Click()
  2. On Error GoTo Err_Approve_Click
  4.     DoCmd.GoToRecord , , acNewRec
  6.     Dim stDocName As String
  8.     stDocName = "Qry_Final Capital Budget Approval"
  9.     DoCmd.OpenQuery stDocName, acNormal, acEdit
  12. Exit_Approve_Click:
  13.     Exit Sub
  15. Err_Approve_Click:
  16.    Select Case Err.Number
  18.     Case 3022
  19.         MsgBox "Data has already been saved. Duplicates not allowed."
  21.     Case Else
  22.         MsgBox "This is an unexpected error.  Please report this to the administrator."
  23. End Select
  24. Response = acDataErrContinue
  25. Resume Exit_Err_Approve_Click
  28. 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.
Jan 20 '09 #1
Share this Question
Share on Google+
5 Replies

P: 90
Try changing this line
Expand|Select|Wrap|Line Numbers
  1. Select Case Err.Number
to this
Expand|Select|Wrap|Line Numbers
  1. Select Case Err
Jan 20 '09 #2

P: 6
I made the suggested change, but the error message displaying is still the default message. Any other suggestions would be appreciated.
Jan 20 '09 #3

Expert 100+
P: 1,287
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.
Jan 20 '09 #4

P: 6
Thanks for the tip. I will try implementing the Dlookup to see if this solves my issue.
Jan 22 '09 #5

Expert 5K+
P: 8,750
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:
Expand|Select|Wrap|Line Numbers
  2. Private Sub Form_Error(DataErr As Integer, Response As Integer)
  3. Const conNO_ERROR = 0
  4. Const conERR_DUPLICATE_KEY = 3022
  6. Select Case DataErr
  7.   Case conNO_ERROR              'do nothing
  8.   Case conERR_DUPLICATE_KEY     'Duplicate Value in Index/Key
  9.     MsgBox "This Record duplicates the Primary, try another Value", _
  10.             vbExclamation, "Duplicate Entry"
  11.   Case Else                     'Unknown
  12.     MsgBox "Unspecified Error", vbExclamation, "Error in Form_Error()"
  13.   End Select
  14.     Response = acDataErrContinue        'suppress Access's built in Error Message
  15. End Sub
Jan 23 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.