The use of the Controls Collection
At regaular base people are sending my snipt of code to give edit it or give some advice with it. I notoced that the use 
of the Control Collection isn't very used. See the next example:



Sub Form_Load()
 txtName.Text = ""
 txtAddresss.Text = ""
 txtZipCode.Text =""
 txtPlace.Text =""
End Sub



What the programmer wants is clear: when the forms loads the fout textboxes has to be empty. 
If this action has to be performed on a regular base (for exmaple with a 'Cancel' or 'Clear' button) then 
it is nessecaity to type in the same code several times.

You can however make a separate procedure:



Sub LeegTextBox()
 txtName.Text = ""
 txtAddresss.Text = ""
 txtZipCode.Text =""
 txtPlace.Text =""
End Sub



Under every event that must clear teh textboxes you just call the procedure:



Sub Form_Load()
 Call LeegTextBox
End Sub


Sub cmdCancel_Click()
 Call LeegTextBox
End Sub



You can simplified it by making use of the Controls Collection:



Sub LeegTextBox()
 Dim Control
 For Each Control In Me.Controls
  If TypeOf Control Is TextBox Then Control.Text = ""
 Next Control
End Sub



To do it completely 'right' you have a module form to set the procedure public and use a parameter to the procedure 
to set the form:



Public Sub LeegTextBox(Byref X as Form)
 Dim Cntr
 For Each Cntr In X.Controls
  If TypeOf Cntr Is TextBox Then X.Cntr.Text = ""
 Next Cntr
End Sub



Calling the procedure is as followed:



Sub Form_Load()
 Call LeegTextBox(Me)
End Sub



It can be done from every form and every event. Of course you can set almost every other property like this. 
Also you can use it to call almost every kind of control. This is done with the TypeOf command.



The use of indexed controls

The use of indexex controls has the advantage that it is not necessary to place the same code under similair events 
of the same controls. Suppose you have five textbox-controls in which you want the user to put the next values:



 fristname (name = txtFirstName)
 lastname  (name = txtLastName)
 address   (name = txtAddress)
 zipcode   (name = txtZipCode)
 city      (name = txtCity)



The input of the first three boxes you want to set the first character to uppercase; the last two you want to set the 
whole value to uppercase. If you choice to give the texboxes different names (for clearity is it a good reason to do so)
your code looks like:



Sub txtFirstName_LostFocus()
 txtFirstName.Text = StrConv(txtFirstName.Text, vbProperCase)
End Sub

Sub txtLastName_LostFocus()
 txtLastName.Text = StrConv(txtLastName.Text, vbProperCase)
End Sub

Sub txtAddress_LostFocus()
 txtAddress.text = StrConv(txtAddress.Text, vbProperCase)
End Sub

Sub txtZipcode_LostFocus()
 txtZipcode.Text = UCase$(txtZipCode.Text)
End Sub

Sub txtCity_LostFocus()
 txtCity.Text = Ucase$(txtCity.Text)
End Sub



If you use indexed controls it looks like:



 FirstName  (name = txtInput; index = 0)
 LastName   (name = txtInput; index = 1)
 Address    (name = txtInput; index = 2)
 Zipcode    (name = txtInput; index = 3)
 City       (name = txtInput; index = 4)




Sub TxtInput_LostFocus(Index As Integer)
 Select Case Index
 Case 0, 1, 2
  TxtInput(Index).Text = StrConv(TxtInput(Index).Text,vbProperCase)
 Case 3,4
  TxtInput(Index).Text = UCase$(TxtInput(Index).Text)
 End Select
End Sub



It is a lot easier and little to type! Still you can use the earlier way to empty all boxes at once (the control
 does not change). The use of indexed controls is often in combination with the Select.... Case statement.



An other example of indexed controls.



Everybody uses commandbuttons. Suppose you have five buttons:



 New    (name = cmdKnop; index = 0)
 Change (name = cmdKnop; index = 1)
 Save   (name = cmdKnop; index = 2)
 Delete (name = cmdKnop; index = 3)
 Exit   (name = cmdKnop; index = 4)



The code looks like



Sub cmdKnop_Click(Index As Integer)
 Select Case Index
 Case 0 ‘code to add an item
 Case 1 ‘Code to change an item
 Case 2 ‘Code to save an item
 Case 3 ‘Code to remove an item
 Case 4
  Unload Me
 End Select
End Sub



What you want is a way to disable certain buttons while your user is executing one action, you cannot add and 
remove at the same time. When you are adding the buttons 'Remove' and 'Change' cannot be available. After saving 
the item they must be enabled again.
It can look like this:



Sub cmdKnop_Click(Index As Integer)
 Select Case Index
 Case 0‘new
  cmdKnop(1).Enabled = False
  cmdKnop(3).Enabled = False
  ‘code to add an item
 Case 1‘change
  ‘Code to change an item
 Case 2‘save
  ‘Code to save an item
  cmdKnop(1).Enabled = True
  cmdKnop(3).Enabled = True
 Case 3‘remove
  ‘Code to remove an item
 Case 4‘exit
  Unload Me
 End Select
End Sub



Of course this can be done with less work.. Instead of setting each commandbutton at a time you use a variable:



Sub cmdKnop_Click(Index As Integer)
 Select Case Index
 Case 0‘new
  Call SetCommand("10101")
  'code to add an item
 Case 1‘change
  ‘Code to change an item
 Case 2‘save
  ‘Code to save an item
  Call SetCommand("11111")
 Case 3‘remove
  ‘Code to remove an item
 Case 4‘exit
  Unload Me
 End Select
End Sub



Sub SetCommand(which As String)
 Dim lngX As Long
 For lngX = 1 To Len(which)
  If Mid(which, lngX, 1) = 0 Then 
   Me.cmdKnop(lngX - 1).Enabled = False 
  Else 
   Me.cmdKnop(lngX - 1).Enabled = True
  End If
 Next lngX
End Sub



The procedure SetCommand check each character of the used string if it is a one (1) or a zero (0). 
Depending on the value AND the location in the string the proper commandbutton will be set.
To use it for several forms you must adjust the code a little bit.



Sub SetCommand(which As String, X As Form)
 Dim lngX As Long
 For lngX = 1 To Len(which)
  If Mid(which, lngX, 1) = 0 Then 
   X.cmdKnop(lngX - 1).Enabled = False 
  Else 
   X.cmdKnop(lngX - 1).Enabled = True
  End If
 Next lngX
End Sub



Using the procedure is done with: Call SetCommand("11111", Me)