• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

Suppress Access-generated errors

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
Tom Knowlton
Asked:
Tom Knowlton
  • 12
  • 8
  • 2
  • +4
1 Solution
 
lynersCommented:
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
 
Tom KnowltonWeb developerAuthor Commented:
The Access generated message still displays.
0
 
lynersCommented:
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
funkeCommented:
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
 
funkeCommented:
sorry issue the off command to start and the on at the end to turn them back on
0
 
lynersCommented:
Is that the messages you are getting... Warning messages? If so. Funke has the answer. Docmd.setwarnings false
0
 
Tom KnowltonWeb developerAuthor Commented:
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
 
Tom KnowltonWeb developerAuthor Commented:
SetWarnings also turns off my custom error handling
0
 
Arthur_WoodCommented:
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
 
fontmasterCommented:
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
 
Tom KnowltonWeb developerAuthor Commented:
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
 
lynersCommented:
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
 
lynersCommented:
Fat fingered the f key there. Should be killing.
0
 
Tom KnowltonWeb developerAuthor Commented:
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
 
lynersCommented:
Are you adding/editing the record in code or through a form?
0
 
Tom KnowltonWeb developerAuthor Commented:
I am building the APPEDND query from scratch, then running it with a DoCmd.RunSQL statement.
0
 
Tom KnowltonWeb developerAuthor Commented:
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
 
Tom KnowltonWeb developerAuthor Commented:
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
 
lynersCommented:
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
 
Arthur_WoodCommented:
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
 
Tom KnowltonWeb developerAuthor Commented:
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
 
lynersCommented:
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
 
Tom KnowltonWeb developerAuthor Commented:
Thank you all for you suggestions.

Tom
0
 
joekendallCommented:
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
 
maryamkaCommented:
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
 
Tom KnowltonWeb developerAuthor Commented:
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
 
fontmasterCommented:
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
 
Tom KnowltonWeb developerAuthor Commented:
Thank you fontmaster.

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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 12
  • 8
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now