Some Basic Questions - I just want to be sure I understand some things (1 Viewer)

[Umpire]

Just a few things I want to be sure I am understanding properly:
1. In my DB I use a PartNum field set as text. The part numbers consist of a series of numbers (usually 6 numbers but not always.) On occasion the numbers will have a letter suffix (123456-A) or a letter prefix (S123456) or sometimes both (S123456-A) All 3 of these samples would be different parts in our system. If I want to check if an entered part number is one of about 4 different parts so I can have a warning pop-up using a MsgBox, then I would have to do a String comparison rather than a direct If PartNum = "S123456-A" Then MsgBox.....

2. Subs do things, Functions return results that can then be used in a Sub to do other things.

3. If I am checking the contents of a field entered on a form, the best time to check it is using the "Before update" event for that field on the form (See #1.) I am not really sure about this one.

Thanks Again to everyone for helping guide me through this adventure called Access.

 

[theDBguy]

Hi. Data validation is best done in the BeforeUpdate event. If you have a specific pattern, you could create a function to verify the input. Subs and Functions can do the same things, except Functions can return something, whereas Subs cannot. Hope that helps...

 

[Isaac]

I think your #2 basically covers the gist of it. Some people are in the habit of using Functions for almost everything, because:
a) Functions can do things too - they can do whatever you want in the middle of the code, while still returning a value to the calling code
b) This way, you can return error (or other informative) information after 'doing' something

I still use Functions merely to return a value, probably 75% of the time.

Agreeing with dbGuy, your final, absolute validation should be concentrated in the form's BeforeUpdate event. However, considering that you may have a record with many fields to be filled out, it's also nice to--in addition--give users some idea of what is acceptable or not-acceptable, prior to that time. It's kind of a case by case situation.....think of what will provide the best, easiest user experience. If I'm on a website filling out a long form, I kind of like to know that my data is acceptable, field-by-field.........And also, of course, at the final Submit.
Having said that, if your "individual-field-level" validation is heavyweight (slow), this may detract from the user experience, so you have to use your best judgment.

 

[theDBguy]

Just for clarification, I purposely did not mention which BeforeUpdate to use; there are different kinds. You would have to decide which one is the most appropriate for your needs. All I'm saying is that it has to be a BeforeUpdate event.

 

[Isaac]

Just for clarification, I purposely did not mention which BeforeUpdate to use; there are different kinds. You would have to decide which one is the most appropriate for your needs. All I'm saying is that it has to be a BeforeUpdate event.

Curious:
So if you wanted to validate a specific control's entry, approximately at the time the user was filling it out, you'd use the Control's BeforeUpdate event?

I only ask because that would probably be my last choice, coming after Exit and LostFocus, so am curious.

 

[theDBguy]

So if you wanted to validate a specific control's entry, approximately at the time the user was filling it out, you'd use the Control's BeforeUpdate event?

Yes, if for example, you have several controls you want to validate and immediately reject invalid entries, I would recommend using the Control's BeforeUpdate event. If you can wait until all the controls were filled out and only want to validate all entries at the same time, then you would use the Form's BeforeUpdate event.

 

[Isaac]

Yes, if for example, you have several controls you want to validate and immediately reject invalid entries, I would recommend using the Control's BeforeUpdate event. If you can wait until all the controls were filled out and only want to validate all entries at the same time, then you would use the Form's BeforeUpdate event.

Thanks for the info.

 

[Umpire]

I only ask because that would probably be my last choice, coming after Exit and LostFocus, so am curious.

Yes, if for example, you have several controls you want to validate and immediately reject invalid entries, I would recommend using the Control's BeforeUpdate event. If you can wait until all the controls were filled out and only want to validate all entries at the same time, then you would use the Form's BeforeUpdate event.

I love to see these types of discussions when ask questions. It really helps me see the pro and cons of different methods.

Isaac, Just curious as to why you would chose Exit or lostFocus over BeforeUpdate

 

[Isaac]

I love to see these types of discussions when ask questions. It really helps me see the pro and cons of different methods.

I agree. Differing experiences provide rich set of information to all of us.

Isaac, Just curious as to why you would chose Exit or lostFocus over BeforeUpdate

Exit will fire when the user exits the control - for example, most data entry-savvy users will use Tab. So when they press Tab to leave a control (or if they put their cursor in another control), that Exit event will fire - and you can then check what they typed into the control and see if it is acceptable or not, and inform them if not. You may wish to evaluate whether they left a control blank, which would be possible by using the Exit event, but not by using the BeforeUpdate event, if all they did is exit a blank control and not change it.

However, I suspect that this is one of those situations where there are pro's and con's of any of the methods mentioned. For example, and contrary to my position, maybe you want to skip doing the control-level validation, if the user left the control blank - maybe that's actually preferable. Or maybe you'd rather catch it when they left the control - that's why I say, ultimately, it's going to be a case-by-case basis & your best judgment.

I've just usually used one rather than the other, but I don't claim it's necessarily right nor better, just seems to have fit my needs more on my particular GUI's.

But in fact all of this mostly serves to simply reinforce the importance of the Form's BeforeUpdate event: Because if the user neither Updated nor Entered the control in question...........Then neither method would have done much good

 

[theDBguy]

Isaac, Just curious as to why you would chose Exit or lostFocus over BeforeUpdate

Ah, pardon me for jumping in... I forgot to address that part.

Either of those events won't be a good candidate for validating data input. First of all, the main advantage of the BeforeUpdate event is it can be canceled. The LostFocus event can't be canceled, so the input might already "stick" to the field by the time you try to validate it. Besides, if the input is invalid, you might want to keep the focus on the same control, so the user can retry to enter a valid input. I think setting the focus back from the LostFocus event doesn't work, or at least it requires extra code.

On the other hand, the Exit event can be canceled. However, the event fires anytime you leave the control - whether an input was made or not. So, you would be validating the data whether it was just entered or has been there before.

Bottom line is, I'll repeat, the "best" event for validating data input is through the BeforeUpdate event.

There may be other reasons/points I could have missed with the above comment. Cheers!

 

[CJ_London]

Data validation depends on the requirements of the business rules.

For example a form has 2 bound controls. The requirement is that both are completed

or perhaps the requirement is a control has a minimum value or number of characters

or the value of a control is dependant on the value of another - so what happens if the value of the other control is changed?

 

[Isaac]

In cases where I do use the Exit event, I do want to validate the control's content, whether something is there or nothing is there, and whether it was already validated or not - that's not a problem to me.

But I don't really disagree with most of what anyone has said. It's a case by case. If I already know that a field ought to contain a certain type of value, and depending on how long the process of filling out the form is for the end user, I might choose to let them know when they Exit the control.

Either way, the form's BeforeUpdate event is the glue that holds it all together, and can perform a lot more conditional evaluations, as CJ's example mentioned

 

[theDBguy]

Data validation depends on the requirements of the business rules.

For example a form has 2 bound controls. The requirement is that both are completed

or perhaps the requirement is a control has a minimum value or number of characters

or the value of a control is dependant on the value of another - so what happens if the value of the other control is changed?

Just one person's humble opinion, but I would still use the BeforeUpdate event for that.

Re: The requirement is that both are completed - I would probably use the Form's BeforeUpdate event

Re: or perhaps the requirement is a control has a minimum value or number of characters - The Control's BeforeUpdate will work here, and

Re: or the value of a control is dependant on the value of another - so what happens if the value of the other control is changed? - Again, each Control's BeforeUpdate event should satisfy the requirements here

 

[theDBguy]

In cases where I do use the Exit event, I do want to validate the control's content, whether something is there or nothing is there, and whether it was already validated or not - that's not a problem to me.

Hi Isaac. You mentioned earlier that either approach really depends on the user. Some use the Tab key, while others might use the mouse and click around. Either the control's Exit or BeforeUpdate events could get bypassed when the user skips that control, which I agree that the Form's BeforeUpdate event could be your last chance to catch any problems. Cheers!

 

[The_Doc_Man]

In my big security-tracking project, I used the LostFocus event (and GotFocus, too) because I was doing several things.

1. Re-validating the value during LostFocus
2. Determining whether to show "normal" focus or "urgent" focus if someone was about to leave a critical value blank/empty.
3. Because the system colors for controls being in focus were under the domain of user whim, I chose to NOT use system theme colors for control issues. Therefore, every GotFocus and LostFocus would call a routine that would alter the border, background, and foreground colors of any control that had a value. That way, there was NEVER any doubt on my forms as to which control had focus.

Which doesn't invalidate ANYTHING said by Isaac and theDBguy. I used the focus events because I was doing something to the control's appearance REGARDLESS of whether it was empty and REGARDLESS of whether it had been changed. Therefore, the focus events worked for me and I had specific, directed reason to use them.

 

[arnelgp]

on your point #1 on post#1, it is best to have a Combobox rather than textbox.
when the combos' Auto Expand is On, you will see that when you type something
that is in the list, it will show the first match.

you might even bring to further using Code to Limit the combos results.

 

[Umpire]

Fantastic Discussion on various event triggers and when to use them.
However I think I may have used improper terminology. I am not sure if Data Validation is the correct term.
Some parts I deal with have Windows License numbers on them. Currently less than 10 out of over 200 part numbers.
What I am trying to do is when one of those part numbers that should have a license is entered in a form, I first want to ask if a license has been assigned to the part. (It may not depending on what point of the process the part failed) I figured a Yes/No Message Box for this step. If yes, a separate form would pop-up to enter the license number. If no, everything closes and the repair/test information continues as if the part did not need a license.

Where I am stuck is in checking the Part Number after it is entered. It looks like in this case, using the Controls BeforeUpdate event is a good choice. But how do I compare what was entered against the 10 (at most) parts numbers that will require the pop-up form?

I think it would be a StgComp but can I use the field data before the field has been updated? Or would AfterUpdate be better? I do not care about the Part number unless it is one of the 10 I am looking for.
Can I do a compare using Or? Compare field to this String or this other string or this3rd string? Or should I put the 10 part numbers in a table and compare the entered part number against the table and trigger my event if it is present anyplace in the table?

On this May the forth, I ask the Jedi Masters of Access to show me the way.

 

[theDBguy]

In this case, you could definitely use the AfterUpdate event of the control. However, you will have to manage the scenario where/if the user ignores the prompt for a license. i.e. How will you force them to enter one? That's probably where data validation kicks in, and you would use the Form's BeforeUpdate event for it.

 

[Umpire]

In this case, you could definitely use the AfterUpdate event of the control. However, you will have to manage the scenario where/if the user ignores the prompt for a license. i.e. How will you force them to enter one? That's probably where data validation kicks in, and you would use the Form's BeforeUpdate event for it.

Unfortunately I cannot force them to enter a license number as a part number may exist with or without a license number assigned in our system. I may be processing a part before the license has been assigned. We do have other systems in place to verify nothing goes to a customer with out a license being assigned. We are looking at assigning the License earlier in our process so I would not see a part without a license. If that happens then I can use the form Before update event to check for the number.

Can I do a StgComp using Or statements? it is not clear from what I found on line.

 

[theDBguy]

Can I do a StgComp using Or statements? it is not clear from what I found on line.

Show us an example of a possible input and what you would compare it against. Maybe you can use StrComp, or maybe you may need to use regular expressions.