Cannot get validation rule to work

Question

I have a access 2013 application with a form with a control for which I want to set a validation rule.  The control name is "PopUpNote".   The validation rule is "Is Not Null" and the validation text is "You must enter a value".  When the field receives the focus, then loses it, the rule doesn't cause the desired text to display.  I don't believe the rule is working.  I found the reference illustrated below that indicates that I should be able to use that validation rule.

What am I doing wrong?  I have never had mush luck with validation rules and am looking to learn.

Answer 1

Control validation rules only fire if the user modifies the text of the control; that is, if the user makes some change to the control value.  If you just tab through it, the rule won't fire.  With your form, if you type a value in the field, then delete it -- or delete the value of an   existing record -- does the rule fire?

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 2

You might like to take a look at SaveDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.

This little demo file illustrates, amongst other things, validation at both control and form level.  The former is achieved in two ways: (a) by code in the control's BeforeUpdate event procedure; and (b) by forcing the user to enter data into the controls in a specific order, which is done by putting code in each, bar the first, controls' GotFocus event procedure which examines the previous control(s) for Null.

---

Ken Sheridan, Stafford, England

Answer 3

To all,

Thanks for your help.  Yes, I did get the rules to fire, now that I know how it works, I can attempt to use it properly.  I was hoping to use it to eliminate null values, looks like I need to use other methods like making the field a required value.  I can check for a proper value using the Lost Focus event, but when I try to set the focus back to that control, it doesn't go there.

Any help?

Answer 4

...........but when I try to set the focus back to that control, it doesn't go there.

My demo uses the GotFocus event procedure of the next control, e.g.

Private Sub Lastname_GotFocus()

    If IsNull(Me.FirstName) Then
        MsgBox "First name must be entered", vbExclamation, "Invalid Operation"
        Me.FirstName.SetFocus
    End If

End Sub

As I said earlier, this does force the user to insert the data in a specific sequence.  If you don't want to do that, then the form's BeforeUpdate event procedure is the place to do the validation, e.g.

    ' ensure first and last names have been entered
    If IsNull(Me.FirstName) Then
        MsgBox "First name must be entered", vbExclamation, "Invalid Operation"
        Cancel = True
        Me.FirstName.SetFocus
        Exit Sub
    End If
   
    If IsNull(Me.Lastname) Then
        MsgBox "Last name must be entered", vbExclamation, "Invalid Operation"
        Cancel = True
        Me.Lastname.SetFocus
        Exit Sub
    End If

However, if the user tries to close the form while all the required data has not been entered, then the Cancel argument of the Unload event procedure also needs to be set to True to prevent the form closing. The form in my demo does this by allowing the record to be saved or the form closed only via command buttons.

You should certainly set the Required property of the column to True in the table design in addition to whatever code you use at form level.  This disallows Nulls if a row is inserted other than via the form, and will raise an error.

---

Ken Sheridan, Stafford, England