[AccessD] How to Trap ?Insert Error? (Duplicate record) caused byIndexed Field (No Duplicates)

I think I'd try the BeforeUpdate event, check for a duplicate in the event,
and if there is, update your tracking table, give a message to the user and
set Cancel = True.  I think.

Rocky
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Monday, February 28, 2011 4:57 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] How to Trap ?Insert Error? (Duplicate record) caused
byIndexed Field (No Duplicates)

We have an Access table that we want to insert records into (via
DoCmd.RunSQL). 

We have one field Indexed (No Duplicates). 

There is the normal "error handling" set up. 

When we ran tests to ensure that the "No Duplicates" on the Indexed Field is
working, we were was expecting that the normal error-handling would trap the
error. It does not. 

If we have "DoCmd.SetWarnings True" Access displays a pop-up message with
the error message, but we would like to be able to programmatically trap the
error in VBA code. 

We want to keep track of how often there is an attempt to insert a duplicate
record.  The input data is actually coming from another system.  We know
that there will be attempts to insert duplicates, and we know the the "Index
(No Duplicates)" will prevent duplicates from being inserted.  We would like
to be able to trap the error with VBA code so that we can analyze the number
of times that this is happening.   

Is there a way to do this? 

Is there some other "Status Code" available after an insert to indicate if
an insert worked or not (that we can get at with VBA code)?

Thanks, 

Brad