469,616 Members | 1,792 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,616 developers. It's quick & easy.

Using Custom Error Messages

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
5 2414
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
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
1,287 Expert 1GB
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
Thanks for the tip. I will try implementing the Dlookup to see if this solves my issue.
Jan 22 '09 #5
8,800 Expert 8TB
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.

Similar topics

17 posts views Thread by Steve Jorgensen | last post: by
1 post views Thread by Tamas Demjen | last post: by
1 post views Thread by Jacob | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.