Sample database: SelectFirstItemInList.zip (36 KB)
Combo boxes and list boxes are a great way to control user experience by limiting the values that a user can choose to a known list.
By default, the value is blank but when you initialize or refresh a ComboBox or ListBox, you may want to select the first item in the list.
This can occur when the form loads, or if the RowSource values of the ListBox or ComboBox change based on another selection (see our tip on Cascading ComboBoxes/ListBoxes).
To replace the blank value, use this syntax to set the value of the control to the first item (assumes Column Heads is set to No):
Me.ControlName = Me.ControlName.ItemData(0)
Our example database contains a form with a ComboBox containing ProductCategories, and a ListBox containing Products.
When we load the form, we want to select the first Category in the list.
Private Sub Form_Load() ' When the form loads, select the first item in the Category ComboBox Me.cboCategoryName = Me.cboCategoryName.ItemData(0) ' Call the code that updates the Products ListBox based on the selected category cboCategoryName_AfterUpdate End Sub
When we change the Category, we want to update the Products list, and select the first Product in the list:
Private Sub cboCategoryName_AfterUpdate() ' Call the code that updates the Products ListBox based on the selected category Me.lstProducts.RowSource = "SELECT ProductName " & _ "FROM products " & _ "WHERE CategoryID = " & Nz(Me.cboCategoryName) ' Re-load the list box Me.lstProducts.Requery ' Select the first Product in the list box Me.lstProducts = Me.lstProducts.ItemData(0) End Sub
After updating the RowSource property, use the list box's Requery method to re-load the data in the ListBox.
If a combo box or list box has the Column Heads set to Yes, the first displayed row is the title of the column.
In this case, the value of ItemData(0) is the name of the column since that's the first value in the list. To get the name of the first data value, choose element 1:
Me.ControlName = Me.ControlName.ItemData(1)
There is sometimes confusion between the terms RecordSource and RowSource. Both are properties that can contain a table, query, or SQL string.