Combo Box Control
Printer Friendly 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.
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.
The
ListBox has the same properties as the
ComboBox except the values are always displayed.
There are three types of ComboBox styles to choose from:
Styles
| 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. |
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.
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 ComboBox
| List |
ListIndex |
ItemData |
| Chicago |
0 |
20 |
| Houston |
1 |
21 |
| Philadelphia |
2 |
22 |
| San Antonio |
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 "Philadelphia"
cboBox1.AddItem "San Antonio"
|
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
|
"Chicago (0)"
"Houston (1)"
"Philadelphia (2)"
"San Antonio (3)"
|
Text
Text is the value of the selected item.
If the user selects the third item, the text will be
"Philadelphia"
|
str = cboBox.text
str = "Philadelphia"
|
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.
|
cboBox.AddItem "Chicago"
cboBox.ItemData(cboBox.NewIndex) = 20
cboBox.AddItem "Houston"
cboBox.ItemData(cboBox.NewIndex) = 21
cboBox.AddItem "Philadelphia"
cboBox.ItemData(cboBox.NewIndex) = 22
cboBox.AddItem "San Antonio"
cboBox.ItemData(cboBox.NewIndex) = 23
|
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 "Philadelphia"
cboBox.ItemData(cboBox.NewIndex) = 22
cboBox.AddItem "San Antonio"
cboBox.ItemData(cboBox.NewIndex) = 23
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"
ListIndex is the ComboBox index of the item selected.
The first statement returns the
ItemData value for the selected item.
If Houston is selected, and Houston's
ListIndex is
"1",
then the
ItemData for
ListIndex "1" is
"21".
cboBox.ItemData(cboBox.ListIndex) = 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)