|< Day Day Up >|
Controls are interface objects that facilitate communication between the users and the application by either displaying data or accepting input. The list box and combo box controls display a list of items from which the users can select one or many items.
You probably use a wizard to populate a list control (combo box or list box), but doing so limits the list's contents. Sometimes you need more flexibility allowing the list to determine its contents on the fly (as needed).
Both list controls have two common properties that you can manipulate programmatically to control the list contents:
There are three Row Source Type settings:
This chapter uses VBA to refer to and manipulate these two controls, and assumes you already know the general purpose and characteristics of both controls.
You're probably familiar with these three properties already because you've set them via the Properties window. You can also set them using VBA code, and in doing so, determine or change the list items.
The ControlSource property isn't all that important to us at this point a control doesn't need to be bound or unbound to be programmatically manipulated. However, chances are you won't be using VBA to dynamically control a bound control's list items too often.
Although theoretically you can populate a list control most any time, the most logical time to do so is when you load the form or when the control itself gets the focus. Either way, you set the control's RowSourceType property using the following syntax:
control.RowSourceType = value
where control identifies the list control and value is one of the string expressions listed in Table 12.1. Use the following syntax to set the RowSource property:
control.RowSource = datasource
where datasource is a table, query, SQL statement, or value list, as determined by the RowSource Type setting.
A Simple Filtering List Control
Let's work through a quick example that builds a filtering combo box for the Employees form. Specifically, you'll add a combo box to the form's header and then use the appropriate VBA code to display a list of employees in the control's drop-down list. (The example uses a combo box, but you can just as easily use a list box the syntax and properties are identical, but the combo box requires less room.)
To get started, open the Employees form in Design view. Open the form's header and insert a combo box control. Name the combo box control cboFilter and set its corresponding label's Caption property to Search For. Then, complete the following steps:
The results might not be what you expected. By default, the combo box list displays only one column the first column from its data source.
When using VBA, use the ColumnCount property to determine how many columns to display and the ColumnWidths property to determine how wide each column is. Add the following statements to the form's Open event procedure:
cboFilter.ColumnCount = 3 cboFilter.ColumnWidths = "0"
as shown in Figure 12.2.
Figure 12.2. Use VBA to manipulate control properties.
Save the form and close it. Then, re-open it in Form view and display the control's list a second time. This time, as shown in Figure 12.3, the list displays just the names. However, the employee values in the first column are still available; you just won't see them in the control's list.
Figure 12.3. Now the list displays just the data you want.
The accompanying exercise automates settings that you generally set manually. When doing so, don't forget that by default, the combo box control's Bound property, and hence, its value, is still the first column in the data source and not the first column of data in the list.
Wouldn't it be nice if the list control you just created actually did something like display the record for the employee you select? To have it do so, you must add an event procedure that responds when a list item is selected. For the purposes here, the Click event is the least work. To continue the example:
There are a number of ways to enter VBA code into a module. In the earlier example, you choose the On Open property setting to display and enter the appropriate event's stub. In the accompanying example, you use the Object and Procedure controls in the module window.
After learning all the different ways, you'll slowly slip into a routine that you're comfortable with, but we want to introduce you to all the methods for entering code. That's why you use different methods throughout the examples.
The previous example considers just one type of list a Table/Query list, which is probably the most common. Creating a field or value list is just as easy. The only key is to make sure you use the right type of RowSourceType setting to accommodate the data. For instance, the following code produces the list shown in Figure 12.7:
cboFilter.RowSourceType = "Field List" cboFilter.RowSource = "Employees"
Figure 12.7. This field list displays the names of the fields in the Employees table.
Figure 12.8 displays a value list using this code:
cboFilter.RowSourceType = "Value List" cboFilter.RowSource = "Larry;Ronald;Clint" cboFilter.ColumnCount = 1
Figure 12.8. Displaying a list of explicit values in a drop-down list.
Comment out or delete the ColumnWidths property statement (if you've saved the form, you also have to remove the Column Widths setting from the form's Properties window). This last example is impractical in real practice because it isn't dynamic, which means you have to update the RowSource property statement to update the list.
|< Day Day Up >|