PROGRAMMING FOR POWER WITH MULTISELECT LISTBOX CONTROLS
In addition to giving the same capability to do lookups that combo boxes give, the Access ListBox control allows you to do the following:
-
Display lists so that the user can see more than one—if not all—of the choices onscreen
-
Have users select more than one choice from the list and keep track of them so that you can use the choices in a task
Note
All the examples given in the earlier sections for combo boxes also can be used with list boxes.
Using list boxes in the standard way takes no real programming; using them for multiselection, however, requires some code to read them.
List Box Properties Dealing with Multiple Selection
When working with list boxes in multiselect mode, you can use certain properties. Table 10.3 lists properties used when dealing with list boxes in multiselect mode. These properties can be accessed through code to manipulate the selected items. Ctrl+clicking an item selects or deselects that item.
Note
The arrays and collections, such as the ItemsSelected collection used by the ListBox object, use a base 0, as do other Access collections. If a list box contains three items, the index is 0 through 2.
Manipulating Items Selected in a Multiselect List Box with VBA
Like most programming with VBA and Access, the trick to understanding how to manipulate multiselect list boxes in VBA is to understand the properties and collections—and also to have a simple example.
A Simple Example for Getting Selected Items
In the Chap10.mdb database is the MultiSelectListBoxExample1 form, on which you'll find two controls: a list box and a text box. When this form is run, you see that the lboEmployeeToChoose list box is filled with the last and first names of employees (see Figure 10.26). When you click a name in the list box, it appears in the txtNamesChosen text box.
Figure 10.26. This form is an example of using VBA to manipulate a list box with its Multi Select property set to Simple.
The row source for the lboEmployeeToChoose list box is the following SQL statement:
SELECT DISTINCTROW [Employees].[EmployeeID], [Employees].[LastName]& ", "&[Employees].[FirstName] FROM [Employees] order by LastName, FirstName;
In addition to having the Multi Select property set to Simple, the real power behind this form is the Control Source for the txtNamesChosen text box:
=ShowNamesChosen()
The ShowNamesChosen()function can be found in the General routines section of the MultiSelectListBoxExample1 form module. Listing 10.5 shows the code for this function in its entirety.
Listing 10.5. Chap10.mdb: Displaying the Current Names Chosen
Function ShowNamesChosen() As String
Dim varEmployee As Variant
Dim strTemp As String
'-- for each of the items in the ItemsSelected collection
For Each varEmployee In Me!lboEmployeeToChoose.ItemsSelected()
'-- If not the first item, put a carriage return line feed
'-- in front of the item
If Len(strTemp) <> 0 Then
strTemp = strTemp & vbCrLf
End If
'-- Grab the second column over of the current item
strTemp = strTemp & Me!lboEmployeeToChoose.Column(1, varEmployee)
Next varEmployee
'-- Assign the final string pass it back
ShowNamesChosen = strTemp
End Function
|
The last example is fairly straightforward: it simply shows how to iterate through the list box's ItemsSelected collection. This is done with the following code:
For Each varEmployee In Me!lboEmployeeToChoose.ItemsSelected()
(For more information on the For Each statement, see Chapter 2, “Coding in Access 2000 with VBA.”)
Notice that in the following line of code, the second column is the one that's being concatenated to the text box:
strTemp = strTemp & Me!lboEmployeeToChoose.Column(1, varEmployee)
Finally, for the txtNamesChosen text box to be recalculated, you need to put the following code line in the AfterUpdate event of the list box:
Me.Recalc
Although this example is useful for showing how to access the ItemsSelected collection, it shows only one aspect of manipulating a multiselect list box. It doesn't show the whole picture of setting preselected items in a list box or saving selected items to a table. The following example does this very task.
Example for Getting/Setting Selected Items from/to a Table
There are a number of reasons for needing routines for storing selected items to a table, reading them from that table at a later time, and then preselecting the items back in the same list box. Some examples might include choosing
The reports the user wants to print at the end of day
The tasks that must be performed before another can be marked as completed
The tables that should be chosen for a particular task, such as exporting
The last task is what the following example, found on the MultiSelectListBoxExample2 form, is based on. This example uses two tables: TablesInSystem and TablesChosen. Both tables contain one field, TableName. For the sake of simplicity, this example performs three tasks:
It compares the current tables listed in TablesChosen with the lboTableToChoose list box and marks particular table names as selected in the list box, if they exist in TablesChosen.
It lets users select or deselect tables from the list box and allows the display of them in the txtTablesChosen text box.
It allows users to save the new set of selected tables.
The example doesn't do anything with the tables chosen—this is left up to you. Figure 10.27 shows what the MultiSelectListBoxExample2 form looks like when it's first opened in Run mode.
Figure 10.27. Selections can be saved to a table and then reselected by opening the form used in this example.
The first task in the list is done in the OnLoad event of the form, as Listing 10.6 shows.
Listing 10.6. Chap10.mdb: Noting the Previously Chosen Tables
Again, there are more ways to perform the preceding task, and some might even be more efficient. But this way is easier to follow. The first step is that the TablesChosen table is opened and looped through. Each list box's ItemData collection is compared to each record in the recordset. This is done with the following code lines:
For intCurrTable = 0 To Me!lboTableToChoose.ListCount - 1
'-- If there is a match, mark it in the list box as selected
If dynTablesChosen!TableName = _
Me!lboTableToChoose.ItemData(intCurrTable) Then
Me!lboTableToChoose.Selected(intCurrTable) = True
Exit For
End If
Notice the use of the ListCount property and the Selected() array. A list box's ListCount property gives the number of entries in the overall list itself. The Selected() array holds an array of Booleans that specify whether the individual rows are selected.
Tip
After the table name is found, the For loop is exited by using the Exit For command. Although this isn't considered “top-down” programming, it does help performance and, therefore, it's your decision whether to use it.
Again, the Form_Load routine is used to load the list box with the preselected items that were saved from the last time this routine was used.
The routine to display the selected items in the txtTablesChosen text box on the right is similar to the ShowNamesChosen() function earlier in the section “A Simple Example for Getting Selected Items.” Listing 10.7 shows this function, ShowTablesChosen().
Listing 10.7. Chap10.mdb: Displaying Selected Table Names
The only difference, besides using different variable names, is that this routine gets the actual bound column to concatenate to the temporary string, as seen in the following code line:
strTemp = strTemp & Me!lboTableToChoose.ItemData(varTable)
Another item that's similar to the previous example, shown earlier in the section “A Simple Example for Getting Selected Items,” is that you need to put the Me.Recalc command in the list box's AfterUpdate event.
The final piece of the puzzle in this example is the code for saving the current items that were selected during the session. This code, placed on the cmdSaveTablesChosen command button, can be seen in Listing 10.8.
Listing 10.8. Chap10.mdb: Saving Currently Selected Items
This code first deletes the current entries in the TablesChosen table and then loops through the ItemsSelected collection, adding a new record for each entry.
These methods are only a couple of examples of the power of the multiselect list box. As you work with them, you'll come up with literally hundreds of ways to use them as you build your applications. More examples of using the multiselect list box can be found in Chapter 11, “Creating Powerful Reports.”