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

Hi Brad,

I just answered this for you on Linked In.

Use CurrentDB.Execute str_SQL ,dbFailOnError 
instead of DoCmd.RunSQL.  

Then look for Err.Number 3022 in your error trap.

-- 
Stuart

On 28 Feb 2011 at 18:57, Brad Marks wrote:

> 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
>