Ask a new question

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 ?

 


 

Answer
Answer
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,
Newport, Shropshire, England

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

2 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

You can unsert a line

 

DoCmd.SetWarnings False

 

before, and

 

DoCmd.SetWarnings True

 

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

---
Best wishes, HansV
https://www.eileenslounge.com

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated October 5, 2021 Views 6,112 Applies to: