Solved

VBA Errors, Jet Errors Access Errors, and Form Errors

Posted on 2010-11-22
29
897 Views
Last Modified: 2013-11-28
I believe that MS Access is three components rolled into one 'application':
- MS Access UI (ie, tables, queries, forms, reports, etc.).
- Jet DB Engine.
- VBA.

I also believe that each can generate its own errors.

What I don't know is:
- How can you distinguish a Jet error from an Access Error?
- Which Jet and Access errors can fire the Form Error event?

Notes:
- MS Help says: "The Form Error event occurs when a run-time error is produced in Microsoft Access when a form or report has the focus. This includes Microsoft Jet database engine errors, but not run-time errors in Visual Basic." I'm not sure if this means that the Form Error event fires for both Access errors and Jet errors, but I would be surprised if it didn't.

- An old list of all Jet Errors is available in Appendix D of: D Haught and J Ferguson, Jet Database Engine Programmer's Guide.
0
Comment
Question by:Milewskp
  • 10
  • 6
  • 5
  • +3
29 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 34196899
Does it really matter?  Trap the errors and 'deal' with them.

In general, if you are going after data, you are more likely to get a Jet/DbEngine error than an Access front-end error.

However, you might get both.  There is an Errors collection that you can iterate to find *all* the errors that occurred.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34196996
Well, if you run code in VBA and it runs an SQL string, you may get a Jet Error, but that error can only manifest itself in VBA, (Because that is how you ran it).

For example, if you run a query directly from the DB window (Navigation Pane), and you have a SQL syntax error, you may be thrown back into the SQL.
If you run the same query in VBA, you will get an error, but you will be thrown back to the code, not the SQL.

So as aikimark, states, "Does it really matter?  Trap the errors and 'deal' with them."

JeffCoachman
0
 
LVL 1

Author Comment

by:Milewskp
ID: 34201163
Hi folks,
<Does it matter?>
These are academic questions, but it obviously matters to me. The second also seems to matter to mx (see http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26615454.html  ID:34190323)

<There is an Errors collection that you can iterate to find *all* the errors that occurred. >
Where can I get more info on the Errors collection, I've never heard of it before.

0
 
LVL 84
ID: 34201541
Access is made up of a lot more than 3 components, but you're right - Access is a development platform/environment with many of the pieces of the puzzle are very tightly coupled. That's why it's not really possible to separate the database errors from the UI errors, for example. In other environments, where you are much more responsible for managing the data separate from the UI (a standard VB6 unbound application, for example) the distinction is much more obvious. You would likely have a class that sits between the UI and the database (a "data layer") that would manage the interaction between the UI and the database. Errors raised on the database side of things would be obvious, as would errors raised through the UI, and you could deal with them as needed, based on their source. Access tends to hide these things from you, which is good in some ways but can obviously bring about some questions such as this.

So if you manage the data yourself, and work through unbound forms, then you could make this distinction. Working with bound forms, however, I doubt you could ever truly make the distinction due to the tightly coupled nature of the components of Access.

mx asked for a complete list of errors which could be 'generated' from the form's error event. I would suspect that list would include nearly EVERY error possible, since as we've all seen, Access forms can do some amazing things, and just when you think "That can't be done" someone comes along and *figgles out a way to manage it.

<Where can I get more info on the Errors collection, I've never heard of it before>
Here's a good starting point:
http://msdn.microsoft.com/en-us/library/bb258159(office.12).aspx

*figgle: My 3 year old grandson's way of saying "figure", as in "figgle out". :)
0
 
LVL 75
ID: 34201580
"I would suspect that list would include nearly EVERY error possible, since as we've all seen"
Well, ummm ... I don't think so ....
Like Error 5 or 13 for example I doubt would be rendered via the Form Error event.

mx

0
 
LVL 84
ID: 34202102
I may have misread it, but my intent was to say that ANY error could conceivably be managed by the Form's Error event, including Error 5 or 13. Depending on the way the app is written, ALL errors could ultimately pass through that error handler.

As to errors "fired from" the Form Error event - which is where I believe I've misread - then I'm not sure I understand exactly what the author is asking. Once you've hit the Form's Error handler, I would suspect that ANY error could be "fired", depending on what you do from that point forward. For example, if you have a SELECT CASE statement that manages your error depending on the Err.Number, then it's conceivable that you could pass invalid values to a subroutine (like a audit or error logging routine) and you could then trigger any event (including 5 and 13).

If we're discussing something different here, then perhaps the author could clarify exactly what they mean.
0
 
LVL 75
ID: 34202120
This is is basically prompted from a comment in made in a previous Q, wherein I said I would "like to know (a list of) all possible errors that are rendered ('fired') via the Form Error event.  

Of all possible Microsoft Access errors ... in the attached mdb, compliments of Luke Chung of FMS and a table and some comparison queries I added ... only a subset of those are actually rendered via the Form Error event.   The remainder would never be rendered via the Form Error event.

That's what I was asking, and what is part of (and prompted) this question.

mx
Errors2000-2007-MX.zip
0
 
LVL 84
ID: 34202153
Perhaps I see this differently, but the Error event doesn't "fire" any errors (unless you use Err.Raise within that event, or code in that event causes an error). It simply reacts to errors which may occur and are not handled through some other means (i.e. in proc error handlers).

Thus my point re: "rendering" errors in the form's Error event.

If we're talking about errors which can be manifested/raised/caused/fired from within the Form Error event, then I'd suspect that any VBA error would be on that list.

If we're talking about which errors might be "caught" by the Form's Error event, then that's a different matter entirely. As you said, I doubt an "Invalid Procedure Call" would ever make it to the Form's Error event, but a function called FROM the Form's Error event could certainly cause VBA error #5.
0
 
LVL 75
ID: 34202188
"unless you use Err.Raise within that event, or code in that event causes an error). It simply reacts to errors which may occur and are not handled through some other means (i.e. in proc error handlers). "

"t simply reacts to errors which may occur and are not handled through some other means (i.e. in proc error handlers)."

Sorry, but that's not correct.  Also, didn't you tell me sometime back that you never use the Form Error event?

Anyway, an example of errors that can only be trapped in the Form Error event with a Form bound to a table or query ...  are ... say Text Box has an Input Mask, and the user enters an inappropriate value for that mask.  Error 2279 will occur - the value of DataErr in the example below.  Another error only rendered in the Form Error event a Required Field error ... 3314.  There are many others, including one of the Write Conflict errors (7787 I think?).

Private Sub Form_Error(DataErr As Integer, Response As Integer)
   
    Response = acDataErrContinue
    Select Case DataErr
        Case 2279
            MsgBox "some friendly message about Input Mask error"
        Case 3314
            MsgBox "some friendly message about Required fields"
        Case cccc  
            MsgBox "some friendly message"
        Case Else
            MsgBox "An unexpected error has occurred: " & DataErr & " " & AccessError(DataErr)
    End Select
   
End Sub

mx
0
 
LVL 84
ID: 34204520
Please indicate what's not correct.

I use the Form's Error event as a "catchall". I include error handling on all functions and subs, and only use the Form's Error event to manage errors that occur when the form has the focus.

The Form's Error EVENT is just an Event, like Load or Activate. It reports the DataErr and allows you to specify a response. That's all it does. According to MSDN, it's not raised from "run-time errors in Visual Basic or errors from ADO.during" (from here: http://msdn.microsoft.com/en-us/library/bb214801(office.12).aspx). This would seem to be the start of a "list" that doesn't raise the Form's Error event.

It will be fired when the form's code module encounters an error, and when the access codebase "raises" that event. There would be a limited subset of events which would cause this to be "fired", it would seem (see my earlier comment re: errors manifested/raised in the Form's Error event). However, once IN that event, then ANY error could conceivably be caused/rendered/fired etc from inside that event (depending on what code is being run in the Error event).




I don't use Form Error as my ONLY error handler. I use it as a catchall, but use error handling at the procedure level to manage errors there.

<Anyway, an example of errors that can only be trapped in the Form Error event with a Form bound to a table or query >

0
 
LVL 57
ID: 34205271
@LSM,

 <<It will be fired when the form's code module encounters an error, >>

  You sure about that? Maybe I mis-reading that, but I've always been under the impression that the forms Error event only fired when errors occured that were not under your control, but Access's.  For example, a table level validation rule that fails on a bound form.

  Basically anything a form might do with built-in logic that is not directly under your control.

JimD.
0
 
LVL 1

Author Comment

by:Milewskp
ID: 34205312
Hi LSM,
<once IN that event, then ANY error could conceivably be caused/rendered/fired etc from inside that event>
My quesiton was:"Which Jet and Access errors can fire the Form Error event." By fire, I mean cause. As you say, it's not raised from run-time errors in Visual Basic, so VBA runtime errors do not fire the event.
0
 
LVL 75
ID: 34207434
"<<It will be fired when the form's code module encounters an error, >>"
Sorry, but that is not the case as I already explained.

"However, once IN that event, then ANY error could conceivably be caused/rendered/fired etc from inside that event (depending on what code is being run in the Error event)."
Again, not the case, sorry.

"I don't use Form Error as my ONLY error handler."
Of course not, nor did I (or do I) imply that I do.

My main point is ... that there are some errors that are *only* rendered in the Form Error event ... you cannot trap them elsewhere ... again, per the examples I noted above, like an Input Mask error.

"Error event only fired when errors occured that were not under your control, but Access's.  For example, a table level validation rule that fails on a bound form."
Exactly.

"Basically anything a form might do with built-in logic that is not directly under your control."
Pretty much.

The Form Error event is not ... a 'catch all'.  Specific errors occur (are trappable) *only* in the Form Error event.  Again the example of Error 2279.  You cannot trap that for example in a BeforeUpdate event of a Control or the Form.  If you are not trapping it in the Form Error event per my example above (and below),  then you just get the generic Access error message, instead of a friendly one that you can display by trapping it.  

Here is a more expanded example. In this example, these are errors that I 'expect' to occur because of user actions, and consequently want to trap *and* display a friendly message:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Beep
    Response = acDataErrContinue
   
    Select Case DataErr
       'Note: Form error 7753 occurs when a form level validation rule, if present, is not met.
        Case 2279
            MsgBox "The format of the value you have entered is not appropriate for the Input Mask on this field." & Chr(13) & Chr(13) & _
                   "For example:" & Chr(13) & Chr(13) & _
                   "All 9 digits are required for the ISVC and SAN fields, including any leading zeros. You do not need to (and should not) enter the dashes." & Chr(13) & Chr(13) & _
                   "If you need to cancel the entry in this field, then after you click OK, highlight all the numbers and use the Delete key or backspace over all the numbers, clearing them out.", _
                   16, Me.Caption
       
        Case 2237
            MsgBox "You must select an item from the list.", 16, Me.Caption  ' Combo box NotInList err!
           
        Case 2113
           'In appropriate data type ...
            MsgBox "The value you have entered is not appropriate for the data type of this field." & Chr(13) & Chr(13) & _
                   "For example:" & Chr(13) & Chr(13) & "The data type is Date and you did not enter a valid date?" & Chr(13) & _
                   "The data type is Numeric or Currency and you entered a text value?", 48, Me.Caption
       
        Case Else
            MsgBox "An unexpected error has occurred. The error number is:" & Chr(13) & Chr(13) & DataErr, 16, Me.Caption
    End Select

Form_Error_Exit:

    DoCmd.Hourglass False
    Exit Sub
End Sub

==========

Overall, I've been doing this for probably 15 years, so I have a pretty good idea of how it works.

mx
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:Milewskp
ID: 34208278
hi mx,
Suggestion:
I would use the Case Else to set Response = acDataErrDisplay and log the error. I could then periodically review the error log, and if an error comes up often enough, I could add a Case statement for it.
0
 
LVL 75
ID: 34208664
"acDataErrDisplay and log the error."
That doesn't log' an error, but not sure what you mean by that.  acDataErrDisplay, if used will cause Access to display the generic error message.

acDataErrContinue allows you to display the message you want, and skips the generic message.

What I've more recently done is:

        Case Else
              MsgBox "An unexpected error has occurred. The error number is:" & Chr(13) & Chr(13) & DataErr & "  Description: " & AccessError(DataErr) , 16, Me.Caption

mx
0
 
LVL 1

Author Comment

by:Milewskp
ID: 34209522
Hi mx,
<use the Case Else to set Response = acDataErrDisplay and log the error. >
what I mean is this:
     Case Else
          Response = acDataErrDisplay
          Call ErrLogger(DataErr)
          ....
0
 
LVL 75
ID: 34209547
Problem is ... you get the honky Access error message instead of a nice one you could display ... as I've show.  So instead, just:


     Case Else
          Call ErrLogger(DataErr)
          MsgBox "An unexpected error has occurred. The error number is:" & Chr(13) & Chr(13) & DataErr & "  Description: " & AccessError(DataErr) , 16, Me.Caption
 
0
 
LVL 84
ID: 34216529
Note that I'm referring to the Access Form Object class module, NOT the VBA module that is attached to an Access form after it's been created.

<You sure about that? Maybe I mis-reading that, but I've always been under the impression that the forms Error event only fired when errors occured that were not under your control, but Access's.  For example, a table level validation rule that fails on a bound form>

What I"ve been trying to say is this: the Form's Error event is fired from the Access Form object's Class Module. That's all. As to WHICH errors would be managed by that method, I've not called into doubt any other claims. As mx has stated earlier, and as is very evident from many other sources around the web, there are several errors which can ONLY be managed through the Form's Error event. That's fine.

Once IN that Error event, code that is called INSIDE THAT EVENT - that is, a Select case that YOU write, perhaps, or an external function that YOUR VBA code calls - could render ANY OTHER ERROR. This has absolutely nothing to do with errors which could be managed by the Form's Error handler.

My main point in raising this issue what that it was unclear (to me, anyway) exactly what the author was asking for. Obviously I've interpreted this differently from other Experts here, and the author has returned to clear this up.

"<<It will be fired when the form's code module encounters an error, >>"
Sorry, but that is not the case as I already explained."

How can it be anything else? An Access form is GENERATED FROM A CLASS MODULE. Events are raised through that class module and "called" back in the UI layer. If you're handling errors via the Form's Error EVENT, then something must raise that event. We don't have access to the underlying codebase, but Class Modules behave the same regardless of the code language used, and Events are Events.

Perhaps it's not clear what I meant, and perhaps I should have written: "It (the Error Event) will be fired when the ACCESS FORM code module raises the Error Event". Note I'm not referring to the Access Form's Code Module (i.e. the VBA Module attached to the Form Object) but rather the class module that is used to actually build the Access form.

""However, once IN that event, then ANY error could conceivably be caused/rendered/fired etc from inside that event (depending on what code is being run in the Error event)."
Again, not the case, sorry."

Again, you're not reading what I'm saying, sorry.

Once the Form's Error event is fired (from the Form Class module), you could conceivably encounter/raise/manifest any other error. That does not mean that the Form's Error event will be raised for any error, and that's not what I've been saying. We all agree that the Form's Error event is the only place to manage certain errors (like the 2279 you mention). I've not called that into question.

My point is that AFTER Error 2279 is raised, and AFTER the Access codebase has raised the Form's Error event, you could conceivably encounter any other error, depending on what happens inside that Error event.
0
 
LVL 75
ID: 34219073
"My point is that AFTER Error 2279 is raised, and AFTER the Access codebase has raised the Form's Error event, you could conceivably encounter any other error, depending on what happens inside that Error event. "

"Once the Form's Error event is fired (from the Form Class module), you could conceivably encounter/raise/manifest any other error."

"Once IN that Error event, code that is called INSIDE THAT EVENT - that is, a Select case that YOU write, perhaps, or an external function that YOUR VBA code calls - could render ANY OTHER ERROR. This has absolutely nothing to do with errors which could be managed by the Form's Error handler."

Well ... yeah, but ... not seeing how that is relevant to what I was saying. Of ***course*** that could happen, just as it could in *any* error processing/trapping code.

"As mx has stated earlier, and as is very evident from many other sources around the web, there are several errors which can ONLY be managed through the Form's Error event. That's fine."

So, I guess we are in agreement then ?

However:

"I may have misread it, but my intent was to say that ANY error could conceivably be managed by the Form's Error event, including Error 5 or 13."

Unless I'm misreading ... that statement is not really correct.  Those errors, and most others, are not rendered by the Form Error event.  They would only occur within the Form Error event if ... well, your error trapping code  (maybe a call to yet some other function) ... screws up, for lack of a better term.

"If you're handling errors via the Form's Error EVENT, then something must raise that event."

Yes, a *subset* of all possible errors, several of which I have mentioned.  The fact that some other error ... * could * subsequently occur while in the Form Error event, as a result of the error trapping code ... but that really should not happen unless there is a problem with the error trapping code itself.

Anyway, I know exactly how the Form Error event works, because I've been using it in the manner described above for many years ... successfully.

In a previous Q by Milewskp, I made the comment that ...  I would like to know which *subset* of errors, per my context above, can be rendered in the Form Error event, which to some extent ... led to this question.

mx


0
 
LVL 45

Expert Comment

by:aikimark
ID: 34247988
>>not answered and abandoned
I object to your close request.

* Several experts have answered your question.

* Does this mean you are abandoning this question?

0
 
LVL 1

Author Comment

by:Milewskp
ID: 34248159
Hi aikimark,
<Several experts have answered your question.>
My questions were:
- How can you distinguish a Jet error from an Access Error?
- Which Jet and Access errors can fire the Form Error event?
What were the answers?

<Does this mean you are abandoning this question?>
Sorry, I should have said Inactive. What I mean is that according to EE, this question is 'Inactive'.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34248237
You may click the request attention link and ask for more expert input.  Since your last comment was last Wednesday, I think you could kick-start this discussion by another substantive comment, rather than a close request or RA link click.

That said, the experts that have participated in this thread are very experienced.  You should probably be more active with your participation in order to get the information they have (and you need).
0
 
LVL 84
ID: 34248243
"If you're handling errors via the Form's Error EVENT, then something must raise that event."

I was actually referring to the Form's Class module, but in essence, we're saying the same thing.

There is a specific set of errors which can ONLY be managed through the Form's Error event. However, once in that Event, any error could possible be manifested/raised from code that runs inside the VBA error handler (NOT the Form's Class Error event).

"I may have misread it, but my intent was to say that ANY error could conceivably be managed by the Form's Error event, including Error 5 or 13."

My use of the term "managed" is incorrect. I should have said any error could conceivably be raised once Form's Error event has been invoked.

"Yes, a *subset* of all possible errors, several of which I have mentioned.  The fact that some other error ... * could * subsequently occur while in the Form Error event, as a result of the error trapping code ... but that really should not happen unless there is a problem with the error trapping code itself."

Precisely. And we all know about things that "should not happen" ... they always seem to happen at just the wrong time!

All in all, I think we're in agreement, and you certainly have more experience with the Form Error event than do I. Most of my code in Access over the past few years has been in unbound mode, so I tended to deal with errors in my data layer or class modules.
0
 
LVL 75
ID: 34250471
I strongly object to *deleting* this Q.  

There is a LOAD of good information here on the subject, and I did not make all of my posts only to have it deleted later, besides already adding to my KB.  And noting that points are a non-issue!

Wherein I agree that so far, the Q has not been specifically answered per se ... only because non of use have found the magic 'list' of what is what ... which I would love to locate so to speak.  So, lets compromise for now ... and have Milewskp Accept his post @ http:#a34248159 (or another one) ... indicating that it's not completely resolved ... yet.  Because I want this list, I will continue to search.

"My use of the term "managed" is incorrect. I should have said any error could conceivably be raised once Form's Error event has been invoked. "
Ok then.   And of course, that could happen in any ... error trapping/processing routine.

"All in all, I think we're in agreement, "
I guess we are now ... happy day !

mx
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34260138
Although the advice/approach might be sound, I don't think my comment qualifies for acceptance consideration (on technical merits alone).
0
 
LVL 75
ID: 34261633
4) PAQ the question and store it in the knowledgebase, refunding the points
Yes!

Do not delete this Q.  It's already in my KB.

mx
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 0 total points
ID: 34261642
And see my post @ http:#a34250471

mx
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

747 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

12 Experts available now in Live!

Get 1:1 Help Now