Solved

error handling on duplicate index value

Posted on 1998-08-14
3
264 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

707 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

16 Experts available now in Live!

Get 1:1 Help Now