Results 1 to 14 of 14
  1. #1
    jrdnoland is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2010
    Location
    Ohio
    Posts
    16

    Pause code to retrieve answer

    I'm making an Access 2016 module that checks for the presence of a value. If the value is there it will continue and check another value. But, if the value is missing I want to pause the module and allow user to select a value. After that the whole procedure can continue again and run untill all of the necessary values are there.

    Code:
    'If a checkbox is selected then all of the elements under it need to be checked for blanks
    If (Forms!frmTasks![chkAnalytical] = True) Then
        'check trelloboard who's assigning
        If (Forms!frmTasks![cboANA_TrelloBy] <> "") Then
        Else
            MsgBox "Please select a User to get the Analytical Trello Board information from.", vbOKOnly, "Missing Information"
            Exit Sub
        End If
        'check the trello board to email to
        If (Forms!frmTasks![txtANA_TrelloBy] <> "") Then
        Else
            MsgBox "Please make sure a Trello Board has been selected", vbOKOnly, "Missing Information"
            Exit Sub
        End If
        If (Forms!frmTasks![lstANARes].ListCount = 0) Then
        Else
            MsgBox "Please make sure there is at least ONE Resource selected for this project.", vbOKOnly, "Missing Information"
            Exit Sub
        End If
    End If
    There are multiple things being checked for:


    1. Is the main checkbox selected?
    2. Has a user name been selected from the combobox?
    3. Is a board given in the text box (obtained from the association with the user from the previous step)?
    4. Is there at lease one resource in the listbox?

    ...will then continue with other code.

    I'd like to be able to jump out of the switch, select a value, and then start the checks again.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,419
    When the code hits a stop point (f9)
    hover the mouse over a variable to see its value.

    or use the immediate window (ctl-G) and use '?' To print the result:
    ?iLimit
    ?txtBox1

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,769
    @ranman256, those are debug techniques, not what I think OP is asking for.

    What do you mean by "switch" - the If Then block?

    Usual approach is to set focus to control that needs input and exit procedure and after input the procedure runs again to perform same checks.

    Otherwise need to either present user with a popup dialog form or an InputBox.

    What event is this code in?
    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.

  4. #4
    jrdnoland is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2010
    Location
    Ohio
    Posts
    16
    Quote Originally Posted by June7 View Post
    @ranman256, those are debug techniques, not what I think OP is asking for.

    What do you mean by "switch" - the If Then block?

    Usual approach is to set focus to control that needs input and exit procedure and after input the procedure runs again to perform same checks.

    Otherwise need to either present user with a popup dialog form or an InputBox.

    What event is this code in?


    Yes, I was referring to an If statement.

    The code is in a vba module, not any particular code.

    You confirmed my thoughts about building a custom input box form that gets it's values from a function.
    That way I can use it for any part of the code buy feeding the information to it.

    Thanks,
    Jeff

  5. #5
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    591
    One drawback to the approach of doing validation control by control is when there are several missing items and the user then needs to go through the process several times. It can be tedious to have to acknowledge the message box each time, make the entry, then click another commandbutton, rinse and repeat. I find it much easier and user friendly to do it all at once by including everything missing in one messagebox and use a visual aid to point to the missing items.
    This procedure uses the tag property of the controls to test and gets a list of missing items, highlights the controls border in red and uses one message box to show the list of missing items. The user is only interupted once and can see they need to fill in the red highlighted items.

    Code:
    Public Function ValidateForm(frm As Form, TagCharacter As String) As Boolean
    'validated controls must have a label. Ok to use a hidden label if needed.
    
    
        Dim ctl As Control
        Dim flg As Boolean
        Dim strOut As String
    
    
        flg = True
    
    
        For Each ctl In frm.Controls
    
    
            If ctl.Tag = TagCharacter Then
                If Nz(ctl.Value, "") = "" Then
                    flg = False
                    ctl.BorderColor = vbRed
                    strOut = strOut & Space(10) & "* " & ctl.Controls.Item(0).Caption & vbNewLine
                Else
                    ctl.BorderColor = vbBlack
                End If
    
    
            End If
        Next
    
    
        If flg = False Then
            MsgBox "The following field(s) must be completed:" & vbNewLine & strOut
        End If
    
    
        ValidateForm = flg
    
    
    End Function
    Click image for larger version. 

Name:	valid.png 
Views:	29 
Size:	11.2 KB 
ID:	42986

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,357
    @Moke123, that's the approach I've often used but an FYI if you care: you don't actually need the boolean. If you append the captions of the labels to your string, then at test time you can simply use IF strString = "". If no required controls were empty, the variable = "" otherwise it contains a list. In the latter case, I then concatenate the list to a message
    msgbox "Please enter values for:" & vbCrLf & strString

    Might also be worth noting that this approach requires that the labels are bound/associated to their controls or it will error. Also "If ctl = " is good enough if you're lazy like me, as .Value is the default property.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  7. #7
    jrdnoland is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2010
    Location
    Ohio
    Posts
    16
    Quote Originally Posted by moke123 View Post
    One drawback to the approach of doing validation control by control is when there are several missing items and the user then needs to go through the process several times. It can be tedious to have to acknowledge the message box each time, make the entry, then click another commandbutton, rinse and repeat. I find it much easier and user friendly to do it all at once by including everything missing in one messagebox and use a visual aid to point to the missing items.
    This procedure uses the tag property of the controls to test and gets a list of missing items, highlights the controls border in red and uses one message box to show the list of missing items. The user is only interupted once and can see they need to fill in the red highlighted items.

    Code:
    Public Function ValidateForm(frm As Form, TagCharacter As String) As Boolean
    'validated controls must have a label. Ok to use a hidden label if needed.
    
    
        Dim ctl As Control
        Dim flg As Boolean
        Dim strOut As String
    
    
        flg = True
    
    
        For Each ctl In frm.Controls
    
    
            If ctl.Tag = TagCharacter Then
                If Nz(ctl.Value, "") = "" Then
                    flg = False
                    ctl.BorderColor = vbRed
                    strOut = strOut & Space(10) & "* " & ctl.Controls.Item(0).Caption & vbNewLine
                Else
                    ctl.BorderColor = vbBlack
                End If
    
    
            End If
        Next
    
    
        If flg = False Then
            MsgBox "The following field(s) must be completed:" & vbNewLine & strOut
        End If
    
    
        ValidateForm = flg
    
    
    End Function
    Click image for larger version. 

Name:	valid.png 
Views:	29 
Size:	11.2 KB 
ID:	42986



    Thanks for the input. I may give that a try if I can figure out how to use it for multiple controls.

    What I mean is that I was going to validate 16 controls. Do I give all 16 that same tag character?
    How would I call it during execution?

    e.g.
    Code:
    Public Function ....
    
    Dim flg as Boolean
    
    flg = ValidateForm(myFormName, "control tag character")
    
    ...checks
    
    
    If flg = False then
      ...
    
    Validateform = flg
    
    End Function
    Last edited by jrdnoland; 09-13-2020 at 12:56 PM. Reason: Don't understand multiple controls and tagging then all

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,357
    Do I give all 16 that same tag character?
    Yes, but you don't have to assign the value one by one. Select all 16 in design view and enter the Tag property value you want to check. Only the controls on the form that contain the property value that you test for will be part of those that will be examined for having a value in the form control - or not. Usually the best event for verifying form values is in the form BeforeUpdate event. Do not use AfterUpdate, and if the test does not pass, don't forget to Cancel the update.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  9. #9
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    591
    Quote Originally Posted by Micron View Post
    @Moke123, that's the approach I've often used but an FYI if you care: you don't actually need the boolean. If you append the captions of the labels to your string, then at test time you can simply use IF strString = "". If no required controls were empty, the variable = "" otherwise it contains a list. In the latter case, I then concatenate the list to a message
    msgbox "Please enter values for:" & vbCrLf & strString

    Might also be worth noting that this approach requires that the labels are bound/associated to their controls or it will error. Also "If ctl = " is good enough if you're lazy like me, as .Value is the default property.
    The version I normally use tests for a label and if there is not one uses the controlsource for the text. I use the boolean because I sometimes reverse them so i can use - Cancel = ValidateForm(me,"?")

  10. #10
    jrdnoland is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2010
    Location
    Ohio
    Posts
    16
    I recreated my controls so they would have a label and gave the objects I wanted to validate a tag.

    Code:
    Public Function ValidateForm(frm As Form, TagCharacter As String) As Boolean
    'validated controls must have a label. Ok to use a hidden label if needed.
        Dim ctl As Control
        Dim flg As Boolean
        Dim strOut As String
        
        flg = True
        
        Forms!frmTasks.SetFocus
        DoCmd.Maximize
        
        For Each ctl In frm.Controls
        MsgBox ctl.Tag & "  ...  " & ctl.name
            If ctl.Tag = TagCharacter Then
                If Nz(ctl.Value, "") = "" Then
                    flg = False
                    ctl.BorderColor = vbRed
                    strOut = strOut & Space(10) & "* " & ctl.Controls.Item(0).Caption & vbNewLine
                Else
                    ctl.BorderColor = vbBlack
                End If
            End If
        Next
        
        If flg = False Then
            MsgBox "The following field(s) must be completed:" & vbNewLine & strOut
        End If
        
        ValidateForm = flg
    End Function
    It worked for the combobox and the textbox. I could not get it to work for the listbox, is there something I'm missing

    ?Click image for larger version. 

Name:	Access Controls.jpg 
Views:	20 
Size:	35.5 KB 
ID:	42995
    Attached Thumbnails Attached Thumbnails access controls.jpg   Access Controls 2.jpg  

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,357
    You don't type values in to a listbox, so I don't get why you'd want to check it for an entered value. If the check is to ensure that something has been selected, then the test would be something like
    If Me.ListboxName.SelectedItems.Count = 0 Then (if 0, nothing was selected).

    Be that as it may, I don't think you're on the right track with that function. It's written as if it should be able to accept any form and any tag value you want to pass to it. So it makes no sense to me to set focus to any specific form. If you plan to use this as a public function for any form, then what guarantee do you have that frmTask will be open? I don't see the need to refer to it at all. I'll let Moke123 weigh in on the rest of it seeing as how it resembles his suggestion because I still am not seeing the need for the flag when you can just set the function return value to T or F.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  12. #12
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    591
    The version of the function will work only on textboxes and comboboxes. If you want to include other control types you need to modify it. I cant find my other versions at the moment.

    To modify it you can add a select case to it.


    Code:
    For Each ctl In frm.Controls
    
            If ctl.Tag = TagCharacter Then
    
    Select case ctl.ControlType
    
    Case acTextbox, acCombobox
    'validation criteria
    
    Case acListbox
    'validation criteria
    
    ...
    Edit: Looking at your screenshots and re-reading your original post I agree with with Micron that this may not be the way to go. It appears you need to test for a checkbox value before validating whats under it. Its not clear what the relationship is. My function is generic where it appears you need a more customized approach. We would probably need to see your Db to provide better solutions.

  13. #13
    jrdnoland is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2010
    Location
    Ohio
    Posts
    16
    Quote Originally Posted by moke123 View Post
    The version of the function will work only on textboxes and comboboxes. If you want to include other control types you need to modify it. I cant find my other versions at the moment.

    To modify it you can add a select case to it.


    Code:
    For Each ctl In frm.Controls
    
            If ctl.Tag = TagCharacter Then
    
    Select case ctl.ControlType
    
    Case acTextbox, acCombobox
    'validation criteria
    
    Case acListbox
    'validation criteria
    
    ...
    Edit: Looking at your screenshots and re-reading your original post I agree with with Micron that this may not be the way to go. It appears you need to test for a checkbox value before validating whats under it. Its not clear what the relationship is. My function is generic where it appears you need a more customized approach. We would probably need to see your Db to provide better solutions.
    I didn't realize the function only worked for comobboxes and textboxes. I'm trying to make sure that the listboxes contain at least one person
    before the fuction is completed. When the Assign button is clicked the resources are used if the check box indicates that they should be used. Checking the boxes is a user dependent action that takes place on the previous screen. Once everything is validated the resources are updated in the database and if selected the resources along with the test requestor is sent an email. Each Trello board is also updated with its own resources.

    This is an update to the way this database works, it's been around quite a while and I'm updating it and linking the tables to sql server.

  14. #14
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    591
    Just to follow up on my post #12 heres what using a select case to include a listbox could look like. You could combine the textbox and combobox case if you wanted. It also tests for a label and uses the controlsource if there is no label.
    Code:
    Public Function InValidForm(frm As Form, TagChar As String) As Boolean
    
    
        Dim ctl As Control
        Dim strMsg As String
    
    
        For Each ctl In frm.Controls
    
    
            If InStr(1, ctl.Tag, TagChar) Then
    
    
                Select Case ctl.ControlType
    
    
                Case 109    'acTextBox
    
    
                    If Nz(ctl, "") = "" Then
                        strMsg = strMsg & IIf(ctl.Controls.Count <> 0, ctl.Controls.Item(0).Caption, ctl.ControlSource) & vbNewLine
                        ctl.BorderColor = vbRed
                        InValidForm = True
                    Else
                        ctl.BorderColor = vbBlack
                    End If
    
    
                Case 110    'acListBox
    
    
                    If ctl.ItemsSelected.Count = 0 Then
                        strMsg = strMsg & IIf(ctl.Controls.Count <> 0, ctl.Controls.Item(0).Caption, ctl.ControlSource) & vbNewLine
                        ctl.BorderColor = vbRed
                        InValidForm = True
                    Else
                        ctl.BorderColor = vbBlack
                    End If
    
    
                Case 111    'acComboBox
    
    
                    If Nz(ctl, "") = "" Then
                        strMsg = strMsg & IIf(ctl.Controls.Count <> 0, ctl.Controls.Item(0).Caption, ctl.ControlSource) & vbNewLine
                        ctl.BorderColor = vbRed
                        InValidForm = True
                    Else
                        ctl.BorderColor = vbBlack
                    End If
    
    
                Case Else
    
    
                    Debug.Print ctl.ControlType
    
    
                End Select
    
    
            End If
    
    
        Next
    
    
        If InValidForm Then MsgBox strMsg
    
    
    End Function

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

Similar Threads

  1. VBA code to retrieve duration of sound files
    By GraeagleBill in forum Programming
    Replies: 13
    Last Post: 08-28-2016, 08:08 PM
  2. Replies: 20
    Last Post: 02-24-2015, 10:43 AM
  3. Making code wait/pause until event
    By faythe1215 in forum Programming
    Replies: 3
    Last Post: 02-09-2015, 03:44 PM
  4. Pause code until return from call command
    By trevor40 in forum Programming
    Replies: 1
    Last Post: 03-08-2014, 05:08 AM
  5. Replies: 2
    Last Post: 10-05-2012, 07:52 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 - Senior Forums