cycling through values in a MS Access list box

Question

I have a list box that populates with different sets of data based on user selections.

How can I cycle through any given values that may be in the list box? Is this a For Each statement, or what?

Wifely

Do not forget that you can have a multiselect list box and use the Selected property if space on the form becomes an issue. – Rete

Answer 1

You can do a For loop to examine each row in the listbox, and do whatever with the rows which are selected. In this example, I display the second column from selected items in the lstLocations listbox. (Column numbering starts with zero.)

Private Sub cmdShowSelections_Click()
    Dim lngRow As Long
    Dim strMsg As String

    With Me.lstLocations
        For lngRow = 0 To .ListCount - 1
            If .Selected(lngRow) Then
                strMsg = strMsg & ", " & .Column(1, lngRow)
            End If
        Next lngRow
    End With

    ' strip off leading comma and space
    If Len(strMsg) > 2 Then
        strMsg = Mid(strMsg, 3)
    End If
    MsgBox strMsg
End Sub

Note I assumed you want the selected items from the list box. If you want all items, selected or not, you could use .ItemData as @DavidRelihan suggested. However, in that case, you could get them from the listbox .RowSource instead.

Folger

Answer 2

Here is how you iterate through the ListBox:

Dim i as Integer

For i = 0 to Me.ListBoxName.ListCount -1
   'Access each item with 
   'Me.ListBoxName.ItemData(i)
Next i

Whiffet

Answer 3

If working with a listbox in Access I like to capture the listbox recordset and loop through it. Perhaps because I find DAO recordset objects easy to work with.

I'd do something like:

Dim Rst as DAO.Recordset
Set Rst = lbxYourListboxObj.Recordset
'test to assure that there are records
If Rst.EOF then
     'some error handling
end if
'I'm just paranoid so I always do this
Rst.MoveFirst
'iterate through list
Do Until Rst.EOF
    'do something for each record
    'it is nice and convenient to be able to reference the field names directly too!
    debug.print Rst!Field1.name,Rst!Field1.value
    Rst.MoveNext
Loop

Municipalize