Automating Microsoft Access with VBA

The list box control does something the combo box can't do. You can select multiple items in a list box. By default, the list box control allows only one selected item. By setting the control's Multi Select property to Simple or Extended, you allow the users to select more than one item from the list.

More than likely, you'll set this property when you create the control, but you can use VBA to modify the property using the form

listbox.MultiSelect = setting

where setting is one of the three values listed in Table 12.2.

Determining What's Selected and What's Not

The value of a combo box or a list box set to a single selection is easy to get by referring to the control's Value property in the form

control.Value

In fact, the Value property is the object's default and you don't even have to include the Value keyword. However, you probably should include it, because the code's much easier to read with the property.

Getting the value of a multiselect list box takes more work because there's more than one value to handle, which makes it a good candidate for a For Each statement. Using this statement, you can cycle through all the control's selected items.

Review the For Each statement in "Working with Collections," p. 119.

Let's look at a quick example of a multiselect list box that uses the For Each statement to print the selected items to the Immediate window. Open the unbound example form and insert a list box. Name the control lstCustomers. Set the Row Source property to the following SQL statement:

SELECT Client FROM Clients

Then, set the Multi Select property to Simple.

Use any of the methods you've learned to open the form's module and enter the following event procedure:

Private Sub lstCustomers_LostFocus() Dim varItem As Variant Dim lst As Access.ListBox Set lst = lstCustomers 'check for at least one selected item If lst.ItemsSelected.Count = 0 Then MsgBox "Please select a customer", _ vbOKOnly, "Error" Exit Sub End If 'cycle through selected items 'deselect selected items For Each varItem In lst.ItemsSelected Debug.Print lst.ItemData(varItem) lst.Selected(varItem) = 0 Next End Sub

Return to the form and view it in Form view. The event procedure uses the Lost Focus event, which occurs when you leave the list box. First, let's see what happens when there are no items selected. Press Tab three times to both give focus to and then move it from the list box (don't select anything in the list box). Access displays the message box shown in Figure 12.16. Click OK to clear it.

Before trying to retrieve the selected items, you want to make sure that there is at least one selected item. The If statement checks the number of items in the ItemsSelected collection. If it's 0, that means there are no items selected.

Tab back to the list box and click the first and third items, as shown in Figure 12.17. Then, select either of the combo boxes from the prior examples to trigger the control's Lost Focus event.

After determining that there are selected items, the For Each statement loops through the ItemsSelected collection. The ItemData property equals the item text, which the Debug.Print statement prints to the Immediate window, as shown in Figure 12.18. Then, the corresponding Selected property is set to 0, which has the effect of deselecting the item.