Windows Controls: The Combo Box

There are various ways you can create a combo box or a list box in Microsoft Office Access 2007. The classic way is that, after displaying a form or report in Design View, in the Controls section of the Ribbon, click either the Combo Box button or the List Box button and click the form or report. When you do this, if the Control Wizards button is down, a wizard would start. If you want to create a list manually, you can click Cancel. Otherwise, you can continue with the wizard.

To programmatically create a combo box, call the CreateConotrol() function and pass the ControlType as acComboBox. The first argument is the name of the form or report on which the label will be positioned. Here is an example:

Private Sub cmdCreateControl_Click()
    Dim ctlGenders As Control
    
    Set ctlGenders = CreateControl("Exercise", _
                                   AcControlType.acComboBox)

    Set ctlGenders = Nothing
End Sub

To programmatically create a list box, call the CreateConotrol() function and pass the ControlType as acListBox. The first argument is the name of the form or report on which the label will be positioned. Here is an example:

Private Sub cmdCreateControl_Click()
    Dim ctlGenders As Control
    
    Set ctlGenders = CreateControl("Exercise", _
                                   AcControlType.acListBox)

    Set ctlGenders = Nothing
End Sub

The third argument is the section of the form or report where the control will be positioned. You can pass the fourth argument as the name of the form or report on which the label will be positioned. That is, the first and the fourth argument can be the same.

The primary reason for having a combo or a list box is to display a list of items. There are various ways you can specify this list.

When adding a combo or a list box to a form or report, if you use the wizard, it would assist you with creating and even configuring the list of items. If you don't use the wizard, you will need to create and configure the list yourself. Before creating the list of items of a combo or a list box, you must first specify how the list would be created. The property that allows you to specify the type of list is called RowSourceType. As it happens, the combo and the list boxes of Microsoft Access provide three ways to specify the origin of the list. Two options require a table (or a query, or a SQL statement).

To visually specify the list of items of a combo or a list box, each of their object (ComboBox or ListBox) is equipped with a property named RowSource.

If you want to create a list of strings to display in a combo box or list box, set the RowSourceType property to "Value List". This would be done as follows:

Private Sub Detail_Click()
    cbxGenders.RowSourceType = "Value List"
End Sub

After specifying this, to assist you with adding the items to the list of the control, the ComboBox and the ListBox classes are equipped with a collection property. This property mimics the behavior of the Collection class. For example, to add an item to the control, you can call its AddItem() method. Here are examples:

Private Sub Detail_Click()
    cbxGenders.RowSourceType = "Value List"
    
    cbxGenders.AddItem "Male"
    cbxGenders.AddItem "Female"
    cbxGenders.AddItem "Unknown"
End Sub

After creating the control, to locate an item in its list, you can use its indexed property.

Many of the combo boxes you will in your forms or reports get their values from another table through a pre-established relationship. Such combo boxes have their RowSourceType set to Table/Query. To make data entry convenient, you can allow the user to add a value from the form or report where the combo box resides. Unfortunately, after adding the new value, the combo box is not automatically updated. You or the user must manually update the combo box. The user can change the form's view to design and switch it back to Form View. This is inconvenient and most users do not that this is possible. Fortunately, the ComboBox class is equipped with a method to update itself. The method is called Requery.