asked on

Set Listbox Selected Items by Bound Column

This has gotta be easy, but the solution is escaping me for the moment. I want to set the selected values of a multiselect listbox by the value of the bound column. I know how to set by row number:

    Dim rst As New ADODB.Recordset
    Dim strSQL As String
    strSQL = "SELECT ViewFilterStatusNumber FROM tbl_ViewFilterStatuses " & _
             "WHERE ViewNumber= " & Me!cmbView

    rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockPessimistic
    
    Do Until rst.EOF

        Me!Statuses.Selected(rst(0) - 1) = True
        
        rst.MoveNext
    Loop

Open in new window

but don't want to do that since it's dependent on sorting.

Something like:

Do Until rst.EOF
    
        For Each varItm In ctl
            If ctl.ItemData(varItm) = rst(0) Then ctl.ItemData(varItm).Selected = True
        Next varItm

        rst.MoveNext
    Loop

Open in new window

? But I'm getting the syntax wrong (getting "object doesn't support this method).

Any help?

Pawan Kumar

Refer this Microsoft url - <<selected values of a multiselect listbox by the value of the bound column>>

https://msdn.microsoft.com/en-us/library/office/ff823015.aspx

Hope it helps!

Rey Obrero (Capricorn1)

try this

Do Until rst.EOF
with me.Statuses
For varItm = 0 to .listcount -1
              if .column(0,varItm)= rst(0) then
                   .selected(varItm)=true
                   .value=.itemdata(varItm)
.listindex=varitm
end if
Next varItm
end with
rst.MoveNext
Loop

Rey Obrero (Capricorn1)

if the bound column is not the first column (0), replace 0 with appropriate column number,
column 1 is 0
column 2 is 1
column 3 is 2
etc..

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Michael Vasilevsky

ASKER

Thanks, I used:

Do Until rst.EOF

i = 0
Do Until i = ctl.ListCount
If CInt(ctl.ItemData(i)) = rst(0) Then
ctl.Selected(i) = True
End If
i = i + 1
Loop

rst.MoveNext
Loop

Michael Vasilevsky

ASKER

@Pawan Kumar Khowal that link tells me how to get but not how to set

Thanks all!