Error handling during entry

[Gicu]

I think what the links I posted show is that you cannot trap it in the control's own events so you need to do it in the form's Error event.

Cheers,

[twgonder]

I would suggest you try to trap it in the Form_Error event:

https://learn.microsoft.com/en-us/of...ess.form.error
https://www.tek-tips.com/viewthread.cfm?qid=1023699
https://www.techrepublic.com/article...s-and-reports/
http://www.databasedev.co.uk/custom-error-message.html

Cheers,

I'm getting back to the data entry error issue.
I read each of these articles.

1) They talk about 3022 (trapping a duplicate in an indexed-no-dupes field) but I can only get that error to happen when the record is saved, not during entry. I'm wondering, how many more of these I will run into? I have the field's index property set properly in the table and the error does occur at file time.

2) Most of the samples seem to be based on the idea that a form will only have one field that is required (error 3314). So, the "custom error" messages are useless as shown in the examples. When the error occurs, what can I use to get the offending field's caption?

3) When debugging, how can we get the "Error message" that goes with the DataErr number?

4) Even after handling the error the way I want with my own message, I want the user to be able to continue with entry, if I have to I'll set the field to null in the meantime, and they can enter it later when they know, but before filing. I can't see how to do this with required fields.

[twgonder]

Agree with @Micron & @Minty. All errors trigger the same type of response
In case it helps, see my example database: https://www.isladogs.co.uk/access-er...des/index.html
Or this article provides codes to create your own errors table: https://www.isladogs.co.uk/access-er...ist/index.html

I went to the page, scrolled up and down several times, but I don't see which link to use to download the example database.

Edit: Is that it in the note section?

[twgonder]

The errors are just errors. While an error may only occur due to running code, unless I'm mistaken, that doesn't matter - all errors are in any complete list. Not saying that they're all helpful - such as "Application defined or object defined error" - about the most cryptic Excel error I've seen, and worse, the same number seems to be used with various descriptions.

Your post image is error 2113, and it is in the list.

I'm not saying that what I posted isn't an error. There is a difference between a VBA logic error and an error produced during form control entry. There doesn't have to be any VBA code present for the second situation. The problem is when the error is displayed or the error can be caught during data entry. After entry (which ones will Access catch and which will I have to test for?), at file time? Not all errors seem to show up in Form On Error. So, now I have to go test every type of entry error and sometimes catch it in Form On Error, or I have to write custom code for some (see post #17 and dupes) at After Entry for the field or intercept it when saving the record? Ridiculous.

[Micron]

I'd have to review all again since I've lost track here seeing as how it's been a while since anything was posted before today.

At the moment, I just have to wonder why you wouldn't use either the form BeforeUpdate event to do your validation en masse if you don't want errors to be handled by things such as table field validation (validation rules you create at the table level). I don't get the point of using a form error event (or is it a property? - don't know because I've never seen fit to use it). Review isn't going to happen tonight though.

[twgonder]

I'd have to review all again since I've lost track here seeing as how it's been a while since anything was posted before today.

At the moment, I just have to wonder why you wouldn't use either the form BeforeUpdate event to do your validation en masse if you don't want errors to be handled by things such as table field validation....

That's exactly what I wanted to do (and have done, see image below). But Access keeps trying to validate SOME (not all) field entries before that and displays utterly useless error messages like the entry was invalid for this field, see image below. So, I have to figure out the problem was alpha characters in a date field somehow? I haven't yet found the way to disable incomplete and unwanted error checking at entry time. What I don't want is to have dozens of little error checks sprinkled around in all the forms in the before update event of hundreds of text/combo boxes. If Access is going to validate entry errors at entry time, then it would be nice to have more precise DataErr codes and have all types of errors checked so that I can figure out one way to deal with them all. Or at least understand how to do it with what's there (that I don't yet understand).

[CJ_London]

So, I have to figure out the problem was alpha characters in a date field somehow?

you can restrict entry to certain characters using the keypress event - this example allows numbers and the / character

Private Sub myControl_KeyPress(ByVal KeyAscii As Integer)

  If not ((KeyAscii >= 48 And  KeyAscii <= 57) or KeyAscii = asc("/")) Then KeyAscii = 0

End Sub

obviously this will still allow a user to enter multiple '/' so you would need to modify it to allow only 2. But that still does not make it a valid date which you would need to check in the control before update event

[twgonder]

Here's another one that just boggles my mind. I have a new entry record started. The very first entry field is Nm1 and it's required. I can tab right through it, no Form On Error trapping. I can enter in the second textbox (Nm2), now the record is dirty. Click back in Nm1, and tab through it, no error trapped. Click back in Nm1 and just enter a letter and tab, no error as expected. Tab back to Nm1, delete the letter and tab --NOW ACCESS DECIDES TO TRAP THE FORM ON ERROR?!!!

[twgonder]

So, I have to figure out the problem was alpha characters in a date field somehow?

you can restrict entry to certain characters using the keypress event - this example allows numbers and the / character

Private Sub myControl_KeyPress(ByVal KeyAscii As Integer)

  If not ((KeyAscii >= 48 And  KeyAscii <= 57) or KeyAscii = asc("/")) Then KeyAscii = 0

End Sub

obviously this will still allow a user to enter multiple '/' so you would need to modify it to allow only 2. But that still does not make it a valid date which you would need to check in the control before update event

Thanks for the idea. May I share a little design philosophy, that may help to understand and solve my error dilema?

In the old mainframe development RAD I created, normally I validated every entry. If it was a date field, a date had to be entered. If it was a number field, a number had to be entered, etc. Lengths, ranges, translates (Dlookups), min and max values were all handled in the field defining parameter. I didn't add code to each after entry, the front end run-time part of the RAD (Like Access forms) did all the work, it was written in just one place. Users generally entered top down in sequence. I had predictive analysis that filled in 80% of the fields, so 90% of the time, the user just had to look, see that the default was okay and just press enter. Seventy plus field forms could be filled out normally in less than 90 seconds. That was the goal anyways.

That all goes out the window with Access where users have the ability click all around screwing up the predictive nature of form entry. So, I have to change my philosophy to allow users to take their sweet time filling in a form, and the only benefit I see is that if they don't have the available source data (for some reason, like a customer standing right in front of them giving name and address information out of order like a dyslexic corporal{Sir: Smith, Timothy, Alabama, 123, Birmingham, 10th Street, sir}) they can click/tab to the appropriate field and do entry in a hodgepodge manner.

With the second approach, I have to allow users to do partial and illogical entry and hope they will fix it up before saving. And if they don't, then I have to give an "en masse" notification (as shown in post #21).

As to locking down each field at entry, I've mostly given up that idea since Access doesn't do the job as a front-end app very well (as shown in my posts in this thread). With lots of foreign country data now being used I can't count on a limited number of format strings to work for wildly varying data formats of zip/postal codes and phone numbers, string lengths (makes no sense when a car costs (USD$30,000.00 or COP$144.303.976,59 etc.). As a side note, my old RAD took country (which was entered first) into account for things like phone and zip formats. So, a very detailed procedure needs to be written for going back and validating each particular field for size and value and range and format, etc. and the relationships between fields (i.e., you can´t die on a date before you were born). But, the MS half-ass approach to validating field entry at entry moment is getting in the way of this.

My old RAD had, lots of parameters for the field requirements. This eliminated the need for tons of before update code. Before update field code was needed, but very rarely (it had to be some real weird situation with the data). The before record update logic was limited to the logical interrelationship validation. I wish MS had of thought this all out when they first decided to create Access.

I'm here hoping someone has a better approach to handling validation than a mess of Form On Error and field before update code sprinkled all over the place for hundreds or thousands of fields in forms.

[CJ_London]

Access provides the tools to do pretty much whatever you want. It does not do everything for you since the permutations of possible actions are endless. Pretty sure you have been advised in the past to do your own error checking. And whilst you are at it, you talking of tabbing. But there other ways for users to navigate a form - using the enter key or mouse being the obvious ones.

I look at the validation/required options of a table field as being a final resort to protect the required database structure. I do not rely on it for form or query design. For that I use code - in this case probably the control before update, lost focus or exit events. Be aware that tabbing through a control (or using enter key or mouse), making no changes means the before update event won't fire.

Normally you would rely on the form before update event to check for required since you cannot rely on a user updating a specific control. I know you have forced the issue by making the control tab order 0 so it receives the focus when the form is opened or current - but that does not cover the situation where you have two required fields or entering data in a continuous form.

[CJ_London]

May I share a little design philosophy

Think you need to go back to your old RAD tool since you feel it is so superior.

The example I provided was for you to build on. Easy enough for you to create a public function or class so it is written once - and that function/class can take into account different formats for dates and currencies.

[Micron]

Access keeps trying to validate SOME (not all) field entries before that and displays utterly useless error messages like the entry was invalid for this field,

Then you must have validation rules in table fields, or (related to post 23) you don't understand that when you start a new record and are just tabbing around, you don't have one iota of a record - nothing, zero, zilch, nada. There is nothing to validate. As soon as you start typing in fields you have started a record. Now there is something to validate. Simple, yes?

You're probably not going to like this, but it has to be said so that maybe you take a different approach and save yourself some grief going forward:

Unfortunately, the lack of comprehension for many aspects of the way things work in Access drives you to write stories about how poor your db tool is when it's really the tool user who is the problem. I say that based not on this one thread, but several. Your post about new record validation is a good example. If you simply asked "why can I tab around in a record and there's no validation?" (or something similar) instead of what you do, it would help us to help you. My expectation is that if you maintain your current approach and outlook to your Access issues you're likely to lose your audience. My advice is to keep it simpler (esp. shorter) and try to keep an open mind instead of drawing conclusions when you don't know the reason for what seems to be odd Access behaviours.

[twgonder]

...Unfortunately, the lack of comprehension for many aspects of the way things work in Access drives you to write stories about how poor your db tool is when it's really the tool user who is the problem....My expectation is that if you maintain your current approach and outlook to your Access issues you're likely to lose your audience. My advice is to keep ... try to keep an open mind instead of drawing conclusions when you don't know the reason for what seems to be odd Access behaviours.

I've watched Access since the day it was released. I've well documented that in other threads. I've reviewed dozens of large applications written in Access. They have fallen short of anything I've reviewed that were developed by db professionals. There's no surprise there since we should all know that MS likes to reinvent the wheel with novice programmers. It's not that I can't figure out Access, it's a quite simple system really, the problem is in the "Why the hell did they do it that way?!!!" There's nothing so strange about my forms or design that is causing Access to freak out (especially if one looks at the abomination called NorthWind Traders). Rather than complain about my lack of understanding, how about giving a logical reason why Access functions the way it does with spastic error checking in fields (well documented in this thread)? I'm waiting to be enlightened if you can provide a logical explanation other than "you just don't understand". Not to be snarky, it's just that no other system I've worked with requires so many forums with so many threads to explain how to something that should be quite simple, like error checking.

Another example, have you seen how many ways there are to set something as simple as Tempvars? It makes my head spin. Further, I have to use language like me.ActiveField.Control(0).Caption? Control(0)?!!! WTF?!!!

So, I have an open mind. I've done my research and testing. I'm looking for how others have worked around the spastic design of Access. Enlighten me. I've got a good idea what I have to do, but it's far from an elegant and simple solution that I would strive for.

But wait! I won't make you guess as to what I'm talking about. Try it for yourself with the most simple Errors.accdb I'm attaching. Try some of the things I've mentioned in this thread. Try different nonsense entries (like aaa for a date or number or 9/31/2001 for the date). Can you make sense of it (so that a better developer created error message can be made)? Be sure to enter 1 for MyCode textbox in the form (a new record) and see what doesn't happen (then try it with the code commented out). Tab through the fields, just because you're tabbing (or mousing or entering) through the field, it isn't still required? Answer the comments in the code when it stops. Enter just one field then try the save command button; what is the DataErr for the message? Why wasn't it trapped with Form On Error? How does the developer deal with this error with an alternative error message? Now, go ahead, tell me it's the "tool user" that has the problem! But once again, I want to be open minded, please enlighten me on what the "tool user" is doing wrong in the sample db.

[twgonder]

Think you need to go back to your old RAD tool since you feel it is so superior.....

I've already addressed that suggestion on several occasions.

[twgonder]

Access provides the tools to do pretty much whatever you want. It does not do everything for you since the permutations of possible actions are endless..

How many permutations of a date or number entry can there be? It's a valid date or it's not (ignoring logic constraints). The same with a number. Why such a cryptic message to the user or DataErr to the programmer? I don't expect Access to handle my complex logic validation, but checking for a duplicate entry in an indexed field is hardly a logic validation.