Full Version: validating Null or empty in text box
UtterAccess Forums > Microsoft® Access > Access Forms
I have a routine that adds a new row to a form and leaves the user on a certain textbox. I would like the user not to be able to leave that text box unless a value is entered. I have a validation rule on the textbox of Not Is Null, but this is not working at present. I can move off the field and not be notified that it needs to be filled in. If I type something into the textbox, then backspace over it and try to leave, I do get the desired message.
Any thoughts?
You can do this with code. With the form in design mode and the textbox control selected click on View, Properties from the menu. On the Event tab find "On Exit" and click the [...] box and select "Event Procedure".
ou'll be taken to the VBA code editor. Put in this code:
Private Sub txtPassword_Exit(Cancel As Integer)
    ' Cancel = Len(Me.txtPassword & vbNullString) = 0
    If Len(Me.txtPassword & vbNullString) = 0 Then
        MsgBox "Please enter a value.", vbOKOnly Or vbInformation, "Must Enter Value"
    End If
End Sub

This was a textbox named "txtPassword" on my form. I test for a non-Null and non-empty (null string) value by taking the current value of the textbox and appended a null string (""). The process of appending the null string will co-erce a Null value to become any empty string.
Empty (or NullString) string has a length of 0. So I test for a length using Len() and for a value of 0.
If found I display a message and set the Cancel variable equal to True which effectively cancels the "Exit" event preventing the user from moving away from the textbox control.
You'll note the commented line. This is an alternate solution if you don't care to display a helpful message. This one liner evaluates the condition on the right side of the = sign. It then assigns the result (True or False) to the Cancel variable.
Hope that helps.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.