April 18, 2011, 1:55 p.m.
posted by osi
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.
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 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")
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.
Note that the routine in Figure 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!
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.
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 shows how to use the Object Browser to find the members of the acControlType enumeration.