Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Kundan is offline Competent Performer
    Windows XP Access 2013 32bit
    Join Date
    Mar 2018
    Posts
    155

    Validating


    I want to ensure that the DEPT field in the subform of the attached DB is not null. How to do it?
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    One way is to set it as required in table and let Access nag users when they try to leave new record row after starting data entry. Another is to use textbox/combobox ValidationRule and ValidationText properties. Can use DefaultValue property. Anything else requires code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    There are a number of ways to approach this, using code...such as

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
     If Nz(Me.Control1,"") = "" Then
       MsgBox "Control1 Must Not Be Left Blank!"
       Cancel = True
       Control1.SetFocus
       Exit Sub
     End If
     
    If Nz(Me.Control2, "") = "" Then
       MsgBox "Control2 Must Not Be Left Blank!"
       Cancel = True
       Control2.SetFocus
       Exit Sub
     End If
    
    End Sub

    You could loop through some or all Controls and do the same thing. If the number of Controls makes the above too time consuming, given your situation, this will loop through all Textboxes and all Comboboxes and check that they're populated

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    Dim ctl As Control
    Dim CName As String
    
    
    For Each ctl In Me.Controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox
                If Nz(ctl, "") = "" Then
                  CName = ctl.Controls(0).Caption
                  MsgBox "Following field is required: " & vbCrLf & vbCrLf & CName
                  Cancel = True
                  ctl.SetFocus
                  Exit Sub
                 End If
        End Select
    Next ctl
    
    End Sub

    You could also use the Tag Property to mark certain Controls, and then loop through all Controls but only check on/address the status of these 'marked' Controls.

    To set the Tag Property for multiple Controls, all at once:
    1. Go into Form Design View
    2. Holding down <Shift> and Left clicking on each Control in turn.
    3. Go to Properties – Other and enter Marked in the Tag Property (just like that, no Quotation Marks)

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    Dim ctl As Control
    Dim CName As String
    
    
    For Each ctl In Me.Controls
      If ctl.Tag = "marked" Then
         If Nz(ctl, "") = "" Then
           CName = ctl.Controls(0).Caption
           MsgBox "Following field is required: " & vbCrLf & vbCrLf & CName
           Cancel = True
           ctl.SetFocus
           Exit Sub
         End If
       End If
    Next ctl
    
    End Sub


    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Kundan is offline Competent Performer
    Windows XP Access 2013 32bit
    Join Date
    Mar 2018
    Posts
    155
    Quote Originally Posted by June7 View Post
    One way is to set it as required in table and let Access nag users when they try to leave new record row after starting data entry. Another is to use textbox/combobox ValidationRule and ValidationText properties. Can use DefaultValue property. Anything else requires code.

    I have put in validation rule but it is not working. I have attached the DB. Please guide me.
    Attached Files Attached Files

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Actually, ValidationRule alone won't assure a field is populated. If control does not get focus then the ValidationRule will not trigger.

    Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention. Also, all caps is not recommended because it is harder to read.

    Advise not to allow empty string in text fields. I always set this field property in table to No.

    Sorry, I should have said ValidationRule alone will not assure field is populated. User must start input in the field for the rule to trigger. Just tabbing through the field will not trigger rule. So use it in combination with other settings.
    Last edited by June7; 04-08-2019 at 07:59 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    As June7 indicated...assuring that a Control is populated cannot be done with Validation done using the Control's Properties...it has to be done using the Form_BeforeUpdate event...as my code showed in Post #3.

    Anything based on the Control's events can be circumvented by simply ignoring the Control...i.e. not entering anything into it!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Kundan is offline Competent Performer
    Windows XP Access 2013 32bit
    Join Date
    Mar 2018
    Posts
    155
    Quote Originally Posted by June7 View Post
    Actually, ValidationRule alone won't assure a field is populated. If control does not get focus then the ValidationRule will not trigger.

    Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention. Also, all caps is not recommended because it is harder to read.

    Advise not to allow empty string in text fields. I always set this field property in table to No.

    Sorry, I should have said ValidationRule alone will not assure field is populated. User must start input in the field for the rule to trigger. Just tabbing through the field will not trigger rule. So use it in combination with other settings.

    Thanks for your advice. GOD BLESS YOU!!!!!

  8. #8
    Kundan is offline Competent Performer
    Windows XP Access 2013 32bit
    Join Date
    Mar 2018
    Posts
    155
    Quote Originally Posted by Missinglinq View Post
    As June7 indicated...assuring that a Control is populated cannot be done with Validation done using the Control's Properties...it has to be done using the Form_BeforeUpdate event...as my code showed in Post #3.

    Anything based on the Control's events can be circumvented by simply ignoring the Control...i.e. not entering anything into it!

    Linq ;0)>
    Thanks for your advice. GOD BLESS YOU!!!!!!

  9. #9
    Kundan is offline Competent Performer
    Windows XP Access 2013 32bit
    Join Date
    Mar 2018
    Posts
    155
    Quote Originally Posted by Missinglinq View Post
    As June7 indicated...assuring that a Control is populated cannot be done with Validation done using the Control's Properties...it has to be done using the Form_BeforeUpdate event...as my code showed in Post #3.

    Anything based on the Control's events can be circumvented by simply ignoring the Control...i.e. not entering anything into it!

    Linq ;0)>

    I am attaching DB with the code in the Before Update Event. But I am not getting the desired result. Please tell me where I am making a mistake.
    Attached Files Attached Files

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What are you getting that you don't appreciate? Seems to work.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Kundan is offline Competent Performer
    Windows XP Access 2013 32bit
    Join Date
    Mar 2018
    Posts
    155
    Quote Originally Posted by June7 View Post
    What are you getting that you don't appreciate? Seems to work.

    Sorry, for my impatience. I was expecting the message to appear immediately when I leave the field. Doesn't matter, it is working fine. Thanks for your advice. GOD BLESS YOU!!!!!!

  12. #12
    Kundan is offline Competent Performer
    Windows XP Access 2013 32bit
    Join Date
    Mar 2018
    Posts
    155
    Quote Originally Posted by Missinglinq View Post
    As June7 indicated...assuring that a Control is populated cannot be done with Validation done using the Control's Properties...it has to be done using the Form_BeforeUpdate event...as my code showed in Post #3.

    Anything based on the Control's events can be circumvented by simply ignoring the Control...i.e. not entering anything into it!

    Linq ;0)>
    Sorry, for my impatience. I was impatiently expecting the message to appear immediately when I leave the field. Doesn't matter, it is working fine. Thanks for your advice. GOD BLESS YOU!!!!!!

  13. #13
    Kundan is offline Competent Performer
    Windows XP Access 2013 32bit
    Join Date
    Mar 2018
    Posts
    155
    Quote Originally Posted by June7 View Post
    What are you getting that you don't appreciate? Seems to work.
    The original code is,
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    On Error GoTo TowardsError
    If Nz(Me.DEPT, "") = "" Then
    MsgBox "DEPT.???!!!"
    Cancel = True
    Me.DEPT.SetFocus
    Exit Sub
    End If
    Exit Sub
    TowardsError:
    ' You only get here if an error occurred.
    ' Show the error.
    21 MsgBox Err.Number
    MsgBox Err.Description


    End Sub



    I modified it and put it in a module as,
    Sub depchk
    On Error GoTo TowardsError
    If Nz(Form_Cash_Office.Child65![DEPT], "") = "" Then
    MsgBox "DEPT.???!!!"
    Cancel = True
    Form_Cash_Office.Child65![DEPT].SetFocus
    Exit Sub
    End If
    Exit Sub
    TowardsError:
    ' You only get here if an error occurred.
    ' Show the error.
    21 MsgBox Err.Number
    MsgBox Err.Description
    End Sub

    I call it as follows,
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Call depchk
    End Sub

    My problem is how to substitute the statement ( Cancel = True ) in my Sub?

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why did you put code in a separate sub?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    Kundan is offline Competent Performer
    Windows XP Access 2013 32bit
    Join Date
    Mar 2018
    Posts
    155
    Quote Originally Posted by June7 View Post
    Why did you put code in a separate sub?

    I want to keep my code safe in modules so that no one tampers it. But I want the CANCEL=TRUE thing to work in my code so that the record is not updated until DEPT is entered.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Validating Form Data using VBA
    By dweekley in forum Access
    Replies: 4
    Last Post: 05-16-2017, 09:32 AM
  2. Validating form data
    By dweekley in forum Access
    Replies: 5
    Last Post: 05-16-2017, 06:53 AM
  3. Validating the table
    By XPeriment in forum Access
    Replies: 5
    Last Post: 08-10-2016, 09:43 AM
  4. validating fields
    By bhatia.puja in forum Access
    Replies: 3
    Last Post: 07-28-2011, 05:24 AM
  5. Validating tables
    By JVagenheart in forum Database Design
    Replies: 1
    Last Post: 06-10-2006, 09:03 AM

Tags for this Thread

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