Solved

Suppress Access-generated errors

Posted on 2002-06-17
28
537 Views
Last Modified: 2012-05-04
I have some error handling in place, which is working, but Access is still displaying its own generated message anyway.

I want to completely suppress the Access message and handle the error myself.
0
Comment
Question by:knowlton
  • 12
  • 8
  • 2
  • +4
28 Comments
 
LVL 1

Expert Comment

by:lyners
Comment Utility
Put at the beginning of functions code...
On error goto YourErrorhandling

'At the end of the functions/subs
YourErrorhandling:
     Error_Routine 'Create a function of an error routine

End Function

'In a module
Function Error_Routine(strmodule As String, strfunction As String)
   
    DoCmd.Hourglass False
    MsgBox Err.Number & " " & Err.Description & Chr(13) & "NOTIFY THE HELP DESK!", _
                              vbCritical, "Application Error"
   
    Call Log_error(strmodule, strfunction, Err.Number & " " & Err.Description)

End Function

What I do is make sure that every function has an on error routine. Then in the error routine I have it launch the above function that documents the error to the database and notify's the user what is wrong. Thsi way when there is a problem, I can look at the database to see what the error is. This works great if in a network environment.

0
 
LVL 5

Author Comment

by:knowlton
Comment Utility
The Access generated message still displays.
0
 
LVL 1

Expert Comment

by:lyners
Comment Utility
What error is occuring? Soem of the error handling will have to be handled through the validation text if it is an entry error.

What type errors are you trying to suppress? Code, entry?

0
 
LVL 5

Expert Comment

by:funke
Comment Utility
you can issue the command in your code

docmd.SetWarnings(WarningsOn)


...code


docmd.SetWarnings(WarningsOff)

or you can go to the menu 'Tools' --> 'Options' and on the Edit/Find tab you can check off/on the warnings you want to see.

0
 
LVL 5

Expert Comment

by:funke
Comment Utility
sorry issue the off command to start and the on at the end to turn them back on
0
 
LVL 1

Expert Comment

by:lyners
Comment Utility
Is that the messages you are getting... Warning messages? If so. Funke has the answer. Docmd.setwarnings false
0
 
LVL 5

Author Comment

by:knowlton
Comment Utility
Yeah, I have a record that I am trying to append to a table, but one of the values is invalid.

Access says it can't append the record to the table due to a validation rule violation.

I want to suppress the Access error completely and generate my own error handling message.

Tom
0
 
LVL 5

Author Comment

by:knowlton
Comment Utility
SetWarnings also turns off my custom error handling
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
knowlton,  the "error" that you are getting IS NOT considered to be a RUN-TIME error that is captured by your error handler.  It is also NOT an warning message that you can "turn off" with DoCmd.SetWarnings.  That "error message" is coming from the process of attempting to save a record to the Table, where you have EXPLICITLY set a Validation Rule that is being violated.  If you want to avoid that message, then you MUST do the necessary VALIDATION of the data (for EVERY RECORD), in your code, BEFORE your attempt to save the record.

The purpose of VALIDATION rules in the tables is to "guarantee" that only "SAFE" data is actually stored in the tables.  It is one of your jobs, as a developer, to make sure that when you attempt to save data toi the tabvle, that the data is in fact "safe".  It is a VERY bad policy to attempt to save ANY data, and rely on the Validation rule to catch any invalid values.  In particular, the Validation rule is only tested when the entire record is posted to the table, as a result of the UPDATE of the recordset.  That cannot then indiocate WHICH field was invalid, only that AT LEAST ONE of the fields in the recordset contained invalid data.  That does not help you, if you have several possibly invalid fields, as you have no idea WHICH field (or fields) does in fact have an invalid value.  You should be doing data validation, in your code, before you ever attempt to save the record.

Arthur Wood
0
 
LVL 1

Expert Comment

by:fontmaster
Comment Utility
knowlton,

Validation errors often occur when saving a record because one of the fields (usually a text or memo field) has not been filled in and is left null or empty.

Check the design of the table and make sure every field that has a "Required" property has it set to "No" and every field that has an "Allow Zero Length" property has it set to "No".

If it's really important not to set these properties to "No" for some fields, make sure those fields are being filled with something, even if just a single space, before you try to save the record.
0
 
LVL 5

Author Comment

by:knowlton
Comment Utility
Arthur Wood / fontmaster:

You are both correct and have given some good advice.

What I am looking for is a way to custom-handle the error message and suppress the validation rule violation message, which, apparently I cannot do.

My intent is to gracefully handle the validation rule violation (which generaes a run-time error and kills the entire application) so that people can continue working until I have time to fully analyze and permanently correct the problem.

0
 
LVL 1

Expert Comment

by:lyners
Comment Utility
It shouldn't kill the entire application on a validation rule violation. Something else is wrong here.

What is the validation rule, and what data seems to be fkilling it?
0
 
LVL 1

Expert Comment

by:lyners
Comment Utility
Fat fingered the f key there. Should be killing.
0
 
LVL 5

Author Comment

by:knowlton
Comment Utility
It's a text field.

If you do this:

Gates,Bill

it generates the validation rule violation.

It's not a matter of how to fix it, but rather when will I be able to fix it.  In the meantime I quickly wanted to add some protection so the action of appending the record to the table would not crash the probram.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 1

Expert Comment

by:lyners
Comment Utility
Are you adding/editing the record in code or through a form?
0
 
LVL 5

Author Comment

by:knowlton
Comment Utility
I am building the APPEDND query from scratch, then running it with a DoCmd.RunSQL statement.
0
 
LVL 5

Author Comment

by:knowlton
Comment Utility
The APPEND query works, but weird characters that can crash a query have not been completely protected:

'  as in O' Reily can crash it.
0
 
LVL 5

Author Comment

by:knowlton
Comment Utility
If an error occurs during the APPEND, I want to allow the user to cancel the APPEND and back out of the form, instead of crashing the entire program.
0
 
LVL 1

Expert Comment

by:lyners
Comment Utility
Hmmm.. No quick fix on this. You need to add a fundtion like the following to get rid of punctuation, then add it to your query:

Function remove_invalidchar(Word_to_Check As String) As String

    Dim intwordlen As Integer
    Dim i As Integer
   
    intwordlen = Len(Word_to_Check)
   
    For i = 1 To intwordlen
        If InStr(1, ";,'.:/", Mid(Word_to_Check, i, 1)) > 0 Then 'Add whatever characters are invalid into the InStr clause
        Else
            remove_invalidchar = remove_invalidchar & Mid(Word_to_Check, i, 1)
        End If
    Next

End Function

0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
the problem is that the ' as a SINGLE ' is seen as a remark (Comment), but if you have two of them '', that is fine.

Is this Access 97 or 2000?  If 2000, you can use the Replace function to replace all occurences of ' with '' in the text that you are appending.  If Access 97, then you can code your own Replace Function to do the same thing (very easy function to code).  This same function could also eliminate all other fluky characters, once you know what they are.  That is essentially what lyners has given you in the function shown.

Arthur Wood
0
 
LVL 5

Author Comment

by:knowlton
Comment Utility
I'm in Access 2000.

Yes, we have Replace functions like this that we have custom written to handle this stuff.  I didn't know that Access had something ready made to do this.

The issue here is not WHAT needs to be done (obviously we have to clean the data before the import) but rather WHEN will we have time to fix it, and apply a quick fix, or a "band-aid" so the program can still function after a fatal error.
0
 
LVL 1

Accepted Solution

by:
lyners earned 50 total points
Comment Utility
Couldn't you add the funtion to a module,then go into your query and replace the append fields with the function?

Like

Field: Name
Update: remove_invalidchar(FieldName)

This should only take about 15 minutes max. I think it is the quick fix. I can't think of a way of suppressing the Access warning and then fail.
0
 
LVL 5

Author Comment

by:knowlton
Comment Utility
Thank you all for you suggestions.

Tom
0
 
LVL 11

Expert Comment

by:joekendall
Comment Utility
Instead of using DoCmd.RunSQL, why not try this:

Public Function InsertRecords() As Boolean
    Dim db As DAO.Database
    Dim sSQL As String
   
    Set db = CurrentDb
   
    'Field1 is a Text field
    'Field2 has a validation rule of >100 and
    'is a Number (Long Integer)
    sSQL = "INSERT INTO tblTest(Field1, Field2) VALUES('Joe', 10);"
    db.Execute sSQL
   
    'You could count the number of records to append.
    'Then if the number is not equal to the number
    'that have been affected, you could return False.
    If db.RecordsAffected > 0 Then
        InsertRecords = True
    Else
        InsertRecords = False
    End If
   
    db.Close
    Set db = Nothing
End Function

If you have a validation error, it will not return it to the user. All you need to do is check to see how many records where affected. This should get you started. If you need more help with this, please make it known.

Thanks!

Joe
0
 

Expert Comment

by:maryamka
Comment Utility
I just wanted to add a final comment. This is something I just researched and there actually is a way to replace the standard message with your own. You can find information on this in Access help if you look up 'Response'. Here is one example:


Private Sub Form_Error(DataErr As Integer, Response As Integer)
Const conDuplicateKey = 3022
    Dim strMsg As String

    If DataErr = conDuplicateKey Then
        Response = acDataErrContinue
        strMsg = "Please make sure you have not entered the same item number for two items in your order."
        MsgBox strMsg
    End If
End Sub
0
 
LVL 5

Author Comment

by:knowlton
Comment Utility
Thanks maryamka!

I don't even remember what this question was for anymore, but I'm sure it mattered to me greatly at some point.

:)
0
 
LVL 1

Expert Comment

by:fontmaster
Comment Utility
Seems to me that all these advanced, sophisticated suggestions are trying to crack a peanut with a sledgehammer.

Knowlton's illustration of "weird characters" that cause the problem gives a sample using apostrophes.

I've found that invariably an SQL string that is composed from user's data entered into a text field will crash due to "improper" characters in the string.  In almost all cases, the improper character is an apostrophe, which fouls up a WHERE clause that was carefully built to look like:

    "...WHERE TableField = '" & textEntry & "';"

(The characters after "TableField" are apostrophe followed by double quote, and the characters after the last & are double quote, apostrophe, semicolon, double quote.)

This goes bad if the user's entry contains an apostrophe.  The cure is to replace each apostrophe in the WHERE clause by two double quotes in succession:

   "...WHERE TableField = """ & textEntry & """;"

Now the characters after "TableField" are three double quotes, and the characters after the last & are three double quotes, semicolon, double quote.

The append statement will no longer cause an error message no matter how many apostrophes the user has typed, so no interception or suppression will be needed.
0
 
LVL 5

Author Comment

by:knowlton
Comment Utility
Thank you fontmaster.

I hope I remember this posting next time I run into the issue.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now