Solved

detect docmd error even if setwarnings  is OFF

Posted on 2011-09-07
3
350 Views
Last Modified: 2012-08-14
Often, my vba programs need to analyze the cause of an error and take appropriate action.



But this is not easy when the error comes from a docmd method, when setwarnngs is off.

I want the my vba to see the EXACT same text that a user would have seen if setwarnings had been ON, but I do not want the user to see that text.
 


 For instance the following code does not work

on error resume next
docmd.setwarnings false
docmd.xxxxxxxxxxx
myerror$ = error$   '  THIS will always be blank. It fails because Access thinks that setwarnings false means "don't throw any errors".
docmd.setwarnings true
on error goto myerr_routine
if myerror$ like "*unable to append*" then ....

In the past I have used many "tricks" to get around this problem but I am looking for a general solution that will work for all the many dozens of methods that could be invoked.  



rberke
0
Comment
Question by:rberke
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 36499089
Let me suggest a safer way ... that gets away from the SetWarnings False - which can have many undesirable ramifications.  

The problem with SetWarnings False is ... if one (or more) of the queries fail for *whatever* unexpected reason ... you will never know this because SetWarnings False masks out *all* errors, which can lead to you *thinking* that everything ran ok, when in fact it did not.

The approach below has two advantages:

1) You do not get the warning prompts - just like if you use SetWarnings False ... AND ...
2) If an Error DOES occur, it will be trapped and you can act accordingly.

Private Sub btnRunQry_Click()
   
    On Error GoTo btnRunQry_Click_Error
    CurrentDb.Execute "SomeActionQuery1", dbFailOnError
    CurrentDb.Execute "SomeActionQuery2", dbFailOnError
    MsgBox "Operation completed successfully!"

btnRunQry_Click_Exit:
    Err.Clear
    Exit Sub

btnRunQry_Click_Error:
   MsgBox "An error occurred:" & vbCrLf & Err.Number & vbCrLf & Err.Description
   GoTo btnRunQry_Click_Exit
End Sub

******

Another problem with SetWarnings False is ... if you *forget* to execute SetWarnings True ... OR ... some other error does occur and SetWarnings True does not get execute ... you have a big problem ... because, False stays in effect unit you close and reopen Access.  

So, for example if you are working in design view of say a form (or table, etc) ... and you make some changes ... and hit Save ... you are NOT prompted for 'Do you want to save these changes ...". I will just silently save the changes ... which may NOT be what you wanted to do!!
0
 
LVL 5

Author Comment

by:rberke
ID: 36499331
yes, that is an alternative when I am running a query/sql statement.

But, there are a bunch of other docmds methods.  Any suggestions for those?



0
 
LVL 5

Author Closing Comment

by:rberke
ID: 36943496
The partial answer suggested an alternative that works for open query.

I will resubmit the question at a later date for alternatives to other docmd functions.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

689 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question