Solved Validation Rule

[Pat Hartman]

There is another school of thought that says when you use a form, put validation code in each control's _LostFocus

Absolutely not. You can't cancel the lostFocus event so it is not appropriate for validation. See what happens with invalid data if you manage to close the database without leaving the control.

BeforeUpdate routine tests each field in turn and so can get a bit complex. Which way is right? Damned if I know

The code is not more complex by being in the BeforeUpdate event. It is simply all in one place.
HOWEVER - you always need code in the BeforeUpdate event if you want to ensure that controls are not empty. You can't use control events to check if a control has a value because if focus never enters the control, your validation doesn't find the error.

The two BeforeUpdate events are specifically intended to handle validation. If you put validation in other events, you do so at your own peril. I love my story of removing ~ 5,000 lines of code from an application. The developer was trying to validate the data but he didn't know how Access worked. He was very diligent but nothing worked. He kept adding code to new events. He was repeating code so that in the LostFocus event of each control he validated all prior controls as well. So fld1 validated fld1. fld2 validated fld1 and then fld2. fld3 did 1,2,3, etc. He finally ended up with code in at least 6 events for each control of every form. He forced the user to tab from a to b to c and NEVER deviate. But nothing worked? Messages were flying everywhere but the bad data always got saved. Why? he never put a single line of code into the form's BeforeUpdate events so no matter how many messages he displayed, nothing was stopping the data from being saved.

 

[The_Doc_Man]

You can't cancel the lostFocus event so it is not appropriate for validation.

But you can override the explicit or implied .SetFocus that takes you to the next tab stop - and turn the control red and pop up a message box. And you can set a flag that says "The last time someone left this field, the format was bad" so that the .BeforeUpdate event can know that something is wrong.

Pat, there are many ways to skin a cat. As it happens, I presented the preferred method first, but I have seen the other method on this forum. In fact, I have used both at the same time - using the .LostFocus for controlling the forecolor, backcolor, and bordercolor of aberrant values but ALSO using the .BeforeUpdate event to prevent release of a badly formatted record. I do that because of "division of labor" issues. Also because I had a convenient common routine available to effect a color change on a single arbitrary control when needed.

 

[Pat Hartman]

But you can override the explicit or implied .SetFocus that takes you to the next tab stop - and turn the control red and pop up a message box. And you can set a flag that says "The last time someone left this field, the format was bad" so that the .BeforeUpdate event can know that something is wrong.

why not just put the code into the right event? You can code your way out of many bad decisions but why?

Pat, there are many ways to skin a cat.

Yes, but the way with the skinning knife is oh so much more efficient and easier than the one that uses the can opener or the tweezers. If you use the correct event (and there is ONE correct event) you have absolute control. If you use an incorrect event, you might still be able to accomplish the goal but at what cost. I remember watching my husband drill through a kitchen cabinet with a screwdriver so he could hook up the new refrigerator to the cold water line. We were newlyweds in our first house and all he had was a screwdriver. It was a weekend and before the days when hardware stores were open on Sundays (especially in the bible belt).

Generally I wouldn't argue with you because I agree, much of what we do is preference and reaching the goal post is most important. This is not a preference.

You can use the control's BeforeUpdate event - which does not run until the control loses focus for everything you are talking about. Lost Focus runs whether the control is updated or not. Why would you want to use an event that runs every time the control loses the focus even when the user is just tabbing through the form??? Why would you not just use the BeforeUpdate event of the control if you fee the need to set color, etc? But you STILL cannot validate presence by using control events so no matter how you wind yourself up, if you want to ensure that a zip code has been entered, you have to use a form level event and the appropriate event is BeforeUpdate. If you use any other event, you run into a problem if there is an error.

Do me a favor Doc. Put all the validation you want in the lost focus event. Put bad data in the field. Then close the form. Does the bad data get saved??? Yes it does. Can you prevent it? Of course you can because you can code your way out of many bad decisions. But why? If you just put the validation in the right event, you don't need to code your way out of anything. You ALWAYS prevent the bad data from being saved and you do not need code in any other event. Plus, if you make required fields required and you set AllowZeroLength Strings to no, then you can just let the database engine complain about missing data rather than adding your own code to the form's BeforeUpdate event.

 

[The_Doc_Man]

Regarding your last request, Pat, in the case where I did this, I had a belt AND suspenders, so besides the LostFocus actions I ALSO had BeforeUpdate events for the form. So no, bad data wouldn't get saved anyway because you couldn't close the form while it still held unsaved data. You had to SAVE it with valid data or CANCEL the entry before you could close the form. In fact, the available control buttons were dynamic. You weren't given the CLOSE button unless it COULD be closed, and the other "default" close and navigate items were also controlled. As I recall, SAVE and CLOSE could never appear at the same time. The navigation buttons (NEXT and PREVIOUS) were also not visible when CLOSE was not visible, so no one could use a navigation side-effect to save invalid data. And yes, NEXT and PREVIOUS were dynamic and one or the other would be off if you were at first or last record for the table.

I distributed some of the actions to individual controls because of the way I was controlling color as an indicator of good or bad data and because of other reasons. My issue was that with the complexity of the forms - about 30 major ones, all of the validation COULD have gone into a single routine per form, but by splitting out the individual control actions I had smaller - and thus easier to visually verify and debug - action and validation routines. YES, I could have combined them. But for ease of maintenance - and ease of checking the code - I kept them separate. It would have been entirely too easy to lose track of the snippet of code doing the validation for an obscure control when (for a couple of cases) we had 30+ fields to be maintained - mostly for government record-keeping. If the validation was in the individual control events, it was easy to find in Design mode by following the control's .GotFocus and .LostFocus events.

One of the things that happened is that every control had both a GOTFOCUS routine and a LOSTFOCUS routine because I had a dedicated "Help" panel that was maintained by those two events. The GotFocus routine put up a help message specific to that field - which appeared differently when in focus - and the LostFocus routine erased that help as it returned the control to the "not in focus" appearance as modified by the "valid" vs. "invalid" condition. In fact, because of selective role-based locking, record state-based locking, and other considerations, I had something like seven to ten possible states for bound controls, that included different appearance for "focus/not focus" situations, "locked by role", "record closed (completed)", and a special color for new records showing fields "not yet filled in for the first time." Since I used templating, that wasn't that hard to do and - believe it or not - the color coding was well received. Besides which, I had a common color control routine that did the state testing and control coloring in a single call.

Remember, Pat - while I understand a lot of set theory and other types of programming theory, I'm a pragmatist and I was going with what worked in a shop where not every user was a programmer or engineer. The clerks who had to use it needed all the visual cues they could get and I didn't mind providing that. They thanked me for what probably sounds to you like a crazy jumble of code. But I found it easy to debug and manage. The users said they found it easy to use. The boss liked it because he didn't get a lot of complaints. The government managers got their reports in a timely manner. And the IT security people were satisfied that I was locking down things against improper operation. So step back a little, Pat, and allow for different viewpoints caused by different conditions. You know that every shop is the same and every shop is different. Vive le difference!

 

[Pat Hartman]

So no, bad data wouldn't get saved anyway because you couldn't close the form while it still held unsaved data.

The lost focus event does not prevent you from closing the form if it is dirty and therefore saving the bad data. It simply prevents you from leaving the control if you code it that way. Please prove this to yourself if you don't believe me. I told you how. If your application didn't save bad data, it was because you coded your way around it because you had to because your validation code was in the wrong event. I'm using wrong as a fact here, not an opinion. Validation code in the Lost Focus event runs whether the record was updated or not - that in itself is a poor idea. Then, since you don't have a built in method in that event to tell Access to NOT save, you have to work around it. You need to code yourself out of the bag. Of course you can do it. I guess you didn't appreciate my little story from #21 about the 5,000!!!!!! completely useless lines of validation code. That developer to this day probably doesn't know that the BeforeUpdate (control and form) are the events that MS intended for us to use for validation when it created the Event procedure "hooks" where we could hang our code.

The Form's BeforeUpdate event is like the flapper on a funnel. If the flapper is open, the record gets saved. If the flapper is closed, it doesn't. PERIOD. Kinda' like your "old programmer rules". It's a binary rule and the Form's BeforeUpdate event can NEVER, EVER be bypassed. If you have code in it, it is always the last event that runs BEFORE the record gets saved. That means if the user closes the form with the record dirty, the BeforeUpdate event runs. If the user scrolls to a new record and the current record is dirty, the BeforeUpdate event runs. If the user presses your save button to initiate a save, the BeforeUpdate event runs. Therefore, in that event, you have complete control over whether or not a record gets saved. You don't need code in multiple other events where if you miss an event, your bad data slips through no matter how many error messages you raise. Error messages don't stop the bad data from being saved. There are valid reasons for wanting the validation code closer to the error. Hence, the control level BeforeUpdate event. You wanted to use fancy highlighting. OK, that is certainly useful to help the user to focus on the problem. When you built the application you didn't know how the Access event model was designed to be used. OK, you picked the LostFocus event. It didn't work but you persisted and finally put enough code in other events so that you made it work. Go back and look at that app and see how you could have simplified the process by using the two BeforeUpdate events instead of the ones you chose. Count the events you needed code in to prevent the bad data from being saved.

You are really digging in your heels to justify NOT using the events that MS intended we use for validation. I don't understand why. If I look back at some of my earlier applications, I cringe at the poor choices I made before I understood more about the event model. But I would never justify them. It feels like you think you are being personally attacked. Far from it. I am simply trying to add a little clarity to the Form's event model. Each event has an intended purpose. Some events can be used interchangeably on a limited level but there is always one "better" choice.

So step back a little, Pat, and allow for different viewpoints caused by different conditions. You know that every shop is the same and every shop is different. Vive le difference!

I am not arguing against visual clues or because I have a different opinion, I am simply pointing out that you can get the same effect by using the control's BeforeUpdate event. That lets you "skin the cat" in one step rather than five or however many you needed to code to actually trap the errors and prevent Access from saving the bad data.

Doc, keep in mind that beginners are reading this discussion. I am trying to keep THEM from making a poor choice when placing their validation code. They latch on to the worst solution as frequently as the best. Some advice is not simply a different opinion. Some advice is based on an actual better method. You are a competent programmer. You can code your way out of a poor choice.

When you use the correct event for your validation, all it takes to prevent bad data from being saved is:

Cancel = True

When you use the control's BeforeUpdate event, that line of code also prevents you from leaving the control.

You might find playing with my sample database at this link enlightening. I did when I built it. Discovered some very interesting things about events that I never realized before

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[The_Doc_Man]

I am not arguing against visual clues or because I have a different opinion, I am simply pointing out that you can get the same effect by using the control's BeforeUpdate event.

The VERY FIRST PARAGRAPH of my response said that I used the BeforeUpdate as the master stopper and simply used the individual control focus changes to INSTANTLY give someone visual feedback on the presence of an error. What's your problem?

 

[Pat Hartman]

The VERY FIRST PARAGRAPH of my response said that I used the BeforeUpdate as the master stopper and simply used the individual control focus changes to INSTANTLY give someone visual feedback on the presence of an error. What's your problem?

I'm arguing because using the Lost Focus event for validation is wrong and that is a fact, not an opinion. You keep attempting to justify using other events for the validation, explaining in detail what you did. Why try to reinforce the Lost Focus as a choice when it is not a good choice for validation and is actually downright dangerous? Once you realize that you actually need to use the BeforeUpdate event to stop the save, you would just use that and remove all your code from the Lost Focus event because you really only want your validation to run for changes, not when the user is just passing through.

The LostFocus event is there because you might want code to run as the user is "passing through" Maybe you want to highlight the controls as the user enters or exits. That's fine but it has nothing to do with validation. Using it for displaying help as you did is also perfectly valid.

Use Form and Control events for their intended purpose. They are not random. Every one has a specific intention. Pat's hypothesis should be added to Doc's "old programmer's rules".

 

[ebs17]

keep in mind that beginners are reading this discussion

- If you rely on form and control events for validation, you limit your writing (editing) work to this form. It could be that other places in the application need to be edited. Then you would have to repeat your measures completely. This may not be possible for larger applications for reasons of clarity.
- Form_BeforeUpdate is not the last place to prevent saving. If you have rules at table level (validity rules, unique index, the operation of the RI anyway) that new data violates, the database engine will block saving with an error message. However, the message generated can be user-unfriendly, so you have to carry out a preliminary check with your own messages and problem avoidances.
You can also use the KeyPress event to avoid incorrect manual entries on the control.
- Validation becomes user-friendly when the user is not only told that their input is incorrect (no one really wants to hear that), but that their input is corrected to a correct value, as long as this can be interpreted.
Ideally, the correct values are immediately suggested to the user as a default. The user feels better when he does everything right. Pleasant help is welcome. If an ID is created according to rules as described at the beginning, you can use these rules to calculate and suggest this ID.

 

[Pat Hartman]

Where is the "hitting head" emoji when you need it?

Form_BeforeUpdate is not the last place to prevent saving.

I know that English isn't your native language but that isn't what I said. What I said was FOR A FORM - the Form's BeforUpdate event is the last event that runs before a record is saved. I didn't say anything about what validation the database engine might be performing. So, if your form level validation is not comprehensive then an error will be passed to the database engine where RI and table level validation may catch it. If you didn't bother with any of that then the bad data gets saved.

The Form's BeforeUpdate event is like the flapper on a funnel. If the flapper is open, the record gets saved. If the flapper is closed, it doesn't. PERIOD. Kinda' like your "old programmer rules". It's a binary rule and the Form's BeforeUpdate event can NEVER, EVER be bypassed. If you have code in it, it is always the last event that runs BEFORE the record gets saved. That means if the user closes the form with the record dirty, the BeforeUpdate event runs. If the user scrolls to a new record and the current record is dirty, the BeforeUpdate event runs. If the user presses your save button to initiate a save, the BeforeUpdate event runs. Therefore, in that event, you have complete control over whether or not a record gets saved. You don't need code in multiple other events where if you miss an event, your bad data slips through no matter how many error messages you raise. Error messages don't stop the bad data from being saved.

Then I said

There are valid reasons for wanting the validation code closer to the error. Hence, the control level BeforeUpdate event.

I never said ANYTHING about what you might be validating. You can use domain functions to look for duplicates. You can use a loop to check all tagged controls for not null. You can check specific controls for value ranges or rational values like a date of death or birth can't be in the future. Use your imagination. Now you are recommending the KeyPress event. Good luck with that. At least using the on Change event to check keystrokes makes sense.

 

[Pat Hartman]

Then you would have to repeat your measures completely.

Only if you don't know how to create a procedure where you can pass in a form or control object.

 

[ebs17]

What I said was FOR A FORM

Well, a lot of words and discussions about a form and its possibilities easily give the impression that this is the only way validation is possible. A newcomer could bow to this impression and internalize this as the only truth.
I wanted to prevent something from that.

 

[Pat Hartman]

Some threads get long naturally. There is no need to expand every one to book length so you can be "inclusive" despite talking about a narrow subject.

The Access event model is poorly documented and even though I came to Access as an experienced database designer and developer, It was the hardest thing I had to overcome when learning Access. I still have no clue, even after all these years, what some events were intended to do. That is one of the reasons I created the sample I posted the link to earlier. No matter what you think you know about events, I promise you that you will learn something new by playing with that application. Tony ( Uncle Gizmo) worked with me on two of the videos that used it and we found ourselves utterly amazed and even amused by some of the things we discovered along the way.

 

[The_Doc_Man]

I'm arguing because using the Lost Focus event for validation is wrong and that is a fact, not an opinion.

I respectfully disagree because anyone who has done enough programming realizes that there is almost NEVER a "one size fits all" answer. Yet that is something you are pushing hard. I respect you, Pat, and it is NOT WRONG to use Form_BeforeUpdate events to protect your data. I don't disagree that it is a last line of defense at the form level. Yet you would deny earlier lines of defense that help to isolate and identify errors too? I'm perhaps just a teeny bit disappointed. In the final analysis, I don't regret for even a femtosecond indulging in a little bit of overkill in order to assure that my users KNEW UNEQUIVOCALLY where an error was.

 

[ebs17]

a last line of defense at the form level

...before attempting to save into table.
An error returned by the database engine would recur in Form_Error. Some people also ask, why should I check something extra when the database engine already does it?
However, I personally prefer avoiding errors over correcting errors and am prepared to accept some additional effort if necessary.

 

[Pat Hartman]

@The_Doc_Man I really don't understand why you believe that the MS developers who built Access created random events. The events are not random at all. They are intended for specific purposes. Feel free to use them however you see fit.

 

[The_Doc_Man]

Feel free to use them however you see fit.

Which is exactly my point. No more and no less. Use ALL of the tools in the tool box. That's why you have them.

 

[Pat Hartman]

Which is exactly my point. No more and no less. Use ALL of the tools in the tool box. That's why you have them.

Call me crazy. I prefer to use my tools for their intended purpose. That is where we differ. I use a hammer to drive/pull nails. I use a screwdriver to install/remove screws. I use a lawnmower to cut the lawn not to trim the hedge. I unplug appliances before I take them apart. When we use our tools incorrectly, we may obtain our objective or we may cut off our fingers or electrocute ourselves. You are perfectly capable of coding yourself out of a hole you have dug. Novices are not. I can probably survive crushing ice with a hammer but if I use a bronze sculpture to hammer a nail, I am as likely to damage the statue as I am to sink the nail. MS gave you a complete set of tools (events). Each is intended to be used for a specific purpose. Despite your opinion, they are not random. At best, there is minor overlap when they fire one right after the other.

I'm sorry you didn't understand my point.

 

[The_Doc_Man]

I understood you perfectly, Pat. You take a narrower view on things than I do. You are a purist and I respect that. But I have NEVER denied being a pragmatist. Sometimes pragmatic enough to kill two birds with one stone.

The purpose of the Focus routines I mentioned earlier included immediate visual feedback, which required error testing that I could only obtain by doing the testing per control, not waiting for the user to save the form. That meant a per-control test event. AND though I did my best to make the progression of data fields logical and similar to the way things were reported, users COULD mouse their way around the forms. Remember, I also controlled navigation and the "usual" save buttons. I had the forms about as tight as I knew how. So far as I know, once each form got tightened in that way, I NEVER had an invalid data entry. (Not to say they couldn't spell new item names wrong... but everything was logically consistent.)

Since I also had a Form_BeforeUpdate routine to stop erroneous record entry, I didn't need the control_BeforeUpdate(Cancel as Integer) option. I was NEVER going to cancel the control's change. And to me, since I wanted per-control visual feedback, using the focus routines WAS a good tool for the job. I'm sorry you don't understand MY point.

 

[Pat Hartman]

I understood you perfectly, Pat. You take a narrower view on things than I do. You are a purist and I respect that. But I have NEVER denied being a pragmatist. Sometimes pragmatic enough to kill two birds with one stone.

I seem to be speaking a foreign language.

Doc, you keep telling me how wonderful this application was and I am not arguing with you. You were very clever to have made it work. I am just trying to explain that there is a simpler way. You only have to open your mind to see it. I'm not saying that you need to go back and recode the application. That would be silly. If you think your application never, ever saved a bad record, I won't argue with you. But in that case, you covered your six by putting additional code in additional events whereas you only need code in the ONE event if you use the correct event.

I seem to be the one who knows what the various events are intended to be used for. You keep arguing with me. When you have validation logic in the lost focus event, you are validating data that has not been changed as well as data that has been changed --- UNLESS you add additional code to determine what was and was not updated. What you are missing is that the event's BeforeUpdate event also runs when the control loses focus. It does run ahead of the Lost Focus event because you may need to cancel it and that would cascade to prevent the Lost Focus event from running. Therefore, validation you put in the control's BeforeUpdate event does what you want WITHOUT the extra code to prevent the event from running when nothing changed. In addition, by putting the validation code in the correct event, you also get to cancel the event which ALWAYS PREVENTS Access from saving bad data. You cannot do that with the Lost Focus event and I explained why.

I'm sorry you don't understand MY point.

I know what your point is. Your point is that you wanted visual confirmation. I never argued that that was not important. You don't seem to be reading to the end of the sentence to get to where I tell you how to make that happen correctly. I also sometimes want visual confirmation or immediate rejection in the case of a duplicate to keep the user from completing the entire record if I'm not going to let him save it.

In this old application, you had code in a minimum of three events (possibly more), when if you had used the correct event, you would have needed less code (not by much but a little) and it would have been in one event because Access would have had your six. And if you did the validation in two events (once in the Lost Focus so you could raise an error message and color the error and a second time in the Form's BeforeUpdate event so you could cancel the save if you found an error), you ran the risk of modifying one version of the code but not the other should a change have been required. And if you didn't duplicate the validation part of the code in the form's beforeUpdate event, you almost certainly did save bad data. It just didn't happen often enough for the users to recognize what was happening. Most people don't ignore error messages. They go back and try to fix the error. Usually they succeed. So, you would only save bad data if you didn't put code in enough events. You had to control the order of progression through the controls on the form, you had to lock down all the options for scrolling and closing the form. You might have needed code in 6 events to ensure you covered all the situations where Access might try to save the record. But all you actually needed was code in one event You would have had your visuals and you would have prevented bad data from ever being saved.

Every once in a while I dig out an old application to see how I did something and usually I cringe. I probably worked with Access for 5 years before I actually understood how the event model would help me. The documentation on events is pretty poor. In some cases it tries to explain the order in which events fire but the problem with that is, "it depends". While the order doesn't actually change, which events fire might depending on what you are trying to do. For example, if you are just tabbing through the controls you get a series of Enter, GotFocus, Exit, LostFocus events but if you stop to modify data, you end up with a myriad of additional events firing such as the Dirty event, and the BeforeUpdate and AfterUpdate event for the control you changed. we won't even talk about all the "key" events. What is missing is instructions or even suggestions of what type of actions you would code in which events.

 

[The_Doc_Man]

Sorry, Pat. We are talking past each other. I'll leave it alone.