O'Reilly Book Excerpts: Access Hacks
Populate and Sort Lists with Flair
Author's note: Populating a listbox is standard Access fare. But what if you need to populate a listbox with values from more than one source, and sort the whole thing as if it were from a single source? Better yet, imagine a listbox that always displays the most popular items at the top. Users will love that! Read on, and learn how to hack this into your application.
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.
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
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;
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
Unionquery 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