(Solved) – inconsistent ListBox behaviour in MS-Access (.ItemData vs. .Value)

(solved)-–-inconsistent-listbox-behaviour-in-ms-access-(itemdata-vs.value)

For single-select ListBox (.MultiSelect = False) in MS-Access (current 365) I found that setting .Selection(i) may lead to inconsistencies if mixed with using .Value instead of .ItemData(i):

  • Setting .Seletion(i) does not set .Value but highlights the row (so the user must think that value is selected).
  • Setting .Value to a value that is not in the list (which is possible!) removes the selection (=> could be used as a test of a value against the list: set .Value and check If .ItemsSelected.Count > 1).
  • .Selected(i) always matches .ItemData(i) => maybe more failsafe approach

Test code (to see highlighting behaviour you need to comment out some steps):

Private Sub cmdTest_Click()
    With Me.lstSingle
        FillList ' "eins", "zwei", "drei", "vier" (code see below)
        Debug.Print ".ItemData(1)", ".Value", "Selected(1)", "Selected(2)"
        ' note: rows count from 0

        Debug.Print .ItemData(1), .Value, .Selected(1), .Selected(2)
        ' result:   zwei          Null    0             0             no selection shown

        .Value = "test" ' assign a value not in the list
        Debug.Print .ItemData(1), .Value, .Selected(1), .Selected(2)
        ' result:   zwei          test    0             0             no selection shown

        .Selected(1) = True ' set selection on second row
        Debug.Print .ItemData(1), .Value, .Selected(1), .Selected(2)
        ' result:   zwei          test     -1           0             second row highlighted
        '                         ^^^^                                ^^^^^^^^^^^^^^^^^^^^^^

        .Value = "drei" ' assign a value that is in the list
        Debug.Print .ItemData(1), .Value, .Selected(1), .Selected(2)
        ' result:   zwei          drei     0            -1            third row highlighted

        .Selected(1) = True ' set selection back on second row
        Debug.Print .ItemData(1), .Value, .Selected(1), .Selected(2)
        ' result:   zwei          drei    -1             0            second row highlighted
        '                         ^^^^                                ^^^^^^^^^^^^^^^^^^^^^^

        .Value = "test" ' assign a value not in the list (again)
        Debug.Print .ItemData(1), .Value, .Selected(1), .Selected(2)
        ' result:   zwei          test    0             0             no selection shown
    End With
End Sub

'results compared
'.ItemData(1)  .Value  Selected(1)  Selected(2)
'zwei          Null    0            0             no selection shown
'zwei          test    0            0             no selection shown
'zwei          test    -1           0             second row highlighted
'zwei          drei    0            -1            third row highlighted
'zwei          drei    -1           0             second row highlighted
'zwei          test    0            0             no selection shown


Private Sub FillList()
    With Me.lstSingle
        ' clear list
        .RowSource = vbNullString

        ' fill list
        Dim vA As Variant
        vA = Array("eins", "zwei", "drei", "vier")
        Dim i As Long
        For i = LBound(vA) To UBound(vA)
            .AddItem vA(i)
        Next i
    End With
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *