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 sources—tblFruits and tblVegetables—are 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.

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, 3-2 as SortNumber

The code snippet does this by giving the word All the lowest value of SortNumber—in this case, 3-2. To be clear, neither the word All nor the value 3-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, 3-2 as SortNumber from tblFruits
Select "---" as a, 3-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.

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 value—in 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:

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.