error handling on duplicate index value
Posted on 1998-08-14
I have a table that I don't want to allow duplicates into so I've created an index with NO DUPLICATES (duh!)
So when the user enters in a duplicate record I'm trying to trap the error so Access (97) doesn't put up that big ugly box that says
"The changes you requested to the table were not successful because they would create duplicate values in the index...."
The error happens after the Form_BeforeUpdate event. So how come I can't display my own error message instead of Access putting up a message?
Here is my Form Before Update
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrBU
MsgBox "This is a duplicate part number and description for this manufacturer", vbOKOnly, "Hi it's me, the Central Scrutinizer"
I put in a breakpoint on the ON ERROR line so I can watch it. It drops down to the next line (Exit_Form_BeforeUpdate, does the Exit Sub line, then Access puts up it's error message.
On a success record insert (no duplicate) the next event after Form_BeforeUpdate is amazingly enough Form_AfterUpdate. The error seems to happen between where I can't figure out how to insert an error handler.
I'd really like to display my error message instead of the one Access puts up.
Thanks in advance.