Working with Multiselect Controls

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.

Table 12.2. Multiselect Property Settings

Setting	Description	Integer Value
None	The default setting, which doesn't allow multiple selections.	0
Simple	Select or deselect multiple items by clicking or pressing the spacebar.	1
Extended	Select or deselect multiple items by holding down the Shift key and using the down arrow to extend the selection. Or, hold down the Ctrl key and click specific items for a noncontiguous selection.	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.

Figure 12.16. The procedure warns you when there isn't at least one item selected.

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.

Figure 12.17. Select a couple of items from the multiselect list box.

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.

Figure 12.18. Print the selected items to the Immediate window.