Change duplicate values Error Msg

[Samantha]

Does anybody knows how to control the below message to something like this through VBA:

"The Job: " & JobNumber & " already exists in the table." or to open an alternate form where they can make the change. Allowing duplicates is not appropriate for what I am trying to accomplish. I just want a friendly way of rerouting a user to understandable answer.

instead of this:
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."


Thanks for reading!
Samantha

 

[pbaldy]

You can use a DCount() to test in either the before update event of the form or the control where they enter that value. I'd probably use the control's, so they find out before entering other data.

 

[Samantha]

Thanks, for your response. I am just getting back to attempting to solve this problem. I have been trying to figure out exactly how to use this and the examples I have seen do not exactly fit my problem, or I just cant make sense of it. Would you have an example of using this procedure?

My form that I am executing from is frmAddWOH it is a modal dialog box and has one field JobNumber which is a text box and two buttons OK & Cancel. The record source of the form is tblWOH.

This form adds a new job number to tblWOH, the problem is when a job has been added the tblWOH.Status field is defaulted to "Approved" state. The Status field contains 3 options Approved, Completed, Cancelled. The error message is appearing when a user attempts to put a job back on this schedule since it is already there in a cancelled state. I just want to be able to direct the person back to a different form or even better change the Status field back to approved programmatically so there is no message to the user.