Think of the form's BeforeUpdate event as the flapper at the bottom of a funnel. It is the LAST event that runs before a record gets saved. And nothing can bypass it regardless of who or what caused the save to happen. It only runs once and is immediately followed by the AfterUpdate event assuming the save was successful. Modifying the record in the Form's AfterUpdate event puts the code into an infinite loop. Current versions of Access recognize the loop when the call stack becomes excessively deep and gracefully exits. Earlier versions simply locked the computer and the screen flickered. If the save was successful, the dirty flag would be turned off and therefore, the event would not run again unless something or someone dirtied the record again. If you cancel the event as in the code I posted, the dirty flag is left set so the user either has to fix the problem or he has to use esc twice to back out the update before he can gracefully exit the form.
99.9% of all validation belongs in the Form's BeforeUpdate event or in procedures called by the form's BeforeUpdate event. You can use each control's BeforeUpdate event for some of it but you cannot do all validation in Control events. So, my standard is to just use the form's BeforeUpdate event and not worry about ever using the control's BeforeUpdate event. If you use procedures such as MaJP suggested, you can use them in the Form's BeforeUpdate event but you STILL have to cancel the event to prevent the record from being saved and you can't do it in the function because functions such as were suggested need to be in standard modules in order to be used by multiple forms.
I would use the control's BeforeUpdate event if I wanted to stop the user at that point if the entry was invalid. For example, if the employee record required SSN and SSN had to be unique, I would validate the SSN for uniqueness in the SSN control's BeforeUpdate event so I could avoid having the user waste his time entering all the other data if I wasn't going to let him save the record anyway. But, even in that case, validation is required in the Form's BeforeUpdate event because if SSN is required, then I can't validate for "required" in a control event because the control level events only fire if the control is entered and in the case of the BeforeUpdate event if the user actually typed something. So, the ONLY place I can reliably force a field to be not null is the Form's BeforeUpdate event. You have a similar problem if you have validation rules that involve multiple fields. For example, it makes no sense for TerminationDT to be < HireDT but you can't validate that in the control events because you can never be certain when to do it. If you do it in HireDT, TermiationDT should be null so you can't do it there. If you do it in TerminationDT, what happens if HireDT is empty for some reason? If you just give a message and leave TerminationDT, what if the user doesn't go back and fix the HireDT? The safe route would be to erase the user's entry in TerminationDT but that isn't very user friendly.
One other trick you need to know is that if a user types something in a control and then backspaces, the control stays dirty and it is not null so IsNull() will return false. There are two ways to check for both Null and a ZLS in the same expression:
1. If Len(Trim(Me.SomeField))> 0 '''''' the field is not null and not ZLS
2. If Me.SomeField & "" <> "" ''''' the field is not null and not ZLS
I use the second because old habits die hard and functions, especially two nested take infinitesimally more time to execute than a concatenation operation. It probably makes no difference in the greater scheme of things but I spent too many years creating CICS transactions used by thousands of people simultaneously and milliseconds count in that kind of environment so I always code the least expensive instruction when there are options.
You will find many people suggesting other events. They all require extra code in multiple events and there is still a likelihood that they missed something. I can almost always break these apps. And if I can break them, so can the user. I once removed 5,000+ lines of validation code from an app. The programmer did not know what I have just told you. He had code in five control level events of every single control and the code was validating not only the control it was in but all "previous" controls in the tab order and still bad data was being saved. Why? You can display all the error messages you want but if you don't close that pesky funnel at the end of the Form's BeforeUpdate event, the bad data WILL be saved unless it violates some RI rule and the data base engine prevents the save.
Do NOT put validation in multiple places. You just make more work for yourself and you run the risk of modifying one procedure and not the other. It also confuses people who have to maintain the db after you and when you do stuff like this to them that can trap them into doing the wrong thing, they will curse you and your offspring to eternity.