[SOLVED] MS Access Listbox click will not register a listbox selection

MS Access Listbox click will not register a listbox selection

I've run into this a couple of times and haven't found any solutions, so hoping you can help. I have an unbound form with a combo box (cmbVariety) and two list boxes (lstItems and lstProductionYears). When a selection is made in cmbVariety, it sets the underlying rowsource query for lstItems. When an item in lstItems is selected, I want it to set the underlying rowsource of lstProductionYears based on the selection.

lstItems is and MUST be multi select: none. Cannot have the user select more than one line in the list.

so I've tried the code:

Private Sub lstItems_Click()
Dim i As Integer

With lstItems
    For i = 0 To .ListCount - 1
        If .selected(i) Then
            YearsTable .Column(0, i) 'A routine for setting the rowsource for lstProductionYears based on the lstItems selection
        End If
    Next i
End With

Me.lstProductionYears.Requery
End Sub

and the code:

Private Sub lstItems_Click()
Dim sel As Variant

With lstItems
    Debug.Print .ItemsSelected.count 'results in 0 selected even when a line is clicked
    For Each sel In .ItemsSelected 
        YearsTable .Column(0, sel) 'A routine for setting the rowsource for lstProductionYears based on the lstItems selection
    Next sel
End With

End Sub

In neither case does the selection register. It works for double click, but I use that for something else. What am I missing? (Again - I know it's different with multi select, but that isn't an option)

Solution

You could simply try passing lstItems.Value

Private Sub lstItems_Click()
        YearsTable lstItems.Value
End Sub