how to preselect a value in ListBox in MS-Access form?

Viewed 559

Trying to preselect one value in a single-select ListBox (.MultiSelect = False) in MS-Access (current 365) by code, 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 > 0).
  • .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

What would be best practice for preselecting a value in a ListBoxcontrol?

1 Answers

Yes, I also learned this (the hard way) a long time ago.

But you already answered your question: Setting .Value is the way to go.
Especially if you will read the value later, and the user may never change the default selection.

Related