Q: how to handle docmd.runsql error in access2007

I want to detect a duplicate key error when appending a record to a table.

I correctly construct an INSERT INTO sqlString, and then the following code gets executed:


    On Error Resume Next
    DoCmd.RunSQL sqlString

   If Err = conDuplicateKey Then


when the docmd.runsql statement is executed and the duplicate key error happens,  sql error message box(es) are displayed before the statement immediately following the docmd.runsql statement is reached, i.e. "within" the docmd itself,  and then the err returned is

2501  , The RunSQL action was canceled

or 0 , depending on how I respond to the SQL error box options ...


so :

 1) how to avoid the docmd.runsql from announcing errors and instead passing the actual error back to the next VBA statement


 2) if that's impossible with docmd, how best to accomplish this:

       - if a record is in a table already, update it

       else insert a record


 3) in general, how to get docmd to be quiet ?





If you use:

    CurrentDb.Execute sqlString, dbFailOnError

you can then handle the error.

Or you can anticipate the error by first calling the DLookup function to see if a row already exists with the relevant key value.  If Null is returned then execute an SQL statement to insert a row, else execute an SQL stament to update the row.

If you called the Execute method without the dbFailOnError option you'll get no error, but it's not good practice to ignore errors per se; it's best to handle errors and deal appropriately with the specific error, which in some situations might be to simply ignore it of course.

Ken Sheridan,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

Did this solve your problem?

Sorry this didn't help.



You can unsert a line


DoCmd.SetWarnings False


before, and


DoCmd.SetWarnings True


after a series of commands that you want to run silently.

Kind regards, HansV

Did this solve your problem?

Sorry this didn't help.

Question Info

Views: 3,770 Last updated: February 6, 2018 Applies to: