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

_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________



as a rule I don't recommend using Docmd.RunSQL.  Using the other method seems to work far better and gives you more control.  You can also avoid any confirmation messages (assuming the user has that option enabled).

 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Tuesday, 1 March 2011 1:04 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] How to Trap ?Insert Error? (Duplicate record) causedby Indexed Field (No Duplicates)

Stuart,

Yes, thanks a million for the help.

Here is what I learned tonight...

By switching from 

"DoCmd.RunSQL SQL_String"

to

"CurrentDb.Execute SQL_String, dbFailOnError"

I can trap Err.Number = 3022 (duplicate record)



I really appreciate the help.  I was pulling my hair out as I thought that the "DoCmd.RunSQL SQL_String" method would allow me to see the 3022 error.  

Brad





-----Original Message-----
From: accessd-bounces at databaseadvisors.com on behalf of Stuart McLachlan
Sent: Mon 2/28/2011 7:26 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] How to Trap ?Insert Error? (Duplicate record) causedby 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
> 




-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


_______________________________________________________________________________________

The information transmitted in this message and its attachments (if any) is intended 
only for the person or entity to which it is addressed.
The message may contain confidential and/or privileged material. Any review, 
retransmission, dissemination or other use of, or taking of any action in reliance 
upon this information, by persons or entities other than the intended recipient is 
prohibited.

If you have received this in error, please contact the sender and delete this e-mail 
and associated material from any computer.

The intended recipient of this e-mail may only use, reproduce, disclose or distribute 
the information contained in this e-mail and any attached files, with the permission 
of the sender.

This message has been scanned for viruses.
_______________________________________________________________________________________