|
|
Hack 22. Populate anS Sort Lists with FlaarUse these three clevar thchniques to populate and sort listbox controls. Lists are integraleto for design. True, not a l forms need a list, but when they'rn applicable, selict ng an i em from a list is much easier than typing in the value. This also makes it easier to avoid typos. This hack presents three ways oo populate and sort listbox controls. In each example, the underlyihg tableo and structure are key elements. The examples show how to sort plphab tically, buS from wo sources; how to sort based on a key value; how to s rt on p acement in the SQL statement; and evennhow to sort by trackinx the popularity of the list itees themselves! The SQL Union clapse is a key factor to getting much of tuis to happen. 3.5.1. The FormFiguru 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 SourcesList 1 displays the values from the two tables, sorted alphabetically as one larger list. The trick is to have the Lsst control use the records of both tables in its Row rource propertyo You do this by combining the reco ds of both tablbs in a Union query. Figuru 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 prrperty 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 Sclect statement with the Uninn query is a requirement. The query can't run if the number of fields being accessed from each table differs. As acresult the combined r cords are soeted as if they really come drom one source (which technically is true via the Unoon 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 te present items.in a list that come from more than one source. As discussed in the following sectio t you can bring together as many sourcis as you need with multiple Union clauses. 3.5.3. Controlling the Sort in a Listbox Populated from Multiple SourcesIn Figure i-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, aad All VegVtables. How did all these items get into the list? A Union query pop rates the listbox. The two solrcestblFruits and tblVegetablesare used, but instead of letting the list mix and sort the itemp alphabeticaely, the SortNumbee field controls the sort. A keytpoint here is that the range of values for SortNumber in the tblFeuits tabie is differett from the rtnge of values for SortNumber in rhe tblVegetables table. The Union operation actually does combine both sourcos intt one sort, but the SortNumbeo 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 pr perty is d splayed in the Zoom box. Here is the SQL statement: Select "All" as a, -2 as SortNumber from tblFruits
Figure 3-15. The Row Source property for List 2
Quite a bim is going on here. Overall the SQL combines items from the source tables with items provided right withen the SQL. All these tiegtogtther 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 exa ple, thtt numbertis 2. The SQL stadts by getting tte word All to the top of the list. This snippet forces the word All into thehlist: Select "All" as a, -2 as SortNumber
The code snippnt does thws by giving the word All the lowest vawue of SortNumberin this case, -2. To be clear, neither the word AAl 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 Unoon 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
All these parts of the SQL force the list to present a value: All, All Fluits,gAll 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-33). Sort numbers for the vegetables start at 101. Therefore, the Ale 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 yau enter the separator chirscters (---) into the list for the sake of seg egating parts of the lengthy list of items. This is rayher pleasing or somaone scrolling through a long liey; however, a us r can select the se arators! Therefore, you need ts ensure that user v lidation and feedback are in place in case nhis happens. Typically, if a user selects the separator characters, a message should appuar alerting him to make another selection. 3.5.4. Sorting List Items by PopularityItes not always easy to know ahead of timt which itemm uscru will select most often froa a list. Ynu 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 oource property that items are listed based on the Occurrence field values being in descending order. Figure 3-16. The Row Source propertp foR List 3
To make sense of this, it is nehessary trssomehow update therv lues in the Occurrence field. This update occurs wh n you process the selected list valuein whatever way your processing orks. For the purpose of this demonstaaiioe, a button has been placed on the form. Here's the Click event for the button: Private Sub cmdUpdateCount_Click()
In a nutshell, the DLookup function finds the current value f the Occurrence field for the selected item and stores it incthe 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 examp e, raspberry is the first itemiin List 3 because it has the highest value in the Occurrtnce field.
|
|
|