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.
Microsoft Access within an Organization's Database Strategy
How many simultaneous Microsoft Access users?
Blaming Microsoft Access instead of the Developer
Microsoft Access Version Feature Differences
Microsoft Access Versions, Service Packs and Updates
Microsoft Office 365 Access Update Version Releases
Top 14 Features Added with MS Access 2007
Taking Over Legacy MS Access Databases
Winner of Every Best Access Add-in Award
Split Database Architecture for Multiuser
Set AutoNumber Starting Number Other than 1
Avoid Unnecessary or Duplicate Indexes
Replace Attachment Field Paperclip Icon
Copy Command Button and Keep Picture
Module VBA to Forms and Controls
Subform Reference to Control Rather than Field
Suppress Page Headers and Footers on the First Page of Your Report
Annual Monthly Crosstab Columns
Add Buttons to the Quick Access Toolbar
Collapse the Office Ribbon for more space
Avoid Exits in the Body of a Procedure
Send Emails with DoCmd.SendObject
Error Handling and Debugging Techniques
Error Number and Description Reference
Remote Desktop Connection Setup
Terminal Services and RemoteApp Deployment
Missing Package & Deployment Wizard
Remove 'Save to SharePoint Site' Prompt from an Access Database
Class Not Registered Run-time Error -2147221164
Microsoft Access to SQL Server Upsizing Center
When and How to Upsize Access to SQL Server
SQL Server Express Versions and Downloads
Deploying MS Access Linked to SQL Azure
SQL Server Azure Usage and DTU Limits