Loop Through a Set of Controls

Loop Through a Set of Controls

A common requirement is to check a set of controls for a particular value or condition. Here I demonstrate two methods. The technique employed is what I call the “For Each Control in Me Controls” – it’s a technique where you interrogate each control “in/on” the Form in turn. Essentially it means you perform this code/operation on each control inside “this” (Me) Form. It’s a loop, a loop that interrogates each control in turn. One way to think about it is to imagine you have a bag full of the keys popped off your keyboard! You reach into the bag and you pick a key, you look at it and it might be the “J” key. But it’s totally random, as you reach into the bag you’ll never know which key you are going to pull out. But let’s say you wanted to find the “B” key, you would just keep pulling out them out until you found the “B”… Essentially that’s what this code does, it interrogates each control in turn, then performs an operation, but only if it’s the control you were looking for. If you only wanted to operate on one control, you might be inclined, and it might well be much simpler to use another method. However if you have no idea which control is going to meet the criteria, in other words the criteria will change from one control to another depending, then this is the best way to approach it. I mean, imagine writing an “If Then Statement” to find a different a control, with a different criteria each time! It would be a nightmare! If you’ve done it, then please let me have a look at the code, I’d love to see how complicated it actually is!

Text Box Example

Text Box Example

Video 1  (7:34)

Text Box Example. In this particular example two text boxes can be empty but one must contain a value.

Please Note:- This is the same Video as below, but it starts at the beginning!

Video 1  (7:34)

Play Video

… …

Check Box Example

Check Box Example

Video 1  (7:34)

Check Box Example:- In this example the user wanted one checkbox to be checked all the time.

Please Note:- This is the same Video as above, but started at Time Index 2:54

Video 1  (7:34)

Play Video

Nice Comment From:-

Grenee said:- the video on this link provided me with exactly what I needed…

anb001 Said:- Uncle Gizmo, that was spot on. Thanks a lot.

… …

Counting Empty Textboxes

Count of Empty Text Boxes

Video 2 (2:35)

A recent question on Access World Forums provided me the opportunity to demonstrate a new variation on one of my favourite coding methods with MS Access VBA:- “For Each Control in Me.Controls”.

This technique allows you to look at each control on your Access Form in turn, make a decision about the control, and/or change the controls properties. You now have access to a World of possibilities!

The Question:- “Counting Empty Text Boxes”(ATH corrected) I answered the question by providing a demo database and a YouTube video explaining what’s happening. I have provided the code below for your perusal…

Notice that not only does this method facilitate counting the empty controls, but it also allows you to modify a Control by changing its Properties.

In this example, I change the border colour to Red “vbRed” and the background colour to yellow “vbYellow” this highlights the Controls on the Form that are NULL.

See the YouTube video “Counting Empty Textboxes” (Video 2) explaining how the code works…

Video 2 (2:35)

Play Video

Counting Empty Controls Thread on AWF

Private Function fSomeMT()

Read that as “private function” Some text boxes are empty! The Code BELOW examines all of the text boxes on a form, finds the text boxes with a particular prefix, checks to see if they contain a NULL value, in other words the user has not entered any data. If so the Code highlights the text box with a red Border and a yellow background. This is done in Microsoft Access in the Forms Code Module with VBA code.



Private Function fSomeMT() As Boolean
'fSomeMT = Some Empty

Dim conAppName As String
conAppName = "(Replace this Local Variable with a Global One) "

Dim strSubName As String
Dim strModuleName As String

strSubName = "fSomeMT"
strModuleName = "Form - " & Me.Name

On Error GoTo Error_Handler

Dim Ctrl As Control
Dim intEmptyCtrls As Integer

    For Each Ctrl In Me.Controls
        Select Case Ctrl.ControlType
        
            Case acTextBox
                If Left(Ctrl.Name, 6) = "tbGame" Then
                    Ctrl.BorderColor = vbBlack
                    Ctrl.BackColor = vbWhite
                    
                        If IsNull(Ctrl) Then
                            intEmptyCtrls = intEmptyCtrls + 1
                            Ctrl.BorderColor = vbRed
                            Ctrl.BackColor = vbYellow
                        End If
                End If
        End Select
        
    Next Ctrl

If intEmptyCtrls > 0 Then
    fSomeMT = True
End If


Exit_ErrorHandler:
    
    Exit Function

Error_Handler:  'Version - 1a
    Dim strErrFrom As String
    Dim strErrInfo As String
        
        strErrFrom = "Error From:-" & vbCrLf & strModuleName & vbCrLf & "Subroutine >>>>>>> " & strSubName
        strErrInfo = "" & vbCrLf & "Error Number >>>>> " & Err.Number & vbCrLf & "Error Descscription:-" & vbCrLf & Err.Description
            
            Select Case Err.Number
                Case 0.123 'When Required, Replace Place Holder (0.123) with an Error Number
                    MsgBox "Error produced by Place Holder please check your code!" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
                Case Else
                    MsgBox "Case Else Error" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
            End Select
        Resume Exit_ErrorHandler

End Function      'fSomeMT

… …