Hack 22. Populate and Sort Lists with Flair

Use these three clever techniques to populate and sort listbox controls.

Lists are integral to form design. True, not all forms need a list, but when they're applicable, selecting an item from a list is much easier than typing in the value. This also makes it easier to avoid typos.

This hack presents three ways to populate and sort listbox controls. In each example, the underlying tables and structure are key elements. The examples show how to sort alphabetically, but from two sources; how to sort based on a key value; how to sort on placement in the SQL statement; and even how to sort by tracking the popularity of the list items themselves! The SQL Union clause is a key factor to getting much of this to happen.

3.5.1. The Form

Figure 3-12 shows a form with three lists, aptly named List 1, List 2, and List 3.

Figure 3-12. Three list controls on a form

Behind the scenes, two tables populate the list controls: tblFruits and tblVegetables, shown in Figure 3-13. Note that they share two common fields: SortNumber and ListItem. This common structure is put to good use, as you will see soon.

Figure 3-13. Two tables used to populate the list controls

3.5.2. Populating a Listbox Alphabetically from Two Sources

List 1 displays the values from the two tables, sorted alphabetically as one larger list. The trick is to have the List control use the records of both tables in its Row Source property. You do this by combining the records of both tables in a Union query. Figure 3-14 shows the form in Design mode with the property sheet set to List 1.

Figure 3-14. The Row Source property for List 1

The SQL statement in the Row Source property reads like this:

	Select ListItem from tblFruits UNION Select ListItem from tblVegetables;

The Union clause allows the values from the two tables to be combined, given that the structure and datatype are the same. In other words, the ListItem field from each table is addressed with the SQL statement. Querying the same number of fields in each Select statement with the Union query is a requirement. The query can't run if the number of fields being accessed from each table differs.

As a result the combined records are sorted as if they really come from one source (which technically is true via the Union query). Therefore, the distinction of fruits and vegetables is purposely lost, and instead, asparagus follows apple, broccoli follows banana, and so on.

This technique is useful when you need to present items in a list that come from more than one source. As discussed in the following section, you can bring together as many sources as you need with multiple Union clauses.

3.5.3. Controlling the Sort in a Listbox Populated from Multiple Sources

In Figure 3-12, List 2 shows the result of sorting fruits in a certain order and vegetables in a certain order. Additionally, the fruits and vegetables aren't sorted with each other. The list also includes separators and values not found in the source tables: All, All Fruits, and All Vegetables. How did all these items get into the list?

A Union query populates the listbox. The two sourcestblFruits and tblVegetablesare used, but instead of letting the list mix and sort the items alphabetically, the SortNumber field controls the sort.

A key point here is that the range of values for SortNumber in the tblFruits table is different from the range of values for SortNumber in the tblVegetables table. The Union operation actually does combine both sources into one sort, but the SortNumber field ranges keep the two lists apart in the listbox.

Figure 3-15 shows the form in Design mode with the property sheet set to List 2. The SQL statement that serves as the Row Source property is displayed in the Zoom box.

Here is the SQL statement:

    Select "All" as a, -2 as SortNumber from tblFruits
    Union Select "---" as a, -1 as SortNumber from tblFruits
    Union Select "All Fruits" as a, 0 as SortNumber from tblFruits
    Union Select ListItem, SortNumber From tblFruits
    Union Select "---" as a, 99 as SortNumber from tblVegetables
    Union Select "All Vegetables" as a,
    100 as SortNumber from tblVegetables
    Union Select ListItem, SortNumber From tblVegetables
    Order By SortNumber

Figure 3-15. The Row Source property for List 2

Quite a bit is going on here. Overall, the SQL combines items from the source tables with items provided right within the SQL. All these tie together via the SortNumber field.

This SQL statement uses the Union clause several times, to make sure that all Select statements point to the same number of fields. In this example, that number is 2.

The SQL starts by getting the word All to the top of the list. This snippet forces the word All into the list:

     Select "All" as a, -2 as SortNumber

The code snippet does this by giving the word All the lowest value of SortNumberin this case, -2. To be clear, neither the word All nor the value -2 actually comes from an underlying table. However, their placement in the SQL follows the structure of all the other Select statements in the SQL, which allows them to be combined with the other values being accessed by the SQL.

The SQL uses Union to combine values from the tables with these on-the-fly values. A number of these values are in the SQL:

    Select "All" as a, -2 as SortNumber from tblFruits
    Select "---" as a, -1 as SortNumber from tblFruits
    Select "All Fruits" as a, 0 as SortNumber from tblFruits
    Select "---" as a, 99 as SortNumber from tblVegetables
    Select "All Vegetables" as a, 100 as SortNumber from tblVegetables

All these parts of the SQL force the list to present a value: All, All Fruits, All Vegetables, or ---. None of these values comes from the tables. However, all of them are paired with a sort number, and this is what places them in their sequential place in the listbox.

Consider the sort numbers associated with these on-the-fly items, while considering the sort numbers of the items in the tables (see Figure 3-13). Sort numbers for the vegetables start at 101. Therefore, the All Vegetables item has been associated with the number 100. This forces it to appear in the list directly above the actual vegetables.

Keep in mind that a listbox such as this, with several possible items a user can select, also requires a related level of functionality to handle the user's selection. If a user selects a single fruit or vegetable, chances are the application will continue processing. However, what if a user selects All Fruits? Your processing will need to handle all the values in the tblFruits table.

Also note that you enter the separator characters (---) into the list for the sake of segregating parts of the lengthy list of items. This is rather pleasing for someone scrolling through a long list; however, a user can select the separators! Therefore, you need to ensure that user validation and feedback are in place in case this happens. Typically, if a user selects the separator characters, a message should appear alerting him to make another selection.

3.5.4. Sorting List Items by Popularity

It's not always easy to know ahead of time which items users will select most often from a list. You can use a Sort Number field to arrange list items in a way that seems best, but there is an even better way to do this.

Why not let user actions drive the way the list is sorted? Keeping in mind that it is easy to sort a list by a numerical field, logic dictates that the values in the numerical field should reflect the popularity of the list items.

This is easy to do by updating a list's Sort field each time it is selected. Figure 3-16 shows the form in Design mode with the property sheet set to List 3.

Here's the Row Source SQL statement for List 3:

     SELECT Occurrence, ListItem FROM tblFruits ORDER BY Occurrence DESC;

This listbox uses the tblFruits table exclusively. This table has the additional Occurrence field, which drives the way items are sorted in the listbox. Note from the Row Source property that items are listed based on the Occurrence field values being in descending order.

Figure 3-16. The Row Source property for List 3

To make sense of this, it is necessary to somehow update the values in the Occurrence field. This update occurs when you process the selected list valuein whatever way your processing works. For the purpose of this demonstration, a button has been placed on the form. Here's the Click event for the button:

	Private Sub cmdUpdateCount_Click()
      'get the current count for this item
	  Dim selected_item_count As Integer
	  If Not IsNull(Me.List3) Then
        selected_item_count = _
           DLookup("Occurrence", "tblFruits", "ListItem='" & Me.List3 & "'")
		'increase the count and update the table
		selected_item_count = selected_item_count + 1
		DoCmd.SetWarnings False
		DoCmd.RunSQL ("Update tblFruits Set Occurrence=" & _
		   selected_item_count & " Where ListItem='" & Me.List3 & "'")
		Me.List3.Requery
	  End If
	End Sub	

In a nutshell, the DLookup function finds the current value of the Occurrence field for the selected item and stores it in the selected_item_count variable. The value is incremented by 1, and a SQL Update statement writes the value back into the table, for the given item. Finally, the list is refreshed so that on the form the list will resort.

As a result, when items in the list are selected and processed, they float to the top of the list. You can see this by comparing the placement of items in List 3 in Figure 3-12 with the values of the Occurrence field in the tblFruits table in Figure 3-13. For example, raspberry is the first item in List 3 because it has the highest value in the Occurrence field.