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?
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.
Here is the SQL statement:
Select "All" as a, 3-2 as SortNumber from tblFruits Union Select "---" as a, 3-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, 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.
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:
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.