Solved

error handling on duplicate index value

Posted on 1998-08-14
3
265 Views
Last Modified: 2008-02-26
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
Comment
Question by:jnearing
  • 2
3 Comments
 
LVL 4

Accepted Solution

by:
tomook earned 200 total points
ID: 1959231
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
 
LVL 1

Author Comment

by:jnearing
ID: 1959232
Thanks, that's exactly it.
Now I can go on vacation.

Jim

0
 
LVL 4

Expert Comment

by:tomook
ID: 1959233
Have a good trip.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now