Validation Rule does not kick in if user uses tab and no data is entered!

Michael Paravicini
Michael Paravicini used Ask the Experts™
on
I use personalized validation rules on my forms. However, if a field needs to have data (ie may not be empty nor null) and there is no default value and the user uses TAB to enter the data then the validation rule does not kick in and the form will save an empty field. Is there a way to force a 100% the validation rule even if the user has not entered any data (and just uses the tab key)??

Thank you for any help!
Regards Michael
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Yes, run the validation rule(s) at the BeforeUpdate event of the form, and cancel if validation fails.
Distinguished Expert 2017
Commented:
Some validation can be done in the BeforeUpdate event of a control but checking for presence or a relationship with other controls needs to be done in the form's BeforeUpdate event as Gus mentioned.  Rather than have validation all over the place, I tend to put it in just the form's BeforeUpdate event.  That way it is all together and easier to work with.  I only use the control's BeforeUpdate event if I want to provide a validation message as soon as focus leaves the control so for example, if the user enters a value that must be unique, I would check that in the control event so the user doesn't have to fill in the whole form before finding out that the user code already exists.
If Me.SomeField & "" = "" Then
    Msgbox "SomeField is required.", vbONOnly
    Cancel = True
    Me.SomeField.SetFocus
    Exit Sub
End If
If Me.SomeDate < Me.SomeOtherDate Then
    Msgbox "SomeDate must be >= SomeOtherDate", vbOKOnly
    Cancel = True
    Me.SomeDate.SetFocus
    Exit Sub
End If
....

Open in new window

Furthermore, if you require a field to be non-empty, then at the table level you should specify that the field is Required. That guarantees that the user cannot leave the value blank. It does not provide the most user-friendly message, which is where a before_update check can be useful.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Hamed NasrRetired IT Professional

Commented:
Try using the control's Exit event.
Distinguished Expert 2017

Commented:
And what if the control never gets the focus?  That was a rhetorical question so I'll answer for you.  No control event will ever fire if the control never gets the focus.  That is why it is important to understand form and control events and how to use them.
Hamed NasrRetired IT Professional

Commented:
and the user uses TAB to enter the data
Exit event! Try and comment back.

Author

Commented:
Thank you so much for your help!
Regards Michael

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial