Part II: Programming Microsoft Access
408
l (^) A collection might contain many thousands of objects. Although performance suffers
when a collection contains several tens of thousands of objects, a collection is a handy way
to store an arbitrary number of items as an application runs. You’ll see several examples of
using collections as storage devices in this book.
The With statement
The With statement enables you to loop through all the members of an object collection, setting
or changing the properties of each member. Any number of statements can appear between the
With and End With statements. With statements can be nested.
As an example, consider the code using the following For...Next looping construct. This code
loops through all members of a form’s Controls collection, examining each control. If the con-
trol is a command button, the button’s font is set to 10 point, Bold, Times New Roman:
Private Sub cmdOld_Click()
Dim i As Integer
Dim c As Control
For i = 0 To Me.Controls.Count - 1
Set c = Me.Controls(i) ‘Grab a control
If TypeOf c Is CommandButton Then
‘Set a few properties of the control:
c.FontName = “Times New Roman”
c.FontBold = True
c.FontSize = 12
End If
Next
End Sub
Don’t be confused by the different expressions you see in this example. The heart of this procedure
is the For...Next loop. The loop begins at zero (the start value) and executes until the i vari-
able reaches the number of controls on the form minus one. (The controls on an Access form are
numbered beginning with zero. The Count property tells you how many controls are on the
form.) Within the loop, a variable named c is pointed at the control indicated by the i variable.
The If TypeOf statement evaluates the exact type of control referenced by the c variable.
Within the body of the If...Then branch, the control’s properties (FontName, FontBold, and
FontSize) are adjusted. You’ll frequently see code such as this when it’s necessary to manipulate
all the members of a collection.
Notice that the control variable is referenced in each of the assignment statements. Referencing
control properties one at a time is a fairly slow process. If the form contains many controls, this
code executes relatively slowly.
An improvement on this code uses the With statement to isolate one member of the Controls
collection and apply a number of statements to that control. The following code uses the With
statement to apply a number of font settings to a single control.