This month, Rebecca Riordan continues her examination of paired Listbox controls by adding two additional functions: deferring data updates until users explicitly commit their changes, and restoring the contents of the paired Listbox controls to their initial state.
Last month, I showed how to implement paired Listbox controls like the ones shown in Figure 1. If you can afford the space, a pair of Listbox controls is an excellent mechanism for allowing a user to select multiple items from a list.
In Creating Paired Listbox Controls, Part 1, I looked at the basic implementation techniques for paired Listbox controls that update their data source immediately. This month, I’m going to look at the (only slightly) more complicated process of deferring updates until the user explicitly commits them. The same technique allows the user to restore the Listbox controls to their initial state.
Obviously, since I’m only expanding on the functionality of the sample forms from last month, there’s a lot of overlap in the code between last month and this month. Where this month’s sample duplicates last month’s, I’ll include the sample code without much discussion.
I showed in Part 1 that your implementation method depends upon the source of the data being displayed, and that there are three choices: The data for both Listbox controls can come from a single table or from two different tables, or can be created and manipulated in memory and never stored to the database at all. The same options are available when you’re requiring users to explicitly commit the changes they make.
When you’re working with in-memory data, there really isn’t anything to update, so moving to an explicit-commit model is simply a matter of adding the appropriate buttons to the form–do whatever you were going to do with the data but do it as a result of the user selecting a button rather than closing the form.
If you’re drawing your data from tables, you need to overcome the way that Microsoft Access handles data updates (that is, data is updated in the database and then displayed in the Listbox). You could, in theory, use a transaction: Create the transaction when the form is opened, and either commit or roll back your changes based on the button the user selects. Unfortunately, in order to make this work, you need to use a completely unbound form, and it’s been my experience that this approach requires more code than handling the data updates manually.
The simplest way that I’ve found to accomplish this is to use essentially the same technique for data-bound lists as for in-memory data: Store the data in a collection. The sample in this month’s Download uses two collections, collSource and collDestination, for the source and destination Listbox controls. Since the collections are referenced by multiple procedures within the form’s code-behind module, both are declared at the module level.
Sourcing the Listboxes: One table
Microsoft Access allows the RowSourceType property of the Listbox control to be set to the name of a user-defined function (minus the ending parentheses: myFunction, not myFunction()). When using a function to provide the data, you should leave the RowSource property blank.
All Listbox data functions must have the same signature. They receive five arguments:
- fld–The Listbox control itself.
- id–A variant used to uniquely identify the control.
- row and col–The position of the value to be returned.
- code–The action the function should perform.
The function must return a variant. In addition to having the same signature, the majority of Listbox data functions also have the same structure, a case statement based on the code variable. Here’s the procedure:
Function DisplayLBData(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 LoadData End If hasBeenLoaded = True varReturn = True Case acLBOpen varReturn = Timer Case acLBGetRowCount If fld.Name = "lbSource" Then varReturn = collSource.Count Else varReturn = collDestination.Count End If Case acLBGetColumnCount varReturn = 1 Case acLBGetColumnWidth varReturn = -1 Case acLBGetValue If fld.Name = "lbSource" Then varReturn = collSource.Item(row + 1) Else varReturn = collDestination.Item(row + 1) End If Case acLBGetFormat varReturn = -1 Case acLBEnd varReturn = Null Case acLBClose varReturn = Null End Select DisplayLBData = varReturn End Function
I discussed the structure of a Listbox data function in last month’s article, but it’s also explained in the Access online Help (search for “RowSourceType Property (User-Defined Function)”), so I won’t go through it in detail here.
The DisplayLBData Listbox data function differs slightly from the version used last month (and the sample in online Help), in that this single procedure handles the data for both of the Listbox controls. The function is able to do that by checking the name of the control passed in the fld parameter. Online Help suggests that you can use the id variable to perform this test, and you can… but that requires manipulating the id field, and I find the control name rather more self-documenting (it’s easier for me to remember which Listbox is called “lbSource” than which Listbox has the id “1”).
In addition to the two collection variables I’ve already discussed, the procedure uses a module-level Boolean, hasBeenLoaded, as a simple flag to determine whether the collections have been initialized. When the Listbox data function receives a value of acLBInitialize, it checks the hasBeenLoaded flag, and if that’s false, it calls the LoadData procedure.
The LoadData procedure loads the initial data from the data table(s) to the collections. The structure of the procedure depends, of course, on the structure of the underlying data. For a one-table schema, the only thing that’s required is a display field (called Description in the example) and a field to indicate selection. In the example, this is a Boolean field named InSelectedList, but you could use any table structure that suited your application.
The next block of code shows the LoadData procedure for this schema. The first two lines declare a string value and set it to the first part of the selection string to be used against the source table. This isn’t strictly necessary (you could include the entire string in the call to the OpenRecordset method), but it increases readability slightly:
Sub LoadData() Dim theSQL As String theSQL = "SELECT Description FROM BooleanList WHERE " Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset(theSQL & _ "NOT InSelectedList") Do While Not rs.EOF collSource.Add (rs!Description) rs.MoveNext Loop Set rs = CurrentDb.OpenRecordset(theSQL & _ "InSelectedList") Do While Not rs.EOF collDestination.Add (rs!Description) rs.MoveNext Loop rs.Close End Sub
The third and fourth lines open a Recordset, rs, that holds the records where InSelectedList is set to False. These are the unselected records, and the Do…While loop adds each one to the collSource collection (the collection that fills the source Listbox control). One of the drawbacks of using collections rather than arrays is that collections don’t expose an equivalent to the GetRows method of the Recordset, so the items must be moved from the Recordset to the Listbox in a loop. The remainder of the procedure duplicates the process of opening the Recordset and loading the records into the collDestination collection, this time selecting records where InSelectedList is equal to True.
Sourcing the Listboxes: Two tables
The process for loading the data from two tables is similar, as shown in the next code block. In this case, the sample tables have only a single field, Description, as the presence or absence of a record in one of the tables indicates whether the record is selected or unselected:
Sub LoadData() Dim theSQL As String theSQL = "SELECT Description FROM " Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset(theSQL & _ "SourceTable") Do While Not rs.EOF collSource.Add (rs!Description) rs.MoveNext Loop Set rs = CurrentDb.OpenRecordset(theSQL & _ "DestinationTable") Do While Not rs.EOF collDestination.Add (rs!Description) rs.MoveNext Loop rs.Close End Sub
The only significant difference between the one-table and two-table procedures is in the OpenRecordset statements. The SQL statements in the one-table solution use selection criteria; in the two-table solution the SQL statements load records from two different tables. In a production system your criteria will almost certainly be more complex, but the same basic principle will apply.
Once the data has been loaded into the collections, the next step is to implement the Click event handlers for the various buttons. The next two routines show the event handlers for the Select and Select All buttons. These procedures are identical in both the one-table and two-table implementations, and are, in fact, identical to the in-memory implementation I discussed last month.
Here’s the solution to move just one item from the source to the destination Listbox:
Private Sub btnSelect_Click() 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 End Sub
The procedures move the selected items from one collection to the other using the Add and Remove methods. The only tricky bit is the apparent bug in the Listbox control that returns a Null value the first time the ItemData collection is referenced using an enumerator. The otherwise unnecessary assignment of theBug to the first value in the Select handler is a workaround for this problem.
And here’s the code to move all the items from the Source to the destination Listbox:
Private Sub btnSelectAll_Click() 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 End Sub
The Deselect and DeselectAll procedures are identical to their counterparts, but move items from collDestination to collSource. Again, both the one-table and two-table versions use identical procedures.
The Click event handlers for the Reset button are identical for both the one-table and two-table solutions as well. The first two lines of this procedure set the two collection variables to new collections. This is a simple, efficient method to remove all of the items in a collection (the Collection object doesn’t support a Clear or RemoveAll method). The next line of the procedure calls the LoadData procedure to reload the values from the table, while the final two lines requery the Listbox controls, ensuring that the data display is brought up-to-date:
Private Sub btnReset_Click() Set collSource = New Collection Set collDestination = New Collection LoadData Me.lbSource.Requery Me.lbDestination.Requery End Sub
Notice that this procedure restores the values as they are in the table, not as they were when the user first opened the form. If the user makes changes, commits them, and then selects Reset, the behavior may not be what’s expected. A quick (and very unscientific) poll of clients and colleagues indicates that about half of them would have expected the data to be restored to its original state, as it was when the form first opened, even though they had clicked a button to save their changes.
Since restoring the data to its original state would require implementing a complex double-commit and confuse the other half of your client base, I don’t recommend it. You should be careful, however, to explain the situation to the users.
While the handlers for the other buttons are identical for both the one-table and two-table implementations, the Click event handlers for the commit buttons differ. Here’s the handler for the one-table implementation:
Private Sub btnCommit_Click(): Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset("BooleanList", _ dbOpenDynaset) For Each Itm In collSource rs.FindFirst ("Description = '" & Itm & "'") rs.Edit rs!InSelectedList = False rs.Update Next Itm For Each Itm In collDestination rs.FindFirst ("Description = '" & Itm & "'") rs.Edit rs!InSelectedList = True rs.Update Next Itm rs.Close End Sub
The first two lines of the procedure declare a Recordset variable and set it to the table being updated, BooleanList in the example. The first For…Each loop looks for each item in the source collection and sets the value of the InSelectedList field to False. The second For…Each loop is almost identical, but sets the InSelectedList field to True for every item in the destination collection.
This procedure is very simple, both to implement and understand, but the algorithm that I’ve used may not be the most efficient for a real-world application. In the first place, the sample procedure uses the FindFirst method. In most situations it would be more efficient to use the Seek method, but this requires building and setting an index on the appropriate field. I’ve used FindFirst because it’s slightly easier to read, and of course the sample only has a few records, so the performance hit would be indiscernible.
In the second place, the procedure updates every single record in the table, whether or not the record has actually been changed. When you’re working with small data sets, this is actually (and somewhat surprisingly) more efficient than checking the state of each record and updating only those that have changed. With very large Recordsets, you may want to try a couple of other approaches.
You could test each record before updating it, adding a read but limiting the number of writes. This can provide significant performance benefits in situations where write-locks are an issue. In a few situations, it might even be worthwhile to keep a local copy of the data and avoid the read-locks, but frankly, if you’re facing that situation, I’d use a disconnected ADO Recordset instead. (Although DAO is generally considered more efficient for working with Jet databases, ADO’s disconnected Recordset has its advantages.)
The Click event handler for a two-table commit is coming next. Unfortunately, this is a moderately tedious procedure. There isn’t any tricky, quick way of bringing the two Recordsets in line with the two collections. You must go through each item in each collection and compare it to the records in one of the tables. If necessary, you move the record by explicitly adding it to one table and deleting it from the other:
Private Sub btnCommit_Click() Dim rsSource As DAO.Recordset Dim rsDestination As DAO.Recordset Set rsSource = _ CurrentDb.OpenRecordset("SourceTable",dbOpenDynaset) Set rsDestination = CurrentDb.OpenRecordset _ ("DestinationTable", dbOpenDynaset) For Each Itm In collSource rsDestination.FindFirst ("Description = '" & _ Itm & "'") If Not rsDestination.NoMatch Then rsSource.AddNew rsSource!Description = _ rsDestination!Description rsSource.Update rsDestination.Edit rsDestination.Delete End If Next Itm For Each Itm In collDestination rsSource.FindFirst ("Description = '" & Itm & "'") If Not rsSource.NoMatch Then rsDestination.AddNew rsDestination!Description = _ rsSource!Description rsDestination.Update rsSource.Edit rsSource.Delete End If Next Itm rsSource.Close rsDestination.Close End Sub
The procedure begins by setting two Recordset variables, rsSource and rsDestination, to the source and destination tables, respectively. The first For…Each loop performs the same FindFirst method call used by the one-table version. The call is subject to the same proviso, as well–with non-trivial Recordsets, you’ll probably get better performance from a Seek.
Notice that the procedure is looping through the source collection, but searching the destination table. The point is that you take action only if the record is where it’s not supposed to be. If you looked for a match in the table matching the collection–the source table for this loop–and didn’t find it, you’d have to move it from the other table. That would require finding it in the other table, so you’d execute the search anyway.
If that isn’t confusing enough, the DAO syntax for “if the search succeeded” is “if you didn’t fail”: If Not rs.NoMatch. Yikes. I’m sure there’s some very good reason why the original designer(s) of DAO chose to use a property to indicate failure rather than success. On my less generous days, I suspect dyslexia: I am not a nice person.
Within that ugly If statement, the procedure first adds the record to the source table and then deletes it from the destination table, effectively moving the record from one table to another. The schema of the sample tables is identical, consisting of only a single field each. In a real-world application your tables would undoubtedly be more complicated, and your tables’ schemas may not match. You will, of course, need to replace the line that sets the Description field in the source table equal to the same field in the destination table.
The second For…Each loop performs the same steps for each item in the destination collection–looking for it in the source table, and moving it if found.
When to use?
As you’ve probably gathered from all this code, paired Listbox controls that defer updating data changes are slightly more resource-hungry than immediate-update control pairs. But in the majority of cases this isn’t an issue. The choice is more often one of usability and efficiency. Don’t assume that adding deferred updating to a pair of Listbox controls will add usability to the form. It may simply add an extra step to the process.
As always, you need to consider the user. How likely are your users, really, to want to quit without saving their changes or want to start over? How difficult would it be to re-create the original state of the data? If, for example, the form always opens with no items selected, then there really is little value in the ability to reset–the button would only duplicate the select all or deselect all functionality.
But there are situations in which deferred updating adds significantly to the usability of a form. This is particularly the case in multi-user situations, or when the paired Listbox controls are updating “real” data rather than, for example, configuration options. These are also most likely to be the situations in which reducing the number of trips to the data server is likely to improve performance, although that must, of course, be balanced against the resource use.