Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    sprtrmp is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    Greenville, SC
    Posts
    102

    Add controls to some kind of "group/collection/whatever" to check for null/empty??

    I have a form with 10 textboxes on it that the user would use to input information. the information input into 8 of the 10 textboxes is required info, 2 of them are for optional information.
    when the user clicks the "save" button, I want to check the 8 required textboxes to make sure they are not null/empty without having to write nested "Ifs" for all of the textboxes.
    i have the vague understanding that if i wanted to check all of the controls on the form, i could write some code to check ALL of the controls in the controls list, but obviously, i don't want to check ALL of them.

    is it possible to add the 8 controls that i do want to check to some kind of "group" or "collection" or whatever it might be called and then check all of the controls in that group?

    any help would be great, but please be gentle, i am a relative noob with not much experience in THIS sort of thing.

    Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    One method is looping all controls, and use the Tag property to identify the ones you want to test.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I don't know of any way of "grouping" controls. For only 8 controls, I don't think I would worry about fancy code, and you don't need nested if's. All you need is 4 lines per control:

    Code:
    If isnull(Textbox1) then
      Msgbox "Textbox1 is required"
      exit sub
    endif
    You would put these checks at the top of your "Save" button, so that if any one of them failed, the code would exit without saving.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I respectfully disagree with John_G. At the very least, the solution PBaldy alludes to is one that I've used many times if for no other reason that the goal should be to avoid redundant code writing. What may be of more importance in this situation is what can be learned by doing things differently which may or may not have added benefits of being more flexible and less code to scan through. Here, the user has the chance to learn something about the tag property, enumerating object collections, control types/type names and whatever else comes up that is currently unknown to the user. In this situation, the flexibility part lies in simply assigning the tag property to one or several controls that get added if the form requires it, without any additional code writing. I would not do this for maybe 2 or 3 controls, but if I thought there was a possibility that changing business needs could take it to more than 4, I would.
    EDIT: one could even write one function for this and pass the entire form to it, thereby making it usable by practically any form.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    Not tested, pulled from a couple different places and built upon. Tell me if it sinks or swims.


    Code:
    Dim ctlVar as Control
    Dim SaveFile as Boolean
    SaveFile = True
    
    For Each ctlVar In Me.Controls
        If ctlVar.ControlType = acTextBox Then
               If ctlvar isnull then
                            if ctlVar.name <> "Textbox to ignore" and ctlVar.name <> "Other TextBox to ignore" then
                        msgbox ctlvar.name & " Is empty!"
                        SaveFile = False
                   end if 
               End if
    Next ctlVar
    
    if SaveFile = True then 
         blah
    else
         blah blah
    end if
    
    
    Last edited by redbull; 05-16-2016 at 11:52 AM. Reason: A word

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The problem with that as I interpret it, you aren't checking for empty strings "" and it can only test for two controls by name. I suggest this:
    Put "Reqd" (without quotes) in the tag property (property sheet) for each textbox you want to enforce

    Code:
    Private Sub WhateverClickEvent() 'use your own sub name; e.g. button click event
    Dim ctl as Control
    Dim svList as string
    
    For each ctl in Me.controls
      If ctl.Type = acTextbox AND ctl.Tag = "Reqd" then
        If IsNullEmpty(ctl) then svList = svList & ctl.Controls(0).Caption & vbcrlf 'if Reqd and = "" (or is null) then add the label caption to a list)
      End If
    Next
    
    If svList <> "" then Msgbox "You must supply a value for " & vbcrlf & svList 'if list is not empty, message
    End Sub
    Then put this function in a standard module (gets called by line If IsnullEmpty):

    Code:
    Public IsNullEmpty(ctl) as Boolean
    IsNullEmpty = False
    If ctl = "" or IsNull(ctl) then IsNullEmpty = True
    End Function
    NOTES: ctl.Controls(0).Caption refers to the label ATTACHED to the textbox. If your labels are not attached, you can only refer to the controls by their name:
    ctl.Name which might not be real descriptive or useful to the user.
    This particular iteration of code is untested / air code.
    Last edited by Micron; 05-16-2016 at 01:26 PM. Reason: had next and end if reversed
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    sprtrmp is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    Greenville, SC
    Posts
    102
    I am starting to see what is going on here, and it is along the lines of what I was looking for. I just haven't had the time to mess with it yet today. I will dig a little deeper into it this evening, and see what my questions wind up being. I am sure there will be questions.
    Thank you for the help so far!

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Probably does not matter, but I just noticed this comment could be confusing
    'if Reqd and = "" (or is null) then add the label caption to a list)

    should be
    'if Reqd = "" (or is null) then add the label caption to a list)
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    sprtrmp is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    Greenville, SC
    Posts
    102
    I spent some time getting familiar with the logic behind what is going on here, I have to admit, some of it is brand new, but it definitely makes some degree of sense to me..

    I plugged all of the above in, including the standard module.
    when I click the "Go" button, I get an "invalid outside procedure" error that refers to the following line in the module:
    IsNullEmpty = False (with "False" highlighted)

    the error appears even when none of the "Reqd" controls are null/empty

    Any Ideas??

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Looks like a keyword is missing. The function in the standard module should be
    Code:
    Public Function IsNullEmpty(ctl) as Boolean
        IsNullEmpty = False
        If ctl = "" or IsNull(ctl) Then 
             IsNullEmpty = True
        End If
    End Function

  11. #11
    sprtrmp is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    Greenville, SC
    Posts
    102
    one little word makes all the differenc in the world

    now I am getting

    "Error 438"Object Doesn't Support This Property or Method
    "

  12. #12
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Here is both routines. The RED is what I had to change:
    Code:
    Private Sub WhateverClickEvent()    'use your own sub name; e.g. button click event
        Dim ctl As Control
        Dim svList As String
    
        For Each ctl In Me.Controls
            '        Debug.Print ctl.Name
            If ctl.ControlType = acTextBox And ctl.Tag = "Reqd" Then
                If IsNullEmpty(ctl) Then
                    svList = svList & ctl.Controls(0).Caption & vbCrLf    'if Reqd and = "" (or is null) then add the label caption to a list)
                End If
            End If
        Next
    
        If svList <> "" Then
            MsgBox "You must supply a value for " & vbCrLf & svList    'if list is not empty, message
        End If
    End Sub
    Code:
    Public Function IsNullEmpty(ctl) As Boolean
        IsNullEmpty = False
        If ctl = "" Or IsNull(ctl) Then
            IsNullEmpty = True
        End If
    End Function

  13. #13
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Thanks for pitching in, ssanfu! Air code from an air-head?

  14. #14
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @ Micron
    No problems. Not bad for air code..

  15. #15
    sprtrmp is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    Greenville, SC
    Posts
    102
    That seems to have fixed it!!
    Looks like it is working pretty well.

    Thanks again for all of the help!!

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

Similar Threads

  1. Replies: 2
    Last Post: 12-23-2015, 09:32 PM
  2. Replies: 4
    Last Post: 12-20-2015, 12:16 PM
  3. Replies: 4
    Last Post: 01-22-2015, 10:30 AM
  4. Passing criteria "NULL" or "IS NOT NULL" to a query
    By SgtSaunders69 in forum Forms
    Replies: 1
    Last Post: 12-24-2011, 02:22 AM
  5. "Group By" causes "ODBC--Call Failed" error
    By kaledev in forum Queries
    Replies: 1
    Last Post: 03-09-2011, 02:43 PM

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