How to set Selected in multiselect listbox?

[DavidFord]

I'm trying to populate listboxes on a form, based on saved off values in a table. I select items, save the form, those items are written off to a table. When I open the form again, and double click on a combo box value, I want the form to set items to selected based on the records in the table that have the value from the combo box saved off with them.

Problem is, it will only set the last of a list of saved off values to Selected. I'm guessing Selected wants an array, but when I tried to construct one, it gave a "type mismatch" error. I had constructed a string of values separated by a semicolon.

Here is some pertinent code. It checks to see of a value in the underlying table matches the text value in the listbox. If so, I want that item highlighted as selected.

Do While Not rst.EOF
    For i = 0 To Me.lstLOB.ListCount - 1
        Me.lstLOB.Selected(i) = False
        If rst("filterid").Value = CInt(Me.lstLOB.ItemData(i)) Then
            selectStr = selectStr & i & ";"
            'Me.lstLOB.Selected(i) 'THIS ONLY FLAGS THE LAST ONE COMING THROUGH
        End If
       
    Next
    
    rst.MoveNext
Loop
 Me.lstLOB.Selected(selectStr) = True 'Gives type mismatch error.

Any ideas or pointers, folks?

David

[June7]

Consider:

With Me.lstLOB
    For i = 0 To .ListCount - 1
        .Selected(i) = False
        rst.FindFirst "ID=" & .Column(0, i)
        If Not rst.NoMatch Then
            .Selected(i) = True
        End If
    Next i
End With

AFAIK, an array could be used to set RowSource property, not selected.

[DavidFord]

thanks. That is not working. This:

me.lstLOB.Selected(i) = true

is only retaining the last one input.

[DavidFord]

Fixed it.

This code was wiping previous entries out. I only wanted it to wipe the current one out.

Me.lstLOB.Selected(i) = False

[June7]

Don't quite understand that. The code certainly worked for me. All the recordset values were highlighted in the listbox. But if you got what you want, glad for that.

[DavidFord]

I can't figure out why that was unselecting previous entries. It was supposed to just unselect the current one before making a decision to select it again.

[June7]

I am still confused. Is this a multi-select listbox? If not, is listbox bound to field?

[moke123]

I use an array passed to a public sub to select items in a multi select listbox.

Public Sub SelectLBX(lbx As ListBox, strIN As String)


'Clears the listbox and then selects the values in the array passed in


    On Error GoTo SelectLBX_Error


    Call ClearList(lbx)


    Dim varItm As Variant
    Dim i As Integer
    Dim Y As Integer


    varItm = Split(strIN, ",")


    For Y = 0 To UBound(varItm)


        For i = 0 To lbx.ListCount - 1


            If lbx.ItemData(i) = varItm(Y) Then
                lbx.Selected(i) = True
                Exit For
            End If


        Next i


    Next Y


    On Error GoTo 0
    Exit Sub


SelectLBX_Error:


    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SelectLBX of Module modLBX"


End Sub

The following sub is called in the sub above, so including it here.

Function ClearList(lst As ListBox) As Boolean


    On Error GoTo Err_ClearList


    'Purpose:   Unselect all items in the listbox.
    'Return:    True if successful
    'Author:    Allen Browne. http://allenbrowne.com  June, 2006.


    Dim varItem As Variant


    If lst.MultiSelect = 0 Then
        lst = Null
    Else
        For Each varItem In lst.ItemsSelected
            lst.Selected(varItem) = False
        Next
    End If


    ClearList = True


Exit_ClearList:
    Exit Function


Err_ClearList:


    Resume Exit_ClearList


End Function

[DavidFord]

I am still confused. Is this a multi-select listbox? If not, is listbox bound to field?

Multiselect Simple. Not bound to a field, though the contents are pulled from one table through the RecordSource parameter, and the list of selected ones are pulled from another table. Population of the listbox is handled by a query, and the selection is handled by code, looping through another table.

[moke123]

I can't figure out why that was unselecting previous entries. It was supposed to just unselect the current one before making a decision to select it again.

.Selected(i) = False needs to occur outside the loop that is setting the selected property.

You need to clear all the .selected = true properties first and then loop through it again to set the .selected property.

If you see in the code I posted I call a sub to clear the listbox then do my loop to set .selected

[DavidFord]

.Selected(i) = False needs to occur outside the loop that is setting the selected property.

You need to clear all the .selected = true properties first and then loop through it again to set the .selected property.

If you see in the code I posted I call a sub to clear the listbox then do my loop to set .selected

Thanks. That's what I ultimately ended up doing yesterday afternoon, and it worked.

[June7]

Okay, but I tested my code and it worked for Simple and Extended.