Results 1 to 9 of 9
  1. #1
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119

    Vba to Check for required fields

    Hi everyone,
    I have the following vba code on the "before update event" on a form to cancel data entry or update if a field with the tag "required" is left blank.

    Dim ctl as control
    For each ctl in me.controls
    Select case ctl.control type
    Case actextbox, accombobox
    If ctl.value = null and ctl.tag = "required" then
    MsgBox "error message"
    Cancel = true


    End if
    End select
    Next ctl

    When I test the code by inserting/updating a record and leaving out those fields with the tag "required", the record is saved. Meaning the code doesn't work or I missed something. Can someone help?
    Last edited by ezybusy; 06-23-2017 at 06:37 PM.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    After the Next ctl line:
    If Cancel = True Then Me.Undo
    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
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think I would change a couple of things; there's only one case and no Case Else plus I'd tend to use the IsNull function and test for empty strings.
    Code:
    Dim ctl as control
    For each ctl in me.controls
    If ctl.ControlType = acTextbox OR ctl.ControlType = acCombobox Then
      If (IsNull(ctl) OR ctl = "") AND ctl.Tag = "required" Then 
        MsgBox "error message"
        Cancel = True
        Me.Undo
      End If
    End if
    Next ctl
    This all assumes there is nothing else that is causing a record save, such as moving off the record.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    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
    In problem in your original code is with two lines:

    Select case ctl.control type

    needs to be

    Select Case ctl.ControlType

    without the space between Control and Type, and

    If ctl.value = null

    which has no meaning in VBA, in this context. To check, in VBA, for Nulls you need, as Micron said, to use the IsNull() function, and just to be safe, as he also suggested, you need to check for Zero-Length Strings ("") which can be done in several ways, including his of

    ctl = ""

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

    All posts/responses based on Access 2003/2007

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The space might have been thrown in there by the forum. It's happened to me before. Random space in the post and no amount of editing will make it go away.

    If the space really is in the code then a Debug>Compile should have caught.

    But oops, should have caught the = Null.
    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
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Random space in the post and no amount of editing will make it go away.
    I suspect you are referring to what I believe is the forum enforcing a space after 50 characters, in which case, code tags are the remedy. If you're meaning that it truly is random, then I haven't observed that behaviour.

  7. #7
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    Thank you.
    Works fine.

  8. #8
    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
    Quote Originally Posted by June7 View Post

    ...The space might have been thrown in there by the forum. It's happened to me before. Random space in the post and no amount of editing will make it go away...
    Well, that's kind of embarrassing, isn't it? Given that we're a software development site, as opposed, say, to one about baking...or cars...or New Age music!

    Of course, I belonged to an Access site, once, where you couldn't post code, code tags or not, that included a dot, like DoCmd.GoToRecord, with characters immediately on either side of the dot! If you did, the site assumed that it's a email address and for security reasons places asterisks in place of the [dot] and the next three characters!

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

    All posts/responses based on Access 2003/2007

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    not sure to what or whom you're referring to.
    As an afterthought, I use a custom function in a standard module to test for Null or empty strings in controls because I can call it from anywhere in the project. The call would be like
    Code:
    If IsNullEmpty(ctl) And ctl.Tag = "required" Then...
    or
    Code:
    If ctl.Tag = "required" Then
      If IsNullEmpty(ctl) Then
    the difference being that the function is not being called if the tag is not set.
    The function is
    Code:
    Public Function IsNullEmpty(ctl As Control) As Boolean
    IsNullEmpty = False
    If IsNull(ctl) Or ctl = "" Then IsNullEmpty = True
    End Function
    Last edited by Micron; 06-23-2017 at 07:51 PM. Reason: added code tags
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 5
    Last Post: 04-19-2015, 12:42 AM
  2. Required entry if check box is blank
    By justair07 in forum Access
    Replies: 5
    Last Post: 08-22-2013, 12:31 PM
  3. Check for required when leave the field
    By Rhubie in forum Forms
    Replies: 13
    Last Post: 09-12-2012, 02:17 PM
  4. Conditionally Required Fields
    By HawkGuru in forum Programming
    Replies: 6
    Last Post: 10-03-2011, 05:47 AM
  5. Required Fields
    By scubagal in forum Forms
    Replies: 9
    Last Post: 09-29-2011, 07:26 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