Brad Marks
BradM at blackforestltd.com
Mon Feb 28 20:03:55 CST 2011
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.