Browse All Articles > Understanding the ComboBox (and ListBox)
I often find myself explaining how a combo box works. Naturally, every book on Access has a chapter dedicated to them, and many good resources exist on the Web. One more article about them is probably redundant, but hopefully not entirely useless! However, I do not intend to cover all the basics here.
The help file installed with Access has always been quite complete, and easily accessible directly from each property. For example, if you want to know what the number in the bound column property means, please press [F1] and read that page first. I will not assume that you have read the pages of all properties, but at least that these pages are available when my explanations are lacking or not detailed enough.
In this article, I will try to explain the basic mechanics of a combo box, warn about some tricky problems one might encounter, show a few special tricks to enhance the user interface, and sketch out an overview placing each aspect of the combo into a better perspective. It serves as introduction to a series of articles covering various aspects and uses of the combo box, listed at the end.
The control combines two features. It is a drop-down list presenting a list of available choices to the user and also a text box allowing regular editing. If the user can only select something, the editing feature is still useful for the auto-complete feature allowing a record to be found by typing the first few letters. If the user can both select and enter original data, it becomes a true combo box acting simultaneously as a text box.
The property `limit to list´ determines the basic behaviour of the control. If set to yes, it's a drop-down list, if not, it's an actual combo box. However, even if `limit to list´ is selected, the event `on not in list´ can be used to do something meaningful with the entered text. Also, the interface might provide some mechanism to edit the list of available items; this is partially automated since version 2007 through the properties `allow value list edits' and `list edits edit form´, which fall outside of the scope of this article.
In many cases, the user selects something human-readable while the control in reality manages some hidden computer-readable key or code. In that case, the combo is automatically a drop-down list, as the user only sees the information meaningful to him or her. If the `on not in list´ event is used, the procedure will have to add a new code to go with the new data. In any case, the combo box acts as a translator, specifically between the data model (using the hidden key) and the user model or business model (where the visible text is meaningful).
There are five ways to create the rows for a combo, or to “populate” the list, as it is often called.
A value list is a static list of values, separated by semi-colons. It can be as simple as “Mr;Mrs;Miss;Ms” or “tall;medium;small”. There are no specific field and row separators, so a multi-column row source like “1;red;2;green;3;blue”, meant to be displayed in two columns, will generate six rows until the number of columns has been entered. When using a value list, the combo box offers two methods to add and to remove items, which are sometimes marginally easier to use than simple string manipulation of the row source. Note that the first value or set of values is used as headers when `column heads´ are requested.
A table or query is the most frequently used source. Simple lookup tables are often used directly, simple queries can be used to adjust the sort order or to select only meaningful columns, advanced queries like UNION queries can be used for example to add special options like “all” to the available choices. A query is executed in the name space of the form. This means that all other objects and properties of the form can be used directly in the query for example as filter criteria. This is explained in separate articles.
The field list setting also requires a table or query, but instead of returning rows, the combo will simply display the field names, in one column. This is useful for building wizards (e.g. “please select the fields that might contain duplicates”), but usually not for regular user interaction. A user oriented interface tool would not display the field names (e.g. CategName) but its caption (“Category Name”) and perhaps it's description.
Trough programming, the combo's Recordset can be set directly to an existing recordset. This overrides the setting in the `row source´. Both DAO and ADO recordsets can be used. The latter allows for example to create a disconnected recordset, which is much more versatile than a value list or an array. Note that the combo box automatically creates a clone of the recordset, so that changes to the original recordset are not reflected in the combo box.
Finally, a user-defined function can provide all the information needed by the combo box: number of rows to display, column widths, actual data... The function needs to follow a certain format, explained in the help page “RowSourceType Property (User-Defined Function)”, linked from the page “RowSourceType” (press [F1] from that line in the property sheet). Any serious Access developer should create at least one such function, as it is the most versatile way to populate a combo box from any source, albeit not necessarily the most efficient. As a matter of fact, the first three methods above can be simulated by a row source function quite easily.
In all cases, the combo has something to show in its drop-down section. The method used to populate the list has no impact on the mechanics of the control. For all methods, it is possible to add rows not present in the data source. One frequent request is to add the option “all”, which is the title of one of articles listed at the end.
There is no mystery about simple single-column combos. The selected text becomes the value of the combo, and if the value matches an item, that row is “selected”.
Where there are more than one column, the first question is: “which one will be displayed?” The edit box portion shows one value; the other columns are visible in the drop-down section. The answer is simple: the column displayed in the edit box is the fist having a width. Columns can be made invisible simply by setting a width of zero. With a width of almost zero, a column is still technically visible, but not really in the drop-down section. This fact is put to use in one of the tricks below.
The second question is: “which one will be stored as value?” This is determined by the `bound column´ setting. So the internal value might be different from what the user perceives as the value of a combo box. This makes sense in various cases and most notable when the internal key to some data isn't user friendly or not meant to be displayed. The value can even be no column at all (bound column = 0), when the row number is the best way to identify a row.
In other words, the main function of a combo box is often to translate to and from some internal coding mechanism serving as key. Incidentally this is also where the problems start...
In summary:
You might have noticed that, in a combo or list box, columns are always left-aligned. Some columns would be much more readable if they were right-aligned, especially on list boxes, and this question pops up from time to time on EE. The simple solution is to choose a mono-spaced font and to align using spaces; there is no property to centre or right align columns in the list.
The fact that combos know only text is is probably the single most important thing to remember about combo boxes. Although the value might have a numeric type, the mechanics of a combo box are based on text only. To many readers, I suspect that 7 and "7" are the same (and they are in this article), but in programming the unquoted 7 represents the number seven, for example as the byte 00000111, while "7" is the one-character string composed by the digit seven, itself encoded as 00110111. At this stage, it's trivial — because Access will convert automatically between these representations — but things get complicated with formatting.
Imagine a combo used to select a rate expressed in percent. The value might be 0.125, formatted as “12.5%” or “12.50%” for display. (Incidentally, 3:00 is another representation of this value formatted as time, and so is 1/8 formatted as fraction.) The combo will attempt a match between the formatted value and the bound column. In other words, “12.5%” will not match “0.125” or “12.50%”.
Things can get even worse with dates. If a combo box displays a list of dates, they are necessarily formatted to be readable. The programmer might have carefully chosen "mm/dd/yyyy" as formatting string to include the full year. If the combo itself is simply formatted as "short date" and if the user happens to prefer a European date format, the following will happen:
To understand this behaviour better, let's look at the succession of events in a combo.
The rows of the combo box are generated only when needed. Often, this happens only when the user clicks on the drop-down arrow (or uses a keyboard shortcut). The construction of the list itself can generate a burst of function calls (for a list-fill function) and any errors in the row source will only become apparent at that point.
Once the list is open, the user can select an item using the keyboard or the mouse. If the underlying field isn't a text field, or if the control has a number formatting, there will be two data conversions.
![ComboBox mechanics]()
1) The selected text from the list is converted to a number. Changing the value might trigger the `before update event´, and the number is perhaps validated (against a validation rule). Then there might be an `after update´ event. However, even if the data isn't validated, the first conversion took place.
2) In the figure, the value 0.08 is formatted as percent with one decimal. The text property of the combo is thus the result of a formatting (which is another kind of conversion), yielding 8.0%. Strangely enough, this text isn't an item in the list!
This situation doesn't trigger the `on not in list´ event. Although the final text displayed isn't in the list, the user did select an item from the list...
Another example
![immediate reselection of the key]()
This combo uses the first column, the ISO country code, as bound column. However, it isn't unique, because country names appear in more than one language. Now imagine that this column is hidden.
1) The user select “Germany”, and the bound column's value, “DE” is stored in the underlying field (or simply in the unbound control's value). In a way, it is a conversion, from one column to another.
2) The combo box then immediately searches for a match of the value, “DE”, in the bound column. It finds the very first row and happily shows “Allemagne”.
This can be very confusing. One could use this behaviour as a feature, as some sort of automatic translation from English country names to French country names, but that requires a specific sort order (the combo always selects the first match).
If the bound column is set to 0, then the index position is stored as value. This way, when the user selects “Germany”, that row will remain selected. However, this also means that the stored value will be 2 (the items are numbered from zero in this example) and not “DE”.
A combo can be used effectively to filter data, for example in a report. The first article (in fact a short series) explains how to do that. A correlated question is treated in the second.
When a combo box is used to filter another, this is called “cascading” or simply synchronized combos.
The natural continuation from the last section would be to present the properties and methods of combo boxes available from Visual Basic. Instead, it became a rather technical distinct article.
Finally, two little tricks have also been published as separate articles.
Combo boxes are an vital part of any GUI designed in Access (and not only in Access). Understanding their mechanics is essential to have them behave the way you intend, and to enhance the usability of your forms.
This article was in the works for quite some time, until I realised that I was trying to say too much and too little in every section. Too much because I wanted to include all sorts of tricks and special cases, too little because I tried to handle each in only a paragraph or two. As you can see, I ended up creating several smaller articles. This created some redundancy — each article should be understandable on its own — but it makes the material easier to search, and, well... more digestible!
Happy coding!
Markus G Fischer
(°v°)
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
This article is part of the EE-book unrestricted Access.
The help file installed with Access has always been quite complete, and easily accessible directly from each property. For example, if you want to know what the number in the bound column property means, please press [F1] and read that page first. I will not assume that you have read the pages of all properties, but at least that these pages are available when my explanations are lacking or not detailed enough.
In this article, I will try to explain the basic mechanics of a combo box, warn about some tricky problems one might encounter, show a few special tricks to enhance the user interface, and sketch out an overview placing each aspect of the combo into a better perspective. It serves as introduction to a series of articles covering various aspects and uses of the combo box, listed at the end.
Why is it called a ComboBox?
The control combines two features. It is a drop-down list presenting a list of available choices to the user and also a text box allowing regular editing. If the user can only select something, the editing feature is still useful for the auto-complete feature allowing a record to be found by typing the first few letters. If the user can both select and enter original data, it becomes a true combo box acting simultaneously as a text box.
The property `limit to list´ determines the basic behaviour of the control. If set to yes, it's a drop-down list, if not, it's an actual combo box. However, even if `limit to list´ is selected, the event `on not in list´ can be used to do something meaningful with the entered text. Also, the interface might provide some mechanism to edit the list of available items; this is partially automated since version 2007 through the properties `allow value list edits' and `list edits edit form´, which fall outside of the scope of this article.
In many cases, the user selects something human-readable while the control in reality manages some hidden computer-readable key or code. In that case, the combo is automatically a drop-down list, as the user only sees the information meaningful to him or her. If the `on not in list´ event is used, the procedure will have to add a new code to go with the new data. In any case, the combo box acts as a translator, specifically between the data model (using the hidden key) and the user model or business model (where the visible text is meaningful).
The Row Source
There are five ways to create the rows for a combo, or to “populate” the list, as it is often called.
A value list is a static list of values, separated by semi-colons. It can be as simple as “Mr;Mrs;Miss;Ms” or “tall;medium;small”. There are no specific field and row separators, so a multi-column row source like “1;red;2;green;3;blue”, meant to be displayed in two columns, will generate six rows until the number of columns has been entered. When using a value list, the combo box offers two methods to add and to remove items, which are sometimes marginally easier to use than simple string manipulation of the row source. Note that the first value or set of values is used as headers when `column heads´ are requested.
A table or query is the most frequently used source. Simple lookup tables are often used directly, simple queries can be used to adjust the sort order or to select only meaningful columns, advanced queries like UNION queries can be used for example to add special options like “all” to the available choices. A query is executed in the name space of the form. This means that all other objects and properties of the form can be used directly in the query for example as filter criteria. This is explained in separate articles.
The field list setting also requires a table or query, but instead of returning rows, the combo will simply display the field names, in one column. This is useful for building wizards (e.g. “please select the fields that might contain duplicates”), but usually not for regular user interaction. A user oriented interface tool would not display the field names (e.g. CategName) but its caption (“Category Name”) and perhaps it's description.
Trough programming, the combo's Recordset can be set directly to an existing recordset. This overrides the setting in the `row source´. Both DAO and ADO recordsets can be used. The latter allows for example to create a disconnected recordset, which is much more versatile than a value list or an array. Note that the combo box automatically creates a clone of the recordset, so that changes to the original recordset are not reflected in the combo box.
Finally, a user-defined function can provide all the information needed by the combo box: number of rows to display, column widths, actual data... The function needs to follow a certain format, explained in the help page “RowSourceType Property (User-Defined Function)”, linked from the page “RowSourceType” (press [F1] from that line in the property sheet). Any serious Access developer should create at least one such function, as it is the most versatile way to populate a combo box from any source, albeit not necessarily the most efficient. As a matter of fact, the first three methods above can be simulated by a row source function quite easily.
In all cases, the combo has something to show in its drop-down section. The method used to populate the list has no impact on the mechanics of the control. For all methods, it is possible to add rows not present in the data source. One frequent request is to add the option “all”, which is the title of one of articles listed at the end.
ComboBox Columns
There is no mystery about simple single-column combos. The selected text becomes the value of the combo, and if the value matches an item, that row is “selected”.
Where there are more than one column, the first question is: “which one will be displayed?” The edit box portion shows one value; the other columns are visible in the drop-down section. The answer is simple: the column displayed in the edit box is the fist having a width. Columns can be made invisible simply by setting a width of zero. With a width of almost zero, a column is still technically visible, but not really in the drop-down section. This fact is put to use in one of the tricks below.
The second question is: “which one will be stored as value?” This is determined by the `bound column´ setting. So the internal value might be different from what the user perceives as the value of a combo box. This makes sense in various cases and most notable when the internal key to some data isn't user friendly or not meant to be displayed. The value can even be no column at all (bound column = 0), when the row number is the best way to identify a row.
In other words, the main function of a combo box is often to translate to and from some internal coding mechanism serving as key. Incidentally this is also where the problems start...
In summary:
the value of a combo is linked to the bound column, which can be hidden;
the text in the edit box is linked to the first visible column (including almost invisible columns);
Access selects the row by matching the value against the bound column;
the user selects the row my selecting in the visible column(s);
if these two columns are different, this implies a two-way translation.
Combos know only text
You might have noticed that, in a combo or list box, columns are always left-aligned. Some columns would be much more readable if they were right-aligned, especially on list boxes, and this question pops up from time to time on EE. The simple solution is to choose a mono-spaced font and to align using spaces; there is no property to centre or right align columns in the list.
The fact that combos know only text is is probably the single most important thing to remember about combo boxes. Although the value might have a numeric type, the mechanics of a combo box are based on text only. To many readers, I suspect that 7 and "7" are the same (and they are in this article), but in programming the unquoted 7 represents the number seven, for example as the byte 00000111, while "7" is the one-character string composed by the digit seven, itself encoded as 00110111. At this stage, it's trivial — because Access will convert automatically between these representations — but things get complicated with formatting.
Imagine a combo used to select a rate expressed in percent. The value might be 0.125, formatted as “12.5%” or “12.50%” for display. (Incidentally, 3:00 is another representation of this value formatted as time, and so is 1/8 formatted as fraction.) The combo will attempt a match between the formatted value and the bound column. In other words, “12.5%” will not match “0.125” or “12.50%”.
Things can get even worse with dates. If a combo box displays a list of dates, they are necessarily formatted to be readable. The programmer might have carefully chosen "mm/dd/yyyy" as formatting string to include the full year. If the combo itself is simply formatted as "short date" and if the user happens to prefer a European date format, the following will happen:
the date separators is silently changed to “.”;
the user selects 06.02.2011 — intended as June 2nd;
the value is interpreted as DMY, i.e. February 6th;
which is reformatted as "short date" to 06.02.11;
this value is not found in the bound column;
the row is unselected.
When dealing with any numeric value that needs to be formatted to become readable (this includes dates), the best solution is often to use a hidden bound column. The row source should use CStr() to transform the value to string, which will be compatible with the implicit conversion. A second column is used to display the formatted version. If values not in the list are allowed, this doesn't work. For dates, the best bet it to use “general date” as format...
To understand this behaviour better, let's look at the succession of events in a combo.
ComboBox mechanics
The rows of the combo box are generated only when needed. Often, this happens only when the user clicks on the drop-down arrow (or uses a keyboard shortcut). The construction of the list itself can generate a burst of function calls (for a list-fill function) and any errors in the row source will only become apparent at that point.
Once the list is open, the user can select an item using the keyboard or the mouse. If the underlying field isn't a text field, or if the control has a number formatting, there will be two data conversions.
1) The selected text from the list is converted to a number. Changing the value might trigger the `before update event´, and the number is perhaps validated (against a validation rule). Then there might be an `after update´ event. However, even if the data isn't validated, the first conversion took place.
2) In the figure, the value 0.08 is formatted as percent with one decimal. The text property of the combo is thus the result of a formatting (which is another kind of conversion), yielding 8.0%. Strangely enough, this text isn't an item in the list!
This situation doesn't trigger the `on not in list´ event. Although the final text displayed isn't in the list, the user did select an item from the list...
Another example
This combo uses the first column, the ISO country code, as bound column. However, it isn't unique, because country names appear in more than one language. Now imagine that this column is hidden.
1) The user select “Germany”, and the bound column's value, “DE” is stored in the underlying field (or simply in the unbound control's value). In a way, it is a conversion, from one column to another.
2) The combo box then immediately searches for a match of the value, “DE”, in the bound column. It finds the very first row and happily shows “Allemagne”.
This can be very confusing. One could use this behaviour as a feature, as some sort of automatic translation from English country names to French country names, but that requires a specific sort order (the combo always selects the first match).
If the bound column is set to 0, then the index position is stored as value. This way, when the user selects “Germany”, that row will remain selected. However, this also means that the stored value will be 2 (the items are numbered from zero in this example) and not “DE”.
More ComboBox Articles
A combo can be used effectively to filter data, for example in a report. The first article (in fact a short series) explains how to do that. A correlated question is treated in the second.
When a combo box is used to filter another, this is called “cascading” or simply synchronized combos.
The natural continuation from the last section would be to present the properties and methods of combo boxes available from Visual Basic. Instead, it became a rather technical distinct article.
Finally, two little tricks have also been published as separate articles.
Conclusion
Combo boxes are an vital part of any GUI designed in Access (and not only in Access). Understanding their mechanics is essential to have them behave the way you intend, and to enhance the usability of your forms.
This article was in the works for quite some time, until I realised that I was trying to say too much and too little in every section. Too much because I wanted to include all sorts of tricks and special cases, too little because I tried to handle each in only a paragraph or two. As you can see, I ended up creating several smaller articles. This created some redundancy — each article should be understandable on its own — but it makes the material easier to search, and, well... more digestible!
Happy coding!
Markus G Fischer
(°v°)
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
This article is part of the EE-book unrestricted Access.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented: