Different error message?

[joe789]

Hi Folks,

If someone enters duplicate data the following error is rendered:

"Microsoft Office Access: 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."

Is there a way to tailor make an error message, maybe something like the following:

"You have entered a record that has already been found in the database, please do not enter this record as it already exists and would be duplicate. Thank you."

This database is being made idiot proof, so if there is anyway that I can edit this system error in an easy way, please let me know. Any help would be greatly appreciated.

Thank you,

Joe

 

[ajetrumpet]

there is a collection that is called the err collection inside of access. when this error happens again, trap it with the event that your firing (push of a button is best), get the error code (if you don't already have it from the pop up error message), and write your own code. here is an example of what to write:

on push of a button:

on error goto err_message

docmd.save 'WHAT EVER CODE YOU WANT IN THIS SECTION
etc, etc...

exit sub

err_message:

   if err.number = THE ERROR NUMBER then
      msgbox "Record already found, etc, etc...."
         exit sub
   end if

if the error is occuring without an event, i suggest putting a button in to run the code that access is already running on its own at the moment you get the error.

an example would be to try and go to a new record in a data entry form. if you've violated the PK rules, access will show the error that you're talking about and then tell you that "you can't save the record at this time." if it is erroring out on its own without your code running, then you need to write your own sub routine to capture the error like i spelled out above.

 

[gemma-the-husky]

this error is a system error, rather than a programming erorr, so i think normal error handling wont catch it.

instead, on your form there is an ERROR event. You can use this to trap this particular error, and replace the message with your own.

alternatively, you could use beforeupdate events to test whether the data already exists, or not, rather than rely on access to do it for you.