Combo Boxes



by Linda Quinn

Close Window


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.

ComboBox ComboBox ComboBox

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)





=================================================================
This content was created by Linda Quinn of LQNet.

See http://www.lqnet.com for a great collection of articles on this and other topics.

=================================================================
Copyright © 2006-2008, LQ Systems,Inc. All rights reserved.


====================================================================
Want an expert to help with your project?    LQ Systems, Inc.   Business Solutions
====================================================================