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.


ComboBox ComboBox ComboBox


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 Combo Icon 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.
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



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


* 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.


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.


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:













Copyright © 2006-2016, LQSystems,Inc. All rights reserved.


   Printer Friendly Page


Home