Access Combo Boxes

by Linda Quinn

Close Window Print Page

The ComboBox control is an VB/VBA control that can also be used in Access.

The ComboBox displays a list of values to choose from.

The ListBox has the same properties as the ComboBox except the values are always displayed. It doesn't have a drop down pane.

By having a user select from the ComboBox list, you can control the values selected.

In the examples below, the user can select a year and a quarter by clicking on the button at the side of the Combo Box and then selecting from the list of values that are displayed.

Set up the ComboBox

Adding the ComboBox control to the form will differ based on the version of Access being used.

Create a form and select the control tools. You might have to place the form in Design view.

Select the ComboBox control and drag it to the form.

Right-click on the ComboBox control and select Properties.

In the Properties box, give the ComboBox a name. Standard practice is to prefix the name with " cbo"

ComboBox Wizard
Access has a wizard that will help you create a ComboBox. The wizard appears whenever you draw a combo box on a form. The wizard is very helpful if you want to enter a list of values, or if you want the values to be copied from a table.

VBA

However, using VBA to set the properties of your ComboBox allows you to get the database keys, and the location of the selected item in the database. There are many reasons why you would want to do this.

Sample Database

City ID	City Name
20	Chicago
21	Houston
22	Dallas
23	New York

Sample ComboBox with the data

List	ListIndex	ItemData
Chicago	0	20
Houston	1	21
Dallas	2	22
New York	3	23

Properties

ItemData	The set of numbers that correspond to the items in the ComboBox.
List	Text strings for the items in the ComboBox.
ListCount	The number if items in the ComboBox.
ListIndex	The index of the item currently selected.
NewIndex	The index of the last item that was added to the ComboBox.
Sorted	States if the ComboBox is sorted.
Style	If the ComboBox has a drop-down list or a permantly visible list. Also determines if the user can add a new value, or can only select from the preset values.
Text	The Text value of the item currently selected

List, ListCount and ListIndex

List is the set of all values in the ComboBox. AddItem adds text items to the ComboBox.	cboBox1.AddItem "Chicago" cboBox1.AddItem "Houston" cboBox1.AddItem "Dallas" cboBox1.AddItem "New York"
ListCount is the number of items in the ComboBox. In this example, the ListCount is 4	x = cboBox1.Count x = 4
ListIndex is the index of the selected item. If no item is selected, the ListIndex is -1 cboBox1.ListIndex(2) is Dallas.	"Chicago (0)" "Houston (1)" "Dallas (2)" "New York (3)"

Text

Text is the value of the selected item.
If the user selects the third item (ListIndex = 2),
the text will be "Dallas"

str = cboBox1[2].text
str = "Dallas"

ItemData / NewIndex

ItemData is a hidden value for each value
in the ComboBox.

ItemData can be used to store a key value
while a more descriptive text value is displayed
in the ComboBox.

You cannot rely on the ListIndex to determine which
text value was selected, especially if the list has been sorted.

In the example on the right, each city has a
code in the database.
These are used as the ItemData values.

NewIndex is the index of the item currently being
added to the ComboBox.

In this example, the NewIndex value is replaced
with the city code and set as the ItemData value.

Styles

There are three types of ComboBox styles to choose from:

0-DropDown Combo	The user can type a new value into the control
1-Simple Combo	Same as a DropDown Combo except the list is always visible
2-DropDown List	Only items in the list can be selected.

Build a ComboBox or ListBox with Actual Data

cboBox.AddItem "Chicago"
cboBox.ItemData(cboBox.NewIndex) = 20

cboBox.AddItem "Houston"
cboBox.ItemData(cboBox.NewIndex) = 21

cboBox.AddItem "Dallas"
cboBox.ItemData(cboBox.NewIndex) = 22

cboBox.AddItem "New York"
cboBox.ItemData(cboBox.NewIndex) = 23

You can use variables from the database.
cboBox.AddItem DBfile![cityName]
cboBox.ItemData(cboBox.NewIndex) = DBfile![cityID]

* This example uses the data key for the cities in the ItemData value.
* This data key is an additional index for the selected data.
* When the user selects "Houston", the ItemData value is 21.
* This is the key used to access the records for Houston in the database.
* The ListIndex for Houston is a number between 0 and 3.
* However, that doesn't help us find Houston in the City table.

Here's how to get the ItemData for the selected item.

cboBox.ItemData(cboBox.ListIndex) = 21
cboBox.List(cboBox.ListIndex) = "Houston"

The first statement returns the ItemData value for the item at ListIndex (in this case, 1), which is Houston.

The ItemData for Houston was entered as 21, which is the key to Houston in the database. ListIndex is the ComboBox index of the item selected.

If Houston is selected, and Houston's ListIndex is "1", then the ItemData for ListIndex "1" is "21".

The List property of the ComboBox is the set of text values.
We retrieve the selected value using the ListIndex of the List array.

cboBox.List(cboBox.ListIndex) = "Houston"

To update a table with a selected key value, use the ItemData value.

If the user selected "Chicago" and we need to enter "20"
into a table, use the following code:

tblAddress.City = cboBox.ItemData(cboBox.ListIndex)