Programming Combo Box and List Box Controls in Microsoft Access, Part 1
David Shank
Microsoft Corporation
March 2, 2000
Developers working on Microsoft Access solutions have long recognized the power and versatility of combo box and list box controls. The kinds of problems developers encounter with these controls seem to fall into certain well-recognized areas, such as how to get data into the controls and how to determine what items a user has selected. This month, I'm going to talk about working with these controls on Access forms.
Before I get started, I want to point out that the combo box and list box controls available in Microsoft Access are not the same as the Combo Box and List Box controls available to Microsoft Visual Basic forms or to userforms in an Office application's Visual Basic Development Environment (VBE). Because Access is a database application, the controls are more data-aware than those found in other environments. This difference is the main source of confusion for developers who may be coming to Access from some other development environment. For example, you use the AddItem method to programmatically add data to a combo box or list box control on a userform. In Access, there is no AddItem method for these controls.
Getting Data in the Control
In Access, you work with the RowSource and RowSourceType properties of a combo box or list box control to specify or determine the source of data for the control. The RowSourceType property determines what type of data source a combo box or list box will use. The property has three built-in settings: Value List, Table/Query, and Field List. You can also set this property to the name of a custom function that you've created to fill the combo box or list box. You use the RowSource property to specify or determine the source of data for the control.
Using the Value List Setting
When you set the RowSourceType property to "Value List," you supply the values you want to appear using a comma-delimited list of items in the RowSource property setting. This combination is most useful when you have a small number of items that won't change. If you want the control bound to a field in a table, then you must also set the control's ControlSource property. When the ControlSource property is used in this way, the value a user selects is the value that is stored in the field specified by the ControlSource property.
You set these properties at design-time using the controls property sheet. For example, the following image shows the property sheet for a combo box used on a Contacts form to specify the type of contact:
The following code sets these properties in the Load event for a form.
Private Sub Form_Load()
Dim cboType As ComboBox
Set cboType = Me!cboContactType
With cboType
.RowSourceType = "Value List"
.RowSource = "Customer;Friend;Family;Employee"
.ControlSource = "ContactType"
End With
End Sub
Using the Table/Query Setting
When you set the RowSourceType property to Table/Query, you specify that a table, query, or SQL statement will supply the data in the combo box or list box. You use the RowSource property to specify the name of the table or query, or the SQL statement that returns the data you want to display.
When your data comes from a table, query, or SQL statement specified in the form's RecordSource property, you can also bind the control to a field in that table, query, or SQL statement by setting the ControlSource property. When the control is bound, the value selected by the user will be stored in that field, in the table where the field is defined.
It is often useful to store one value and display another when you're creating a combo box or list box that is bound to a foreign key from another table, since the foreign key is likely to be a number or a code. You can design the combo box or list box so that Microsoft Access saves the value of the foreign key to the table, but displays a more friendly value from the same record. An example is the SupplierID combo box on the Products form in the Northwind sample database (\\Program Files\Microsoft Office\Office\Samples\Northwind.mdb). SupplierID is a foreign key in the Products table whose value is an integer. The SupplierID combo box stores this integer value in the Products table, but displays the value of the CompanyName field from the Suppliers table in the combo box.
If you don't set the ControlSource property, the value that the user selects from the control won't be stored in the table and therefore won't affect your data in any way. If you don't want the selected value to be stored in the table, you can set the RowSource property to a table, query, or SQL statement other than the one specified by the form's RecordSource property. For example, you don't need to bind the control if you simply want to show a list of values from a table, query, or SQL statement. The user can select one or more of these values without affecting data in a table.
Binding and displaying columns
When you specify a table, query, or SQL statement in the RowSource property of a combo box or list box, all of the fields in that table, query, or SQL statement are available to the control. You can choose which field you wish to be bound and which field or fields you wish to display.
A combo box or list box can contain multiple columns, each of which corresponds to a field in the table, query, or SQL statement specified by the RowSource property. The Value property of a combo box or list box can return only a single value, however. The column that contains the value that is returned by the control's Value property is known as the bound column. The BoundColumn property indicates which column in the control is bound. If you've set the control's ControlSource property to a particular field, the setting for the BoundColumn property should be the number of the column that corresponds to that field.
The columns in a combo box or list box are numbered beginning with 1, in the same order as the fields in the underlying table, query, or SQL statement. To set the BoundColumn property to the first column, set it to 1; to set it to the second column, set it to 2, and so on. For example, if a control's RowSource property is set to the Employees table and the ControlSource property is set to the EmployeeID field, the BoundColumn property should be set to 1, indicating that the first column in the control is bound to the EmployeeID field. Any column can be the bound column, but in most cases you'll want to choose a column that's either a primary key or a foreign key.
The ColumnCount property specifies the number of columns contained in the combo box or list box. The number of columns available is determined by the number of fields in the table, query, or SQL statement specified by the RowSource property. For example, in the Northwind sample database the SupplierID combo box on the Products form is based on a query that returns two fields, the SupplierID and CompanyName fields. The ColumnCount property for this combo box is set to 2. The first column, SupplierID, is the bound column, and the second column, CompanyName, supplies the values displayed in the combo box.
If you set just the BoundColumn and ColumnCount properties, both columns will appear in the combo box -- SupplierID first, followed by CompanyName. However, the user needs to see only CompanyName. If you set the ColumnWidths property, you can hide the SupplierID column altogether and display only the CompanyName column. To set the ColumnWidths property, specify widths for each column, separated by semicolons. If you omit a column, the default width is used for that column. For example, to hide the SupplierID column, set the ColumnWidths property to "0; ;".
The Products form is bound to the Products table. The Products table includes a field named SupplierID which is the Foreign Key pointing to data in the Suppliers table. As you can see, the SupplierID combo box is bound, via the ControlSource property setting, to the SupplierID field in the Products table.
The RowSourceType property is set to "Table/Query" and the RowSource property contains the following SQL statement:
SELECT DISTINCT [Suppliers].[SupplierID], [Suppliers].[CompanyName] FROM Suppliers ORDER BY [Suppliers].[CompanyName];
This statement returns the SupplierID and CompanyName columns from the Suppliers table. Remember, the goal here is that the control display the supplier name but store the SupplierID. The ColumnCount property is set to 2, so the control will contain both columns returned by the SQL statement. Since you want to display only the CompanyName value, you use the ColumnWidths property to hide the first column (by specifying a column width of 0) and show the second column (by specifying a width of 2.2813 inches).
Using the Field List Setting
When you set the RowSourceType property to Field List, the combo box or list box displays a list of the fields available from the form's record source. For instance, if the form's RecordSource property is set to the Employees table, the control will display the names of all the fields in that table. You don't need to set the ControlSource property when you use this setting, because you're not binding the control to a particular field. You must set the RecordSource property for the form, however, or there will be no fields displayed in the field list. This setting for the RowSourceType property may be useful, for example, when you're creating a wizard that allows a user to choose which fields they want to include on a form.
Dynamically Add Items to a Combo Box
You can control how and when the user adds new data by using the LimitToList property and the NotInList event of a combo box.
The LimitToList property indicates whether the user can add a new item to the list displayed in the control. When this property is False, the user can type a new value in the text portion of the combo box, but this value isn't added to the list. When this property is True, however, the NotInList event is triggered when the user tries to enter a new value in the combo box. Within the NotInList event procedure, you can write Visual Basic for Applications code to add the new item to the control's source and requery it, you can ignore the user's attempt to enter it, or you can post an error message stating that the item can't be added. Which action occurs depends on the value you give to the Response argument of the NotInList event procedure.
When you set the Response argument to acDataErrAdded, Microsoft Access enables you to add the value of the NewData argument to the RowSource property setting. Once the value has been added, Microsoft Access automatically requeries the combo box. If you set the Response argument to acDataErrContinue, Microsoft Access doesn't display an error message. If you don't set the Response argument or set it to the default value, acDataErrDisplay, Microsoft Access displays the default message stating that the value the user typed is not in the list. Use this value or acDataErrContinue if you don't want the user to add an item to the list.
The following code sample shows how to add new data supplied by the user to a combo box displaying data from a different table than that specified in the RecordSource property of the form that contains the control. The combo box is named CategoryID, and the code in the NotInList event procedure adds the data supplied by the user to the Categories table by opening the AddCategory form, which is bound to the Categories table. This lets the user fill in all the necessary information, and then automatically displays the new data in the combo box when the Categories form is closed.
Private Sub CategoryID_NotInList(NewData As String, _
Response As Integer)
If MsgBox("Do you want to add '" _
& NewData & "' to the items in this control?", _
vbOKCancel, "Add New Item?") = vbOK Then
' Remove new data from combo box so control can be requeried
' after the AddCategory form is closed.
DoCmd.RunCommand acCmdUndo
' Display form to collect data needed for the new record.
DoCmd.OpenForm "AddCategory", acNormal, , , acAdd, acDialog, NewData
' Continue without displaying default error message.
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
You can also use the NotInList event to dynamically add an item to a combo box or list box based on a value list. In this case, you concatenate a string containing the value to be added with the existing value of the RowSource property. However, keep in mind that if you change a property setting while a form is in Form view, that change is preserved only as long as that instance of the form exists. Once you close the form or switch to Design view, the change is lost. Therefore, if you change the setting of the RowSource property by adding an item to the list at run time, that change will be lost when you close and re-open the form. To preserve this change, you'd need to edit the property in Design view.
Determining what the User Selected in a List Box
The MultiSelect property determines how a user can select data in a list box. You can set this property to None, to prevent the user from selecting more than one item at a time; to Simple, to allow the user to select multiple items by clicking them with the mouse; or to Extended, to allow the user to select multiple items using the SHIFT and CTRL keys.
If the MultiSelect property is set to None, you can determine which row the user selected by returning the value of the ListIndex property. The ListIndex property returns the index of the selected row. Rows in a list box are indexed beginning with zero, so if the first row is selected, ListIndex returns zero, if the second row is selected, ListIndex returns 1, and so on.
If the MultiSelect property is set to Simple or Extended, you can use the Selected property or the ItemsSelected collection to determine whether a particular item in the list is selected. The Selected property is a zero-based array that contains the selection state of each item in a list box. For example, if you wanted to determine whether the first item in a list box is selected, you would check the value of the Selected property for that item. The following line of code prints the value of the Selected property for the first item in a list box named List1 to the Debug window:
Debug.Print Me!List1.Selected(0)
To return data from a row in a list box, regardless of whether that row is selected, you can use either the ItemData property or the Column property. The ItemData property returns data from the column specified by the BoundColumn property. The Column property returns data from a specified row and column.
The following example fills a list box with data, then checks the Column property for each row and column in the list box, and prints it to the Debug window. To try this example, create a new form in the Northwind sample database, create a list box named List0 on the form, paste the following code into the form's module, and switch to Form view.
Private Sub Form_Load()
Dim lst As ListBox
Dim intI As Integer, intJ As Integer
Set lst = Me!List0
' Display EmployeeID, LastName, FirstName fields in list box.
With lst
.RowSourceType = "Table/Query"
.RowSource = "Employees"
.ColumnCount = 3
End With
' Print value of each column for each row.
For intI = 0 To lst.ListCount - 1
For intJ = 0 To lst.ColumnCount - 1
Debug.Print lst.Column(intJ, intI)
Debug.Print
Next intJ
Next intI
End Sub
The next example shows how you can select items from one list box and copy them to another list box. To try this example, create a new form in the Northwind sample database and add two list boxes to the form, named List1 and List2. Set the MultiSelect property of List1 to Extended. Add the following code to the form's module to create the Load event procedure. When the form loads, this code will fill List1 with a list of product names from the Products table. It will also notify you if the MultiSelect property is set to None.
Private Sub Form_Load()
Dim lst1 As ListBox, lst2 As ListBox
Dim strSQL As String, strMsg As String
Set lst1 = Me!List1
Set lst2 = Me!List2
' Initialize SQL string.
strSQL = "SELECT ProductName FROM Products ORDER By ProductName;"
' Notify user if multiple selection is not enabled.
If lst1.MultiSelect = 0 Then
strMsg = "The MultiSelect property of List1 is set to None." & _
"To enable multiple selection, open the form in Design view " _
"and set the MultiSelect property to Simple or Extended."
MsgBox strMsg, vbInformation, "Multiple selection not enabled"
End If
' Fill List1.
With lst1
.RowSourceType = "Table/Query"
.RowSource = strSQL
.ColumnCount = 1
End With
' Specify that List2 is a value list.
lst2.RowSourceType = "Value List"
End Sub
Next, add a command button named Copy to the form, and paste the following code into the form's module to create the command button's Click event procedure.
Private Sub Copy_Click()
Dim lst1 As ListBox, lst2 As ListBox
Dim itm As Variant
Set lst1 = Me!List1
Set lst2 = Me!List2
' Check selected items.
For Each itm In lst1.ItemsSelected
' Set RowSource property for first selected item.
If lst2.RowSource = "" Then
lst2.RowSource = lst1.ItemData(itm)
Else
' Check whether item has already been copied.
If Not InStr(lst2.RowSource, lst1.ItemData(itm)) > 0 Then
lst2.RowSource = lst2.RowSource & ";" & lst1.ItemData(itm)
End If
End If
Next itm
End Sub
When the form's Load event occurs, the first list box is filled with a list of products from the Products table. When you select one or more products from the list and click the Copy button, the selected product names appear in the second list box. Each item can be copied only once.
Where to Get More Info
Here are some additional resources you can use to explore working with Microsoft Access:
- For more information on developing solutions using Microsoft Access see the Office Developer site's list of recommended articles (https://msdn.microsoft.com/access).
- As always, check in regularly at the Office Developer Center for information and technical articles (https://msdn.microsoft.com/office) on Office solution development.
David Shank is a Programmer/Writer on the Office team specializing in developer documentation. Rumor has it he lives high in the mountains to the east of Redmond and is one of the few native northwesterners still living in the northwest.