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

No BeforeUpdate event.  This is updating via SQL in code, not through  a form.

-- 
Stuart

On 28 Feb 2011 at 17:11, Rocky Smolin wrote:

> 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
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>