Automating Microsoft Access with VBA

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:

• Row Source Type Specifies whether the list is based on a table or query, a list of values, or a list of table or query field names.

• Row Source Depends on the Row Source Type setting to determine the type of data displayed by the control.

There are three Row Source Type settings:

• When the Row Source Type setting is Table/Query, the Row Source setting must be a table, query, or SQL statement.

• When the Row Source Type setting is Value List, the Row Source setting must be an explicit list of items separated by the semicolon character.

• When the Row Source Type setting is Field List, the row Source setting must be a table, query, or SQL statement. You're unlikely to ever use this setting, but Access relies on it for some of the wizards, which are themselves written as Access forms (using VBA as the programming language).

NOTE

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.

NOTE

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:

1. Open the form's module. Add the two property statements shown here between the provided stubs:

   Private Sub Form_Open(Cancel As Integer) cboFilter.RowSourceType = "Table/Query" cboFilter.RowSource = "Employees" End Sub

2. Switch to Form view and open the new combo list control's drop-down list as shown in Figure 12.1.

   Figure 12.1. The combo box displays the contents of the first column in the Employees table.

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.

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.

TIP

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:

1. Return to the VBE using Alt+Tab or by selecting the appropriate icon on the Windows taskbar.

2. In the form's module, choose cboFilter from the Object control's drop-down list, as shown in Figure 12.4.

   Figure 12.4. Choose an object from the Object control.

3. Next, choose Click from the Procedure control (the combo box to the right). Doing so enters the appropriate event stub, as shown in Figure 12.5.

   Figure 12.5. Let the editor enter the event's stub.

4. Insert the following code between the stub's beginning and ending statements (not including the stub statements of course):

   Private Sub cboFilter_Click() Dim strSQL As String strSQL = "SELECT * FROM Employees " & _ "WHERE EmployeeID = " & cboFilter.Column(0) Debug.Print strSQL Forms!Employees.RecordSource = strSQL End Sub

5. View the form in Form view and choose a name from the list control's drop-down list. Doing so automatically displays that employee's record. Figure 12.6 shows the result of choosing Terry Briggs. If you switch to the VBE, you'll see the modified SQL statement in the Immediate window.

   Figure 12.6. Selecting Terry Briggs in the list control displays his employee record.

NOTE

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.8 displays a value list using this code:

cboFilter.RowSourceType = "Value List" cboFilter.RowSource = "Larry;Ronald;Clint" cboFilter.ColumnCount = 1

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.