trap access vba messages

0
votes

I'm developing in Access 2007 after a period doing other things and am trying to trap messages to the user. One function in a form modifies a row in a table. The relevant bits of the code are

On Error GoTo PROC_ERR
...
DoCmd.RunSQL szSQL
...
PROC_ERR:
  MsgBox "Error: (" & Err.Number & ") " & Err.Description, vbCritical

The SQL statement is correct and he system pops up with a "You are about to update 1 row ..." message (number 10505). I want to trap this and replace it with my own warning message. However, my function doesn't trap an error on this message. Nor does it do so on the message that appears if the user clicks "no": "Run-time error 2501. The RunSQL action was canceled."

In VBA options I have for the moment set Error Trapping to "Break on all errors".

What am I missing here? Have I failed to set some other option in Access?

2
vbams-access

2 Answers

1
votes

I would recommend to do not use RunSQL. It requires DoCmd.SetWarnings False, which may affect further warnings in case if it won't be followed by DoCmd.SetWarnings True, for instance after error. Also you won't be able to read quantity of affected rows. Use 

db.Execute szSQL, dbFailOnError

instead, it will allow to trap errors and analyze database state after query execution. Without dbFailOnError option errors won't be raised.

0
votes

That message is not an error.

You can silent it with 

 DoCmd.SetWarnings False

Remember to set them on again.