It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather which page to read?
At the time, there was no “intellisense”, so it could take me a couple of minutes to find for example “.ItemData()”, even if I had used it before.
Another more obscure problem was that I could never trust myself to use the right indexing in the right property. I would type “column(2)” with hesitation and an urge to check if shouldn't be 1 or 3 instead. I learned the cause much later. Row indices are zero-based or one-based depending on the property used; column indices are also inconsistent when column headers are involved. Before I became full aware of this fact, I had simply learned intuitively that, whatever indexing I used, it was wrong every other time.
In this article, I dissect a combo box and show when each indexing is used, and for what reasons. Properties are presented by theme, making them easier to remember. They are then used to perform some typical tasks, in a “how to” section.
Anatomy of a List
In the text below a list
is simply the full content of a combo or list box, including any hidden columns. I will use list box
if I want to designate specifically that type of control.
The innocent looking combo box at the left can contain an entire table
of data. Only the second, most readable column is visible to the user, two other columns are hidden.
The bound column is selected from the blue index, 1 by default (0 means that the orange list index is used to select a row). When reading a column value from the data, the green index is used... In the lower combo, one row is used as headers, and this displaces the red item data index by one. The list count changes as well: from six in the upper combo to seven in the lower one.
The text in the edit portion is matched against (or picked from) the first visible column, while the value is taken from the bound column, “B” in this case. As soon as the selection is validated, the combo will search again for the value and find “B” for “black”, which will be selected. This is very confusing — it's a “data bug” — so the bound column should always uniquely identify the rows. Note that this doesn't happen immediately for list boxes, but only once it's recalculated (e.g. by pressing F9).
The following properties are not always synchronized with the data table.
.Value (variant) — The value is stored in the control itself or in the underlying field if the combo is bound (data bound, that is, meaning with a control source). It is closely related to the bound column: making a selection writes the data from that column into the .Value property, and the .Value is searched in that column to dermine the selected row.
.Text (string) — This property is the current string of characters in the edit section of the combo. It is related to the first visible column, and the first visible column is searched to find a matching row after user input. The .Text can only be different while editing or if the first visible column is also the bound column.
.ListIndex (integer) — This is the orange index number. When no row is selected, the list index is -1. This property is read/only for combo boxes, but read/write for list boxes.
It is important to understand that the value might not result in the selection of a row. Even if the row source is changed, the value in the combo box or list box will not be cleared. Setting the value to Null reliably clears the text of a combo box (and sets the list index to -1).
The meta-data of the table are the relevant properties set at design time, and the current number of rows.
.ColumnCount (integer) — The value set at design time. It may or may not correspond to the actual number of columns in the row source, except for the `value list´ row source type. If more columns exist, they are still available in older versions of Access; since Access 2007, only one additional column is accessible through code.
.ColumnHeads (boolean) — True if the first row of data is used as column headers (this row is added automatically for the table/query row source type).
.ListCount (integer) — Number of rows in the table, including the header row. A query returning no data will still produce one row of headers if requested.
.ColumnWidths (string) — This semi-colon separated list contains the width of each column (or nothing for the default width). A width of zero hides the column.
The next set of properties give access to the data of the entire table (using the green and red indices).
.ItemData(row) (variant/string) — Returns the value of the bound column for the passed row number. Returns Null if the row is out of bounds or if there is no bound column.
.Column(col) (variant/string) — Returns the value of the passed column in the currently selected row. Note that the column number is zero-based.
.Column(col, row) (variant/string) — Returns the value of the required cell.
.ItemsSelected.Count (integer) — For single-selection list boxes and combo boxes, returns 1 or 0. This can be used instead of ListIndex to test whether a row is selected.
For each of the methods above, a full help page is available. Simply type the property and press F1. Many fine points will not make it into this article, it isn't meant as a substitute for the help pages.
Coding for the Combo
The value can be set to anything permitted by the underlying field (or by the underlying formatting), but it will be treated as text. Whether a row is also selected for that value is up to the combo box mechanics: if a match is found in the bound column, that row is selected. To select a given row, the appropriate bound column value must be entered, and the table data can be used to find it. For example to select the first item (“cbo” is the generic name of the combo):
cbo.Value = cbo.ItemData(0)
This seems right, and works as expected in most cases. However, given the considerations above, things are a little bit trickier...
' with a bound column, one of:
cbo.Value = cbo.ItemData(0) ' without headers
cbo.Value = cbo.ItemData(1) ' with headers
cbo.Value = cbo.ItemData(-cbo.ColumnHeads) ' both cases
' without a bound column
cbo.Value = IIf(cbo.ListCount + cbo.ColumnHeads, 0, Null)
' generic code
If cbo.BoundColumn Then
cbo.Value = cbo.ItemData(-cbo.ColumnHeads)
ElseIf cbo.ListCount + cbo.ColumnHeads Then
cbo.Value = 0
cbo.Value = Null
The boolean value in `column heads´ (-1 or 0) is inverted (1 or 0) to select the correct data row [line 11] and used without inversion to decrement the list count [line 12]. This trick can be used everywhere when you are writing generic code meant to work on combos and list boxes with headers and without headers. If the combo doesn't have a bound column, the code should check whether there is a row to select. The `list count´, again in conjunction with `column heads´ provides the answer.
So it is possible to write generic code, in this case to select the first item in a combo box — but only if there is one — which should work in all cases. In practice, this level of sophistication is never needed, either because you are writing for a specific combo box or because all possible combos targetted by the code are the same, typically with a bound column and no header rows...
Combo boxes without a bound column are quite rare, and they modify significantly the mechanics of the row selection. I will discuss them briefly below.
“How to... manipulate the combo”
This section assumes that the combo box or list box has a bound column. The control is called “ctl”, when the code works for any type; replace it with the real control name. Where appropriate, the snippets shows versions with and without a header row, but not the generic code that works in both cases. If this is needed, it usually involves adding `column heads´ to the `list count´ or subtracting it from the `list index´, as shown above.
select the first item
ctl.Value = ctl.ItemData(0) ' without headers
ctl.Value = ctl.ItemData(1) ' with headers
Note: if there is no row to select, this sets the value to Null.
select the next item
select the previous item
ctl.Value = ctl.ItemData(cbo.ListIndex + 1) ' without headers
ctl.Value = ctl.ItemData(cbo.ListIndex + 2) ' with headers
' without headers
ctl.Value = IIf(ctl.ListIndex > 0, ctl.ItemData(ctl.ListIndex - 1), Null)
' with headers
ctl.Value = IIf(ctl.ListIndex > 0, ctl.ItemData(ctl.ListIndex), Null)
Note: the test is necessary because ItemData(-1) doesn't reliably return Null, especially for a combo box when it has the focus.
select the last item
select if there is only one item
' without headers
ctl.Value = IIf(ctl.ListCount, ctl.ItemData(ctl.ListCount - 1), Null)
' with headers
ctl.Value = IIf(ctl.ListCount > 1, ctl.ItemData(ctl.ListCount - 1), Null)
detect if a value corresponds to a selection
' without headers
ctl.Value = IIf(ctl.ListCount = 1, ctl.ItemData(0), Null)
' with headers
ctl.Value = IIf(ctl.ListCount = 2, ctl.ItemData(1), Null)
display the drop-down section
ctl.Value = ...
If ctl.ListIndex = -1 Then
' no row was selected
' a match was found
The problem is that the method is a toggle (it will hide the drop-down section if it is visible), and that there is no property indicating the current state. This severely limits the usefulness of the method.
Special Case: Bound Column = 0
Setting the bound column to zero means that the combo box or list box will not store any value from the associated data table, but only the row index (the orange index in the figure). The methods of the previous section will not work, because `item data´ will always be Null. Instead, the desired index value can be written directly to the combo's value.
Setting the value to Null still reliably clears the combo and sets the list index to -1. However, writing an invalid value, one that doesn't select a row, is also possible. Strange results are obtained with the values -1 and -2, when the combo box is currently selected. If column heads are present, -1 will display the header; -2 (or -1 without headers) displays the last selected row.
For that reason, the `list count´ property should always be checked when assigning a value to a combo box without a bound column. These combos are quite rare, so I will not rewrite the examples from the previous section.
One case where setting the bound field to zero would make sense is a combo with the “field list” row source type. Ironically, this type ignores many settings of a combo, including the column count, the column widths, and the bound column!
Reading Values from a Combo or List Box
This section should be very brief. The property .Column can retrieve any value from the underlying data table of a combo or a list box, as explained above. The most frequent usage is to use the expression
= ‹control name›.Column(n)
in another text box. This also works in Visual Basic, for example in an `after update´ event.
However, there are some cases where it is more convenient to read from the actual recordset instead of using the `column´ property. This is the case when the row source of a combo comprises many columns, of which only a few have been declared. In older versions of Access, the `column´ property would attempt to retrieve any value way beyond the declared number of columns. Today, you need to either declare all the columns (and set their width to zero) or read from the record set.
.FindFirst "ID=" & cboName
The code above is meant as an illustration only. It will fail when the combo is Null, and if the bound column isn't numeric (for an alphanumeric key, the value needs to be quoted). A more direct method to synchronize the recordset with the current selection is to use the list index.
If ctl.ListIndex < 0 Then Exit Sub
.AbsolutePosition = cboName.ListIndex
Anyway, the point is that the recordset of a combo box contains all the columns from the query in the `row source´, while the `column´ property only returns the declared columns, plus one. Of course, this will only work if there is a record source. Value list combos (and field list combos for that matter)
Since the list box doesn't have the edit section, it's generally simpler. Most of the above applies equally to combo and list boxes. However, one thing a combo does not support is multiple selection. A list box has the additional property `multi-select´ (with two selection methods), and two more properties available at run-time.
.Selected(row) (boolean) — This read/write property allows to select and deselect a row, and to obtain the current state of each row. The row index is the red index in the figure at the top of the article, which I called the “item data” row index.
.ItemsSelected (collection) — For convenience, this collection contains all the row indices that are currently selected. ItemsSelected.Count gives their total number, and ItemsSelected(row) returns the nth index in the collection.
Two important properties change drastically in behaviour. The value is disconnected from the selection. It will normally be Null, and setting a value has no apparent effect. Likewise, the list index no longer corresponds to the highlighted row(s), but instead it indicates which row has the “focus”, meaning the dotted selection rectangle used to select with the keyboard. Incidentally, this allows to scroll the list box or otherwise make sure a certain row is visible in the scroll area.
Since neither the value nor the list index can be used to test whether a selection was made, the most general way to do so, in single or multi select list boxes and in combo boxes, is to examine the count of the `items selected´ collection.
determine if a selection was made
build a list of selected items
If lst.ItemsSelected.Count Then
' yes, use selection
' no selection
select (unselect) all items in a list
Dim List As Variant
Dim Index As Variant
List = Null
For Each Index In .ItemsSelected
List = List + ", " & .ItemData(Index)
MsgBox Nz(List, "(none)")
Dim i As Integer
For i = 0 To .ListCount - 1
.Selected(i) = True ' (False)
Formally, this code contains an error, in that it doesn't take into account the header row. However, changing the selected state of an invalid index — in this case zero — doesn't raise a trappable error.
The Not In List Event
As announced in the introduction, this article doesn't replace the Access help pages. If you search for the string “notinlist”, you will have ample explanations and even sample code to add data to a table or to a value list. There would be no point in paraphrasing these pages here.
However, the purpose of this event is made clear: it is meant to add data to the combo's row source. Even the predefined constants support this notion, e.g. acDataErrAdded to let the combo discover that the new data has indeed been added. In recent versions, more tools have been added to help the user in adding data to a combo. This feels like feature overloading to me, after all the combo already manages a (sometimes virtual) one-to-many relationship, often translates between an invisible key and a visible label, and helps data entry by things like auto-complete. Still, it does make sense in many cases.
All this fuss about adding data
leads to a misconception.
In reality, the only requirement for the “data added” condition is that the combo box discovers the new value. But, during the event, you can in fact completely change not only the row source, but even the row source type. Just after a successful not in list event (one where the new data is discovered), the after update event occurs, where the normal row source can be restored.
This mechanism (providing a special row source just to respond to a not in list event) opens many seemingly unrelated possibilities. For example, The Smart Combo
article describes how alternate search strings — meaningful to the user but not present in the list — can be accepted and dealt with, without adding any new data. I have more “smart combo” articles in mind; you will find them in the table of contents linked at the end of the article.
This was intentionally rather technical, but hopefully not too cryptic. While writing, I found myself creating numerous combo boxes and list boxes in all possible combinations of row source types and properties, to verify my claims. The weird thing is that I still discovered new titbits of information, after nearly two decades using the Access ComboBox.
I hesitated whether I should provide a demonstration database, one that would show at least a handful of variations. It became quickly apparent that I would have to design an entire interface, with tool-tips, explanatory labels, even help pages and error messages. Not so much to explain the combos, but merely to explain the interface around them. I dropped the idea, and I believe that your time is better spent dissecting a few combos on your own rather than exploring my notion of a decent demo.
Let's consider this article as a reference page on various techniques and peculiarities, or an index of the built-in help pages. I might very well return to it myself the next time I'm baffled by some unwanted behaviour or hit by the old “wrong index bug”.
I hope you find it useful, and that you'll remember it the next time you'll miss a step while “doing the combo”!
Markus G Fischer
This article is part of the EE-book unrestricted Access.