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)
… …
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)
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…
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
… …
