Access Hacks

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.

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

Populating a Listbox Alphabetically from Two Sources

List 1 displays the values from the two tables, sorted ...