Results 1 to 11 of 11
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    Validation rule

    All, using access 2016..in a table I have a yes/no field. I want a validation rule that if the field selection is no, the next field cannot be null. I tried something like this.

    Code:
    =if([Required?] = "No", "Field must not be null", ,
    How can I set this rule?

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Vlidation is normally best done in the forms BeforeUpdate event
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Thanks for responding. Do I put it in the field I want to check? And can u assist with the validation rule?

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    You would need the code in the Forms BeforeUpdate event.

    Something like:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)    
    If Me.Required = False And IsNull(Me.NameOfYourTextControl) Then
            Cancel = True
            MsgBox "Field must not be null"
            Me.NameOfYourTextControl.SetFocus
        End If
    End Sub
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    the code could look like
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
        If Me.[Required?] = True And IsNull(Me.[Name ofNextField]) Then
            Cancel = True
            MsgBox "Field xxxx cannot be empty when required = true"
        End If
    End Sub
    

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    you can also validate a single form control if that is more helpful or user friendly. You'd use the Before Update event for the control instead of the form. Sometimes it's more convenient to flag this right away instead of waiting until the entire form as been filled out.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Ok..I tried the code...first I received an error when I put the last line: Me.notification.setfocus so I left it out after the msgbox. It still doesn't work. I think it has something to do with putting the code on the "Before update"..Is the code supposed to be on the Form's BeforeUpdate or the "Required?" field's Before Update?
    The first field "Required?" is a yes/no field. The next field after that is "Explanation" So, if the "Required?" field is = No; I want the user to have to fill in the Explanation field.

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
        If Me.[Required?] = True And IsNull(Me.[Explaination]) Then
            Cancel = True
            MsgBox "Explaination cannot be empty when required = true"
        End If
    End Sub
    Is this correct?  Because I put the code on the "Required?" on Before Update. I think I had it wrong.  I'll check when I get back to work tomorrow.  
    

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    User can bypass form control's BeforeUpdate routine easily - simply not editing the control! So this event may be used only to prevent unwanted entries for this field;
    BeforeUpdate event of form will be activated before active record is saved, and this happens when at least one control was updated on form. So this is the event to use for you!

    In Design view, activate the form (form, not some control in form!), in Property Sheet activate tab Event, activate Before Update in events list, and from dropdown select Event Procedure. Text 'Event Procedure' is displayed for this event! (In case the event existed before, this text was present before and you can skip the event creating part!)
    When you click on 'Event procedure' text, the VBA editor opens with at least start and closing rows of event code displayed. This is the place where you edit the event.

    I think the code must be like
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)    
         If (Me.[Required?] = True OR IsNul(Me.[Required?])) And IsNull(Me.[Explaination]) Then        
              Cancel = True        
              MsgBox "Explaination cannot be empty when required = true"    
         End If
    End Sub
    
    You also can make the code at first to check Required control (saving is not allowed wen Me.Required is empty), and then to check Explanation control.

  9. #9
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Thanks, I put the code on the beforeupdate on the form But, then I realized I'm using this form as a subform on another form and the code fires before the user completes the rest of the fields on the form...do I move the code to the main firm if so, how do I reference the subform in the code?

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    the code fires before the user completes the rest of the fields on the form.
    Which form? That makes sense if you mean they change or enter a value in the subform but don't complete it before activating the main form. Lots of approaches you can take but moving subform update code to main isn't one of them. Key thing to realize is that form update event will fire for a few reasons, one of them being that you leave the edited form. Thus you'd end up firing that code when leaving what might be a completed main form even though use hasn't attempted to enter anything in the sub. Not sensible and likely annoying.

    You can live with the behaviour (users will quickly learn that the main form data should be completed first) or not show the subform until main is complete, or some other suitable approach. Or perhaps suppress the message if key field(s) in main form have no data, or separate the forms, or unbind everything and check all necessary fields before appending/updating, or...
    Last edited by Micron; 07-23-2019 at 12:52 PM. Reason: clarification

  11. #11
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    I got it solved...Thanks for everyone's help

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Validation Rule
    By Eranka in forum Access
    Replies: 7
    Last Post: 05-27-2018, 10:24 AM
  2. Validation Rule
    By MdHaziq in forum Queries
    Replies: 3
    Last Post: 12-26-2017, 08:10 PM
  3. Replies: 4
    Last Post: 12-05-2017, 03:31 PM
  4. Replies: 2
    Last Post: 06-16-2015, 03:52 AM
  5. IIf in Validation Rule
    By Bugsy in forum Access
    Replies: 6
    Last Post: 11-21-2011, 11:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums