none
Cannot get validation rule to work RRS feed

  • 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.

    Sunday, May 19, 2019 9:54 PM

Answers

  • 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

    • Marked as answer by tkosel Friday, May 24, 2019 9:26 PM
    Monday, May 20, 2019 1:18 AM
  • 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

    • Marked as answer by tkosel Friday, May 24, 2019 9:26 PM
    Monday, May 20, 2019 11:17 AM

All replies

  • 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

    • Marked as answer by tkosel Friday, May 24, 2019 9:26 PM
    Monday, May 20, 2019 1:18 AM
  • 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

    • Marked as answer by tkosel Friday, May 24, 2019 9:26 PM
    Monday, May 20, 2019 11:17 AM
  • 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?


    • Edited by tkosel Friday, May 24, 2019 10:17 PM
    Friday, May 24, 2019 9:30 PM
  • ...........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

    Friday, May 24, 2019 11:26 PM