Listbox.Selected not working

[Dave D]

I do not know why this isn't working. The "selected" is never realized. You will also notice it isn't even capitalized; it's like access doesn't recognize the property. The crazy thing is I have similar code in a double-click sub and that works fine ("selected still isn't capitalized, though).

I use myVar to go back to the selected record after the listbox is requeried.

Your help is greatly appreciated.

Private Sub lstPartsInSelectedSow_Click()

    Dim i As Integer
    
    For i = 1 To Me.lstPartsInSelectedSow.ListCount - 1
        If Me.lstPartsInSelectedSow.selected(i) Then
            myVar = i           'myVar is a global variable of type Variant
        End If
    Next I

End Sub

[thebigthing313]

The similar code On Dbl Click, is this on the same database? If not then maybe on this one you accidentally removed the reference that contains the Listbox object. You can check VBA Editor>Tools>References, see if the Microsoft Access Object Library isn't checked.

Aside from that, I'm not entirely sure what else it could be. You can try a compact and repair.

[Dave D]

The similar double-click code is in the same db, on the same form for the same listbox. I DID wonder if something was corrupted so I imported everything into a new db - same problem.

Strangely, this works if I set the listbox's multi-select property to 'simple' but I don't want multi-select, I only want single-select. Multi-select would screw everything up.

[thebigthing313]

Regardless of the listbox's multi-select property, Listbox.Selected property should still work. So does the code work and it's just not autocapitalizing "Selected" to indicate that Access recognized the property, or the code doesn't work at all?

And if the code doesn't run, what error are you getting? If Access is really not recognizing the Listbox's Selected property, you would get a compile error saying that the method or data member was not found. Is this the error you are getting?

[Dave D]

The code runs - I can step through each line and watch it. However, it doesn't recognize any row in the listbox as being selected. I even tried putting this code in the After_Update sub, but the same results.

This code works, btw:

Private Sub lstPartsInSelectedSow_DblClick(Cancel As Integer)
    Dim i As Integer
    Dim pRcd As Long
    Dim sRcd As Long
    
    For i = 1 To Me.lstPartsInSelectedSow.ListCount - 1
        If Me.lstPartsInSelectedSow.selected(i) Then
            sRcd = Me.lstPartsInSelectedSow.Column(0, i)
            pRcd = Me.lstPartsInSelectedSow.Column(4, i)
            Debug.Print "sRcd = " & sRcd
            myVar = i
        End If
    Next i
    
    DoCmd.OpenForm "frmEditSowParts", , , "sowId = " & sRcd & " AND tblParts.pRecordId = " & CLng(pRcd)
End Sub

[thebigthing313]

Hrmm. Ok, so when select one (single-click), you want to set myVar to the value right? If nothing else, instead of iterating through the list items to find the selected value, would

myVar = Me.lstPartsinSelectedSow.Value

work instead?

I would need to see the DB myself to troubleshoot further.

EDIT: Nevermind, you were passing the counter i to myVar. Is there a reason you're passing the counter value, instead of the actual listbox value?

[Dave D]

This list box is a down-select from another list box and the value is a PK on a 1:M so it's all the same. To get other data from another column then I'd need to know which row in the list box is selected. It's a catch-22.

Attached is a simple example of my problem. Open the form and single click and then double click. Only the double-click works.

[thebigthing313]

Weird. It seems the Selected Property Array isn't updated until the control loses focus (though I'm not sure why dbl click works but not single click). If I made my selection in the listbox, then used a command button to return the selected row number, the selected-loop works. But not when the listbox goes through Click or AfterUpdate. It behaves as if nothing was still selected. I'm not sure I know what's happening here, maybe someone else can shed some light.

However, you can try using:

myVar = Me.lstPartsInSelectedSow.ListIndex

to get the row number.

[Dave D]

.ListIndex worked swimmingly. Thank you!

Excellent insight into the .Selected property. I, too, am interested if anyone else can identify why this property doesn't work if the object has focus.

I'll leave the thread open for now and, if no one else comes back with an answer within a week or so, I'll set the thread as solved.

[June7]

Selected is capitalized by VBA editor when I opened the db, however, code does not work.

This is not a multi-select listbox. Why bother with code that cycles through the list anyway? Just take the Value of listbox. I would use AfterUpdate event.

For multi-select listbox, review http://allenbrowne.com/ser-50.html

[Dave D]

I see this, now.

Do you know why the .Selected will not work for a single click?

[June7]

Nope. Never seen code that uses it. Doesn't seem to work with any event.