Trap Query 'Errors' (messages)

[dgunner71]

All,

I thought it might be beneficial to start a separate topic for this topic.

Is there any way to programmically 'trap' this notice? (Specifically for 'Lock Violations')

MyDb didn't update 0 field(s) due to a conversion failure, 0 record(s) due to key violations, 1 record(s) due to lock violations, and 0 record(s) due to validation rule violations. (image attached)

My goal is to have VBA provide a much nicer and meaningful message if this should happen.

An proposed example is below:

If {magic code to see if record is locked} = True then
   Msgbox "Custom Failure Message Here."
   Exit Sub

Else:
   DoCmd.RunSQL "SQL Code here"

EndIf

Thanks for any advice -

Kind regards.

Gunner

[NeoPa]

I'm not sure how you get the error code, but if you use .Execute(strSQL , dbFailOnError) then you can certainly see if it runs and, with .RecordsAffecte d, see how many records it processes if it does succeed.

[dgunner71]

Thanks for your reply, NeoPa!

I haven't used this method to run SQL statements from within VBA and it looks to be exactly what I'm hoping to accomplish. I've included a good link here from Utter Access that provides a good comparison of DoCmd.RunSQL vs. CurrentDb.Execu te. http://www.utteraccess.com/wiki/inde...SQL_vs_Execute


Thanks again!

Gunner

[dgunner71]

Just an update - this code works perfectly -
Thanks again!

Gunner

[jimatqsi]

Gunner, I found that link very educational. Thank you.

Jim