• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

error handling on duplicate index value

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

Exit_Form_BeforeUpdate:
    Exit Sub
ErrBU:
    MsgBox "This is a duplicate part number and description for this manufacturer", vbOKOnly, "Hi it's me, the Central Scrutinizer"
    Resume Exit_Form_BeforeUpdate
End Sub

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.

Jim
0
jnearing
Asked:
jnearing
  • 2
1 Solution
 
tomookCommented:
The update actually happens between the BeforeUpdate and AfterUpdate events ;) You want to put your error handling code in the Form's OnError handler (Form_Error) and set
    Response = acDataErrContinue
 so Access does not pop up a message box.
0
 
jnearingAuthor Commented:
Thanks, that's exactly it.
Now I can go on vacation.

Jim

0
 
tomookCommented:
Have a good trip.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now