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. 👀

🃏 The Card Deck Analogy 🎴

Consider this analogy: it’s like going through a deck of shuffled playing cards one by one. You don’t know the order of the cards, so every time you draw a card, it’s a surprise. It could be a 2 of Hearts, or it could be the Queen of Diamonds. But suppose you’re specifically looking for the Ace of Spades. You would need to keep drawing cards from the deck until you find the Ace of Spades. 🎲

Similarly, the code works in this way: it goes through each control in the form one at a time. It performs an operation only on the control that matches your criteria, just like you’d be looking for the Ace of Spades in the deck of cards. 💻

🔍 Selecting the Right Approach 📝

If your task only involves a single, specific control, you might prefer a more straightforward approach. However, if you’re unsure which control will satisfy your varying criteria, this method of cycling through each control becomes invaluable. 🚴

To put this into perspective, consider the daunting task of writing an “If Then Statement” tailored to identify a different control each time, with each control having unique criteria. It would quickly become a complex maze of code. 🧩 If you have already tackled such a task, I’d be very interested in examining your solution and seeing just how intricate it is! 🕵️‍♀️

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)

… …

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)

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

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.

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)

Counting Empty Controls Thread on AWF

				
					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

… …