Synchronize User Selections Between Listbox Controls

You've probably seen them a million times in Wizard-setup programs--listboxes synchronized so that when you select an item from one listbox, the item disappears and reappears in the next listbox. For instance, Figure A shows a common, simplified GUI version of the listbox synchronization we're talking about. Figure B shows what happens in this setup when you select an item from the first control. The selection is no longer available in the first listbox, but appears in the second as a selected choice. This type of synchronization provides a great, user-friendly way to prevent users from selecting an item more than once.

Figure A: Typically, when you synchronize two listboxes, you do so to limit the remaining choices available to users.



Figure B: When you select an item from a synchronized listbox setup, it disappears from one control and appears in the other.



It's fairly easy to provide this behavior in Visual Basic. To be honest, the technique relies more on the SQL statements that generate each listbox's items than it does on any fancy code. However, depending on which controls you use to gather and display the data, you may encounter a few glitches along the way. In this article, we'll show you how to create the SQL statements that will facilitate listbox (or combobox) synchronization, as well as how to avoid some common hitches with the Data Environment and DataListbox components. Even though the Data Environment and DataListbox are Visual Basic 6.0 components, you can easily adapt our solution to work with just about any listbox and data source you choose.

Structuring the data sources

For the most part, synchronized listbox techniques require two database sources: one that contains all potential selections, and another that contains only the selected items. These sources can consist of tables, temporary or otherwise, recordsets, or saved queries and commands. For instance, in our example, the database contains a table that lists every product. Figure C shows the structure of the tblProducts table in SQL Server. For our purposes, we're only concerned with two fields: prod_id and prod_name. We'll display the prod_name field value in the listbox, and store the prod_id field value in the list of selected items. Figure D shows the table that holds this information. Since we now have our tables, let's create the SQL statements next.

Figure C: This table stores a complete list of all products in the database.



Figure D: This table stores the selected items.



It's all in the SQL statement

As we mentioned, the key to creating synchronized listboxes relies on building the proper SQL statements. In the listbox of potential selections, we want to display only those possibilities that the user hasn't already selected. Our SQL statement should reflect this qualification. With this in mind, we'll definitely need to join the two tables we created earlier. Figure E shows how this might appear for our example in the Query Builder (we'll build these queries step by step in a moment). In straight SQL terms, this design translates to:

SAMPLE ONE:(below)

Notice that we used a left outer join to connect the two tables on the prod_id field. In other words, we told SQL to select all records from tblProducts and only those records from tblSelections where the prod_id fields match.

Figure E: We joined both tables in the query builder.



If you look at the results of this query in the bottom pane of Figure E, you'll notice that the query listed all the products even though two of them, as seen in the select_prod_id column, are in tblSelections. Presumably this means the user already selected them. Obviously, we don't want these two products to appear in the results. The contents of the remaining fields in the select_prod_id column gives us a hint how to refine this query. Whenever the tblSelections table doesn't contain a matching product ID, SQL fills the resulting field with NULL. If we select only the records with NULL in this column, then SQL should retrieve only the products that the user hasn't already selected. Figure F shows this layout in the Query Builder, along with the results. Listing A contains the modified raw SQL.

Listing A: SQL to select products

SAMPLE TWO:(below)

Figure F: We added the IS NULL criteria to limit the returned records.



Displaying the selected choices

Creating the query that displays the selected options is a little easier. This time, in the SQL statement, we want to join the two tables together again, but we only want products in the tblSelections table and their associated product names. A right outer join provides this relationship, as seen in Listing B.

Listing B: Selecting the current selections

SAMPLE THREE: (below)

TO SEE COMPLETE ARTICLE: http://msdn.microsoft.com/library/default.asp?URL=/library/periodic/period01/ivb0155.htm