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

But note:

CurrentDb.Execute strSQL
Debug.Print CurrentDB.RecordsAffected
will always return 0.

You need to do this instead:
Dim db as DAO.Database
Set db = CurrentDB()
...
db.Execute StrSQL
Debug.Print db.RecordsAffected
...

-- 
Stuart

On 28 Feb 2011 at 21:17, jwcolby wrote:

> You can also get NoRecsAffected from the db.Execute
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> On 2/28/2011 9:03 PM, Brad Marks wrote:
> > 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.
> >
> >
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>