For Each...Next and With...End With

The For Each...Next and With...End With statements can work together nicely for form data validation and classic enumeration tasks. For Each...Next iterates through any collection (such as the controls on a form) or array. It does not require that your application know the number of elements in the collection or array. The With...End With statement can complement For Each...Next by simplifying how you code multiple controls in a statement block.

The For Each...Next statement is both similar to and slightly less complicated than the For...Next statement. It is similar to For...Next in that its loop starts with a line that begins with For and ends with a line that begins with Next. However, the design of the For and Next lines are different for both versions of the For loop. The For Each...Next statement is less complicated than the For...Next statement because you do not have to track three separate parameters (counter, start, and stop) or worry about a positive or negative step value. The For Each...Next statement always starts at the beginning and loops forward until it reaches the end of a collection or the elements in an array. The general syntax for the statement follows:

For Each <element>
 In <group>
 
 'Statements
 If <condition>
 Then
 Exit For
 End If
 'Statements
Next <element>
 

The <group> term in the first line of the For Each...Next statement refers to the collection or the array name. The <element> in both the first and last lines designates individual objects in a collection or elements in an array.

The For Each...Next statement repetitively executes the statements in its body for each element in the specified collection or array. You will often want to have an Exit For or other conditionally executed statement somewhere in the body of the For loop. This enables your code to respond dynamically to a special outcome in its environment. The condition test identifies this special outcome, and the Exit For or other conditionally executed statements engage only when the outcome occurs.

As with the For...Next statement, For Each...Next statements can nest inside one another. At the conclusion of a For Each...Next loop, control passes to the first statement following the loop.

The With...End With statement simplifies the referencing of several different properties or methods of the same object. You specify the object whose properties or methods you want to reference in the beginning With line, and you close the reference to that object with the End With line at the end of the block. Between the With and the End With lines, you can access the object's properties or methods without specifying the object name. The code shows the general syntax of the With...End With statement.

With <object
>
 .<propertyname1
> = "new value 1"
 .<propertyname2
> = "new value 2"
 .<method1
>
 .<method2
>
End With

The term <object> is the name of an object, a reference to an object, or an array name. The terms <propertyname1> and <propertyname2> are properties of the object, and <method1> and <method2> are methods of the object. As you can see, the With...End With statement make references to an object's properties and methods more concise.

Figures 1-19, 1-20, and 1-21 show a data validation form in action. Although the form has only a pair of text boxes that require validation, the form's code uses a For Each...Next loop that can be expanded to accommodate more text box controls. You can make a slight change to include other types of controls in the validation procedure. Figure 1-19 shows the layout of the basic Input form with a pair of text boxes and a command button.

Clicking the Do It! command button invokes an event procedure that checks the text boxes to make sure they do not contain Nulls. If either control contains a Null, the event procedure displays a message box reminding the user to enter information in both text boxes.

The event procedure also calls a procedure that changes the background of each text box that contains a Null from white to yellow. The procedure also moves the focus to the last text box that contains a Null. The background stays yellow until the user updates the data in the text box.

Figure 1-20 shows a text box that contained a Null but now contains 1. The background of the text box changes back to white as soon as the user enters some information and moves the focus off the text box. Figure 1-21 shows the form after the new value updates the text box's value.

Figure 1.19 - The result of clicking the Do It! command button when at least one text box contains a Null.

Figure 1.20 - A yellow-highlighted text box that contained a Null but now contains 1.

Figure 1.21 - After the user enters information and moves the focus, the text box's background color is reset to white.

The following pair of procedures-named cmdSubmit_Click and MarkFieldsToEdit -examines the text boxes and yellow-highlights any text box that contains a Null. The Click event procedure for the command button loops through all the controls on the form. This includes text box as well as non-text box controls. The event procedure uses a TypeOf keyword to detect which control is a text box. Failure to take this measure can result in a run-time error because not all controls have a Value property. If the event procedure detects a text box control, it queries the control's Value property to determine whether it contains a Null. Any control with a Null triggers the code inside the If...Then statement. This code displays the message box and calls the procedure to highlight the control with the missing entry.

Private Sub cmdSubmit_Click()
'Check for valid entries.
 For Each ctl In Screen.ActiveForm.Controls
 If TypeOf ctl Is TextBox Then
 If IsNull(ctl.Value) Then
 MsgBox "Please enter information " _
 & "in both input boxes.", _
 vbInformation, _
  "Programming Microsoft Access Version 2002"
 MarkFieldsToEdit
 Exit For
 End If
 End If
 Next ctl
End Sub
 
Public Sub MarkFieldsToEdit()
 For Each ctl In Screen.ActiveForm.Controls
 If TypeOf ctl Is TextBox Then
 If IsNull(ctl.Value) Then
 With ctl
 .BackColor = RGB(255, 255, 0)
 .SetFocus
 End With
 End If
 End If
 Next ctl
End Sub

The MarkFieldsToEdit procedure also uses the TypeOf keyword to identify text boxes. When it detects a text box that contains a Null, it uses a With...End With statement to change the control's background color and to move the focus to the control. This ensures that the last text box that contains Null has the focus at the end of the procedure.

Each of the event procedures in the following code fires on the AfterUpdate event. Each procedure uses a With...End With block to change the associated control's background color back to white if the background color is currently yellow. The AfterUpdate event occurs independently for both text boxes, but the code in each procedure is identical except for the name of the associated object (either txtInput1 or txtInput2).

Private Sub txtInput1_AfterUpdate()
 With txtInput1
 If .BackColor = RGB(255, 255, 0) Then
 .BackColor = RGB(255, 255, 255)
 End If
 End With
End Sub
 
Private Sub txtInput2_AfterUpdate()
 With txtInput2
 If .BackColor = RGB(255, 255, 0) Then
 .BackColor = RGB(255, 255, 255)
 End If
 End With
End Sub

In addition to using the For...Each statements with members of a collection, such as controls on a form, you can use For...Each statements to iterate through the elements of an array. The ForEachArrayElement procedure and the NullStr1 procedure demonstrate the syntax rules for doing this. In addition, the next code sample illustrates some other interesting techniques, including conditional compilation and the use of functions to return the lowest and highest index values for an array.

The code sample assumes the declaration of a str1 array defined at the module level with index values of 0, 1, and 2. I remind you of this because the str1 declaration does not show in the listing. The procedure undertakes several tasks with arrays:

1. It declares a local array, str2, and populates it with values in each of its three elements.
2. In between declaring and populating the local array, the procedure conditionally erases any values in the module-level array, str1, by invoking the NullStr1 procedure. The conditional execution of NullStr1, which depends on the value of DebugOn, demonstrates how to use the VBA conditional compilation feature. Note the # prefixes. By the way, the NullStr1 succinctly reveals how to use a For...Each statement with an array. It loops through each of the elementsin the str1 array without ever specifying the exact number of elements in the array or the number of any element in the array.
3. The procedure uses the ReDim statement to enlarge the number of elements declared for the str2 array, and it assigns a value to the new array element. This new element results in the str2 array having one more element than the str1 array. The Print method of the Debug object confirms this by contrasting the index ranges of the two arrays in the Immediate window.
4. The procedure copies elements from the local str2 array into elements of the module-level str1 array. It performs this task until there are no remaining module-level elements into which to copy values. The built-in LBound and UBound functions capture the smallest and largest index values for the module-level array. A For...Each loop determines the progression through the elements of the local array. The ind1 variable tracks the index for the module-level array element into which the procedure copies a value. If the value of ind1 exceeds the largest index value of the module-level array, the code processes an Exit For statement and stops copying values to the module-level array.
5. Another For...Each loop for the elements of the str1 array passes successively through each value and prints that value to the Immediate window.

Sub ForEachArrayElement()
ReDim str2(1 To 3) As String
Dim el1 As Variant
Dim ind1 As Long
 
'Conditional compilation constant;
'set to False after debugging
#Const DebugOn = True
 
'Set all elements in str1 to Null
'if DebugOn flag is true
#If DebugOn Then
 NullStr1
#End If
 
'Assign values to array elements
'scoped at the procedure level;
'str2 and str1 have the same number of elements
str2(1) = "Rick"
str2(2) = "Dobson"
str2(3) = "8629"
 
'Redimension and preserve initial values;
'this makes str2 have one more element than str1
ReDim Preserve str2(1 To 4)
str2(4) = "rickd@cabinc.net"
 
'Print contrasting extents of str1 and st2 arrays
Debug.Print "Elements of str1 array have indexes " & _
 LBound(str1) & " through " & UBound(str1) & "," & _
 vbCrLf & "but elements of str2 array have indexes " & _
 "from " & LBound(str2) & " through " _
 & UBound(str2) & "."
 
'Copy procedure-level array elements into
'module-level array elements until str1
'has no more elements for copying into;
'element pointer (el1) must be a Variant
ind1 = LBound(str1)
For Each el1 In str2
 If ind1 > UBound(str1) Then Exit For
 str1(ind1) = el1
 ind1 = ind1 + 1
Next
 
'Print the values in str1
For Each el1 In str1
 Debug.Print el1
Next
 
End Sub
 
 
Sub NullStr1()
Dim el1 As Variant
 
'Assign Null to each element
'in str1 module-level array
For Each el1 In str1
 el1 = Null
Next
 
End Sub

To help clarify the operation of the ForEachArrayElement procedure, I included its output in Figure 1-22. Recall that, to achieve these results, you must declare the str1 array at the module level with a statement such as Dim str1(2) As String.

Figure 1.22 - The output to the Immediate window from the ForEachArrayElement procedure.

Two points merit some extra commentary. First, conditional compilation is a relatively new VBA procedure introduced with Access 2000. You can use the feature to conditionally execute VBA code based on a conditional compiler constant that you declare and assign a value with the #Const directive. A new #If...Then...#Else directive allows your code to conditionally execute code. The ForEachArrayElement procedure uses a conditional compiler constant named DebugOn. The #Const directive sets this constant to True. An #If...#End block conditionally invokes the NullStr1 procedure. I wanted to see the module-level element values in the process of developing the ForEachArrayElement procedure. Because the procedure writes over these values, it is not absolutely necessary to erase them. Therefore, you can bypass the calling of NullStr1 by setting DebugOn to False in the #Const directive.

The second point that might not be obvious from reviewing the code is that you must declare the data type of the parameter that passes through the elements of the array in a For...Each statement as a Variant data type. This Variant data type argument has the name el1 in the ForEachArrayElement and NullStr1 procedures. The argument's data type is unrelated to the data type of the array elements. For example, notice that the str1 and str2 array elements both have String data types.