How to Retrieve All Selected Values from a Multi-Select Access List Box
Question details
The user needs to retrieve all the selected items from a multi-select list box in Microsoft Access using VBA.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Writing a VBA macro to iterate through a multi-select list box and extract data from a specific bound column for all highlighted rows.
- Observed behavior
- Looping through the list box rows currently returns only the last selected value instead of every selected item.
Ensure that your Microsoft Access list box has its 'Multi Select' property enabled (set to Simple or Extended) in the Property Sheet under the Other tab.
Loop Through Rows Using the Selected Property in VBA
Iterate through the list box rows using a zero-based index and check the Selected property for each row to accurately retrieve its values.
When an Access list box is set to Multi Select, directly referencing its value will only return the most recently selected item. To get all selected items, you must loop through the row collection and evaluate each row's Selected property.
Both the row indexes and the column indexes in Access VBA are zero-based. This means the first row is index 0, and the first column is index 0.
Press ALT + F11 in Microsoft Access to open the VBA Editor, and locate the form module where your list box code is executed.
Create a For loop that runs from 0 to ListCount - 1. For example: `For intRowCount = 0 To Me.lstSalesOrderLine.ListCount - 1`.
Inside the loop, check if the current row index is highlighted by using the Selected property: `If Me.lstSalesOrderLine.Selected(intRowCount) Then`.
Access the specific column of the selected row using the Column property. For instance, to get the 5th column's value for the selected row, use: `Debug.Print Me.lstSalesOrderLine.Column(4, intRowCount)`.
Looking for a Free Office Suite Alternative?
While Microsoft Access handles databases, if you frequently work with data tables, reports, and spreadsheets, WPS Office provides a highly compatible, lightweight, and completely free alternative to Microsoft Office. Seamlessly manage your Excel spreadsheets, Word documents, and PowerPoint presentations in one intuitive workspace.
- 1. Download WPS Office: Visit the official WPS Office website and click on 'Download WPS Office Free'.
- 2. Install the Software: Run the lightweight installer and follow the on-screen instructions to set up the software on your computer.
- 3. Open Your Files: Launch WPS Office and directly open your existing Microsoft Office files without worrying about formatting issues.
Frequently Asked Questions
Why does my VBA code only return the last selected item in an Access list box?
This happens if you reference the value of the list box directly (e.g., `Me.ListBox.Value`). In a multi-select list box, the Value property only holds the item that was clicked most recently. You must iterate through the `Selected` collection to retrieve all items.
Are Microsoft Access list box column indexes zero-based?
Yes, the `Column` property in Access VBA is zero-based. For example, `Column(0)` refers to the first column, `Column(1)` refers to the second column, and so on.
How do I enable multi-select on an Access list box?
Open your form in Design View, select the list box, open the Property Sheet (F4), navigate to the 'Other' tab, and change the 'Multi Select' property from 'None' to either 'Simple' or 'Extended'.
