Presenting a pair of Listbox controls to the user and allowing the user to move an item from one to the other is a popular and effective user interface technique. Microsoft Access doesn’t provide any intrinsic controls for performing this, but as Rebecca Riordan demonstrates this month, it’s easy to create linked Listbox controls and base them on a single table, two tables, or even data created at runtime.
Paired Listbox controls (such as those shown in Figure 1, from the sample database in the accompanying Download) are a useful technique for allowing users to select multiple items from a list. A single multi-select Listbox control performs the same function, of course, but selecting multiple items from a single Listbox is more difficult for the user to manipulate. With a single Listbox it’s entirely too easy, for example, for a single accidental click to eliminate all of the user’s previous selections. Of course, paired Listbox controls require far more space than a single multi-select Listbox, and this limits their use, but when you can afford the screen real estate, multiple Listboxes are well worth considering.
Given that Microsoft Access is a database development environment, implementing paired Listbox controls starts with considering the data to display. There are three choices: You can display data from a single table, or from two tables, or the items can be created and manipulated in memory and never stored to the database at all. Since each of these models requires a slightly different technique, I’ll examine each in turn.
Figure 2 shows a (ridiculously simple) schema that can be used for a one-table implementation. In a real application, you’d almost certainly have more than two fields in the table, but the two fields that I’m showing are required–a field to be displayed to the user, and a field to indicate whether or not the user has selected the record. In this example, the Description field contains the text to be displayed in the Listbox controls; the InSelectedList field, a Boolean, indicates the record’s status.
Although you need a field to display to the user and a field to indicate the status of the record, they don’t need to be Text and Yes/No fields. Use whatever data types make the most sense in the context of your application. Different data types may require slightly different manipulation, but the general approach will be the same. By the way, the semantics of your application may also require a verb other than “select” for the status. For example, records might be “completed” or “ordered.” Again, use whatever makes the most sense within the context of your application.
The first step in implementing the paired Listbox controls (after you’ve laid out the form, of course) is to bind the Listboxes. In a one-table implementation, this is simple: The source Listbox (named lbSource in the sample) is bound to the records that are not selected, and the destination Listbox (named lbDestination in the sample) is bound to the records that are selected. The two SQL statements look like this:
Dim theSQL As String theSQL = "SELECT Description, InSelectedList FROM " & _ BooleanList WHERE " Me.lbSource.RowSource = theSQL & "NOT InSelectedList" Me.lbDestination.RowSource = theSQL & "InSelectedList"
This code is in the FormOpen event handler in the example, simply for clarity, but the RowSource property could just as easily be set in the Properties window.
The next step, of course, is to implement the event handlers for the four buttons, named btnSelect, btnDeselect, btnSelectAll, and btnDeselectAll in the example. Here’s the code for the Click event handler for the first of these buttons, btnSelect:
Dim rsSource As DAO.Recordset Dim theBug As String If Me.lbSource.ItemsSelected.Count = 0 Then Beep Exit Sub End If Set rsSource = Me.lbSource.Recordset For Each itm In Me.lbSource.ItemsSelected theBug = Me.lbSource.ItemData(0) rsSource.FindFirst ("Description = '" & _ Me.lbSource.ItemData(itm) & "'") rsSource.Edit rsSource!InSelectedList = True rsSource.Update Next itm Me.lbSource.Requery Me.lbDestination.Requery
The principle here is simple: The procedure sets the InSelectedList field of the selected record to True, and then re-queries the two Listbox controls. The WHERE clause of the RowSource effectively moves the item from one control to the other.
The first two lines declare a temporary Recordset variable and a String, theBug, that I’ll discuss in a moment. The If statement simply checks that an item is selected in the source Listbox. If not, the procedure sounds a beep and exits. This isn’t very sophisticated error handling (you’d probably want to implement something a little more helpful than a beep in a production system). The next line assigns the Recordset underlying the source Listbox to the rsSource variable.
The core of the procedure is the For Each loop, which will, of course, be executed for each item selected in the source Listbox. The first line inside the loop sets the variable theBug to the value of the first item in the list. There appears to be a bug in the Listbox control that results in the ItemData collection returning Null the first time it’s referenced using an enumerator (the value returned by the For Each loop). The otherwise unnecessary assignment of the first value to theBug works around this issue. (You should check this behavior on your own machine, by the way. I’ve lost track of how many times colleagues have verified a bug on my machine that they’ve been unable to duplicate on their own. Harry Potter obviously left some gremlins behind the last time he “wizzed” through.)
The next line in the procedure executes a FindFirst method call against the rsSource Recordset, passing the field name “Description” and the value of the selected item. The enumerator variable itm returns the index of the selected item, not the value of the item itself, so the procedure uses Me.lbSource.ItemData(itm) to reference the actual value.
The next three lines are standard DAO syntax for editing the value of a record (in this case, setting the value of the InSelectedList field to True).
The final lines of the procedure re-query the two Listbox controls to update the display. Depending on available memory, these method calls might require a disk access, which is unfortunate. Remember, though, that you’ll be working with a very small dataset here, so in reality this rarely presents a problem.
The Click event handler for the btnDeselect button, which transfers a selected value back to the source Listbox, is functionally identical to the Select button. It simply sets the value of the InSelectedList field to False rather than True.
The code for the Click event handler for btnSelectAll, which transfers all of the items, would look like this:
Dim rsSource As DAO.Recordset Set rsSource = Me.lbSource.Recordset rsSource.MoveFirst Do While Not rsSource.EOF rsSource.Edit rsSource!InSelectedList = True rsSource.Update rsSource.MoveNext Loop Me.lbSource.Requery Me.lbDestination.Requery
As you can see, the structure of this procedure is very similar to that of the btnSelect click event hander. However, instead of looping through the selected items in the source Listbox, the procedure loops through the entire Recordset, changing the value of the InSelectedList field of every row. Again, the handler for the btnDeselectAll button is identical, but sets the value to False rather than True.
This example assumes a single table containing a status field. The second implementation paradigm requires two tables, but no status field. In the case of a two-table implementation, the simple presence of a record in one table or the other indicates the record’s status. This is, in practice, a somewhat less frequent situation, but it’s neither invalid nor unknown.
In a two-table implementation, each Listbox control is bound to the appropriate table–in the case of the example, the SourceTable and DestinationTable, respectively. This can be done in the Property window or, as in our previous example, in the Form Open event.
The code for the btnSelect click event handler would look like this:
Dim rsSource As DAO.Recordset Dim rsDestination As DAO.Recordset Dim theBug As String If Me.lbSource.ItemsSelected.Count = 0 Then Beep Exit Sub End If Set rsSource = CurrentDb.OpenRecordset("SourceTable", _ dbOpenDynaset) Set rsDestination = _ CurrentDb.OpenRecordset("DestinationTable", _ dbOpenDynaset) For Each itm In Me.lbSource.ItemsSelected theBug = Me.lbSource.ItemData(0) rsDestination.AddNew rsDestination!Description = Me.lbSource.ItemData(itm) rsDestination.Update rsSource.FindFirst "Description = '" & _ Me.lbSource.ItemData(itm) & "'" rsSource.Edit rsSource.Delete Next itm Me.lbSource.Requery Me.lbDestination.Requery
This procedure is slightly longer, but no more difficult, than the corresponding code for a one-table implementation. In this case, you must physically move a record from one table to the other, but again you use standard DAO code inside the For Each loop. Note that the example tables each contain only a single field. In a production application, you would, of course, need to copy all required fields from one table to the other. Be careful: You must also add the record to the destination table before deleting it from the source table. Otherwise, you’ll copy the values “#Deleted” into the new record. (And yes, I felt very stupid when I did that…)
The code for the btnSelectAll click event is shown in the following block. Like the btnSelectAll handler for the one-table implementation, it loops through the entire Recordset rather than only the selected items, but is otherwise identical:
Dim rsSource As DAO.Recordset Dim rsDestination As DAO.Recordset Set rsSource = CurrentDb.OpenRecordset( _ "SourceTable", dbOpenDynaset) Set rsDestination = CurrentDb.OpenRecordset _ ("DestinationTable", dbOpenDynaset) rsSource.MoveFirst Do While Not rsSource.EOF rsDestination.AddNew rsDestination!Description = rsSource!Description rsDestination.Update rsSource.Edit rsSource.Delete rsSource.MoveNext Loop Me.lbSource.Requery Me.lbDestination.Requery
The final implementation method relies on creating the two lists at runtime rather than pulling the data from one or more tables. This is a slightly complex implementation, but also the most flexible. You could use an array to store the items, but a Collection is far simpler to manipulate.
The following code shows the declarations from the example. The two Collections, collSource and collDestination, are declared as private module-level variables so that they’re available to all of the procedures in the module. The variable hasBeenLoaded, also declared private at the module level, is a Boolean used to control initialization.
Private collSource As New Collection Private collDestination As New Collection Private hasBeenLoaded As Boolean
Microsoft Access supports binding a Listbox control to a user-defined function by simply specifying the name of the function (without an equals or a parenthesis) as the RowSource type property and setting the RowSource property to an empty string. The function itself has a defined signature, shown here:
Function DisplaySource(fld As Control, _ id As Variant, row As Variant, _ col As Variant, code As Variant) _ As Variant Dim varReturn As Variant Select Case code Case acLBInitialize If Not hasBeenLoaded Then collSource.Add ("An item") collSource.Add ("Another item") collSource.Add ("This is an item too") collSource.Add ("Yet another item") End If varReturn = True Case acLBOpen varReturn = Timer Case acLBGetRowCount varReturn = collSource.Count Case acLBGetColumnCount varReturn = 1 Case acLBGetColumnWidth varReturn = -1 Case acLBGetValue varReturn = collSource.Item(row + 1) Case acLBGetFormat varReturn = -1 Case acLBEnd varReturn = Null Case acLBClose varReturn = Null End Select DisplaySource = varReturn End Function
The Listbox data function, which must return a variant, receives five arguments:
- fld–The control itself.
- id–A variant used to uniquely identify the control.
- row–Specifies the value to be returned.
- col–Specifies the value to be returned.
- code–Determines the action that the function should perform.
Most Listbox data functions conform more or less to the structure shown in the listing–a case statement responding to each of the values of the code argument.
The acLBInitialize code, as you’d expect, indicates that the list should be initialized. In the example, it’s used to load the values into the collSource collection, using the standard Add method. When using the data function with paired Listbox controls, you only want to initialize these values once, when the form is first loaded. If you reloaded the data each time the user made a select, you’d override any changes the user has made. The Listbox data function uses the hasBeenLoaded status variable to control this. The Listbox data function is called before the form’s Load event, so the variable is set to True in that handler, and the collection values are only loaded if it’s False. In either case, varReturn, the value returned by the function, is set to True.
The acLBOpen code is passed when the Listbox is first opened. The Listbox data function should return a unique value when passed this code. The value returned by the intrinsic VBA Timer function is convenient, but you can pass any value, provided it’s unique within the application.
The acLBGetRowCount and acLBGetColumnCount code values request the number of rows and columns in the list, respectively. The example procedure returns the Count of items in the collection and 1, respectively. Incidentally, this is one example of why a collection is generally easier to use than an array–it’s difficult to determine the number of actual, non-null values in an array, and you generally wind up maintaining a separate count variable.
The acLBGetValue and acLBGetFormat codes will be passed once for each item in the list. The function should return the actual value when it receives acLBGetValue. In the case of the example, the function simply returns the value of the appropriate item in the collection. However, the row argument passed to the function is zero-based, while collection indexes are one-based, so the value returned is actually row+1. The function returns -1, indicating the default format, when passed the acLBGetFormat code.
Finally, acLBEnd and acLBClose are passed when the form is closing. You’d perform any required clean-up here, but in the example, I simply pass a Null value because no clean-up is required.
The example uses an almost identical procedure, DisplayDestination, as the row source for the lbDestination Listbox. In a production system, you could easily combine the two functions, using the id argument (which, you’ll remember, uniquely identifies the control), to determine which values to return. Combining the functions would reduce code size and might (although not necessarily) simplify maintenance, but I’ve kept them separate in my example for clarity.
Once you’ve defined the Listbox data functions, you need to implement the code for the selection buttons, which is similar to the other procedures I’ve shown. The code for the btnSelect button follows and, again, the procedure for btnDeSelect is almost identical:
Dim theDescr As String Dim theBug As String If Me.lbSource.ItemsSelected.Count = 0 Then Beep Exit Sub End If For Each itm In Me.lbSource.ItemsSelected theBug = Me.lbSource.ItemData(0) theDescr = collSource.Item(itm + 1) collSource.Remove (itm + 1) collDestination.Add (theDescr) Next itm Me.lbSource.Requery Me.lbDestination.Requery
As with the other select procedures, the only difference lies within the For Each loop, which (after dealing with the Null enumerator bug) adds the item to the collDestination collection and removes it from the collSource collection. The itm enumerator is zero-based, so the collection item referenced is itm + 1.
Here’s the code for the btnSelectAll click handler:
For Each itm In collSource collDestination.Add (itm) Next itm For x = 1 To collSource.Count collSource.Remove (1) Next x Me.lbSource.Requery Me.lbDestination.Requery
This code is actually slightly simpler than the corresponding code for the other implementations. It consists of two simple loops: one that adds all of the items to collDestination, and another that removes all of the items in collSource. Again, the order is important, and again, the DeselectAll procedure is almost identical.
As you’ve seen, implementing paired Listbox controls isn’t particularly difficult, but the examples you’ve seen this month have one serious drawback: With the exception of the collection-based version, the changes made by the user are committed immediately, and there’s no way for the user to cancel the procedure. In many situations this is fine–after all, the interface itself makes it simple for users to undo their changes, but sometimes you’ll want to implement explicit functionality to save or cancel the changes. Next month, I’ll show you the mechanism for doing just that.