Hack 86. Bulk-Update Controls on a Form

Tap the Controls property to make fast property changes.

When working with a form's design, typically you set properties for controls with the property sheet. You can select single controls one by one and set properties, or you can select a handful of controls and update them together. When you use the latter method, the number of properties listed on the property sheet shrinks to just those that are common to all the selected controls.

This works fine for the most partthat is, setting properties to multiple controls isn't difficult. But what if you had to see what a property is before deciding whether to change it? In this situation, the property sheet won't help you. When multiple controls are selected, the property sheet can't display individual control properties. It displays a property's value if all the selected controls have the same value for the property, but that isn't always the case.

8.16.1. Accessing Control Properties in Code

Luckily, Access has a Controls property that belongs to the form object, and a Properties property for the controls themselves. This makes it possible to read and write control properties. To start, Example 8-6 shows a routine that reads control properties.

Example 8-6. Reading control properties

Sub list_control_properties() 
Dim form_name As String 
Dim ctl As Control 
Dim list_type As Integer 
Dim prop_num As Integer

form_name = "frm1" ' change as needed! 
DoCmd.OpenForm (form_name), acDesign

list_type = 2 ' change to use with Select Case below

Select Case list_type 
  Case 1 
  ' list names and values of properties for single control 
  With Forms(form_name).Controls(1)
	For prop_num = 0 To .Properties.Count - 1 
       Debug.Print .Name & ": " & _ 
	   .Properties(prop_num).Name & ": " & _
		   .Properties(prop_num).Value
	Next
  End With
  Case 2
  ' list value of entered property for all controls on form
  With Forms(form_name)
	For Each ctl In Forms(frm_name).Controls
       On Error Resume Next
	   Debug.Print ctl.Name & ": Caption=" & _
          ctl.Properties("Caption") 
    Next 
  End With 
End SelectEnd Sub

The routine in Example 8-6 has two variations of reading control properties, which you can choose by setting the list_type variable to 1 or 2.A Select Case statement uses this number to run one or the other code snippet. When the list_type variable is set to 1, the properties of a single control are read. In particular, each property's name and value are written to the Immediate window. The single control is referenced in Example 8-6 simply as .Controls(1). You can enter the name of an actual control instead by putting the name in quotes.

Figure 8-25 shows the results of returning all the property names and values for a single control.

When the list_type variable is set to 2, all the controls on the form are tapped. A single property that is entered into this line indicates which property to return:

Debug.Print ctl.Name & ": Caption=" & ctl.Properties("Caption")

Figure 8-25. Returning a list of control properties

Also note the On Error Resume Next statement before this line. This line is included because not all properties exist for all controls. In this example, the Caption property is being accessed, but some controls, such as text boxes, don't have captions. The error trap keeps the procedure going, and only controls that have the indicated property make it into the Immediate window, as shown in Figure 8-26.

Note that the routine in Figure 8-26 addresses a form in the code. The form must be open for the code to work, presumably in Design mode because the point of this hack is to make bulk design changes. The DoCmd statement takes care of opening the form in Design mode; just provide the name of your form. In the following line, replace frm1 with the name of your form:

form_name = "frm1" ' change as needed!

8.16.2. Changing Properties the Easy Way

Example 8-7 shows a routine that changes the ForeColor property to red for all text boxes on the form. To work with a single type of control, the code tests the ControlType property. If the control type matches the enumeration value, the property is updated.

Figure 8-26. Returning the Caption property for controls that have one

Example 8-7. Working with a single control type

Sub update_controls() 
Dim ctl As Control 
Dim form_name As String 
form_name = "frm1" 
DoCmd.OpenForm (form_name), acDesign
  With Forms(form_name)  
    For Each ctl In Forms(frm_name).Controls 
      If ctl.ControlType = acTextBox Then 
        ctl.Properties("ForeColor") = vbRed 
      End If 
    Next 
  End With 
End Sub

You can choose to change a property for all the controls on a form or for particular control types. If you're working with the full set of controls, it's a good idea to use an On Error Resume Next statement so that the process won't bomb when a control doesn't have the particular property.

The code in Example 8-7 addresses text boxes only. Figure 8-27 shows how to use the Object Browser to find the members of the acControlType enumeration.