Solved Validation Rule

[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.

 

[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!

 

[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?

 

[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.

 

[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.

 

[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.

 

[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.

 

[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.

 

[The_Doc_Man]

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