Selected Items in a Checkbox style Listbox

[33216CLC]

I use the code below to execute an event on the selected items in the listbox, however, it takes too long if too many items are in the listbox. The routine moves through each listbox item to check whether it is selected or not. I would like a routine that would capture only the selected items and not have to move through each list item to see whether it is selected.

''''''''''''''''''''''''
For i = 0 To List1.ListCount - 1
List1.ListIndex = i

sql = "select * from employee where company_id=" & Company_ID & _
"and [Full Name]='" & List1.List(List1.ListIndex) & "' order by employee.[Full Name]"
Set rsEE = dbPayroll.OpenRecordset(sql, dbOpenSnapshot)
If Not rs1.BOF Then
If List1.Selected(i) = True Then
<Statements>
End If
End If
Next i
'''''''''''''''''''''

Any ideas,
thank you

 

[sunaj]

Hi,

What takes up the time is not the list-loop, but opening the recordset. Why don't you:

-----------------------------------------------------------
For i = 0 To List1.ListCount - 1
If List1.Selected(i) = True Then

sql = &quot;select * from employee where company_id=&quot; & Company_ID & _
&quot;and [Full Name]='&quot; & List1.List(List1.ListIndex) & &quot;' order by employee.[Full Name]&quot;
Set rsEE = dbPayroll.OpenRecordset(sql, dbOpenSnapshot)
If Not rs1.BOF Then
<Statements>
End If
End If
Next i
------------------------------------------------------------
i.e. check first if the item is selected and then open the recordset if it is. In that way you'll only have to open a recordset for the selected item (and not for all as before).

Sunaj

 

[JohnYingling]

You also do not need listindex. That takes time. If my memory serves me right, can't you construct a list of Employess and use WHERE ...... IN list or something like that and get all records in one request.

For i = 0 To List1.ListCount - 1
    If List1.Selected(i) = True Then
    
        sql = "select * from employee where company_id="   &        Company_ID & _
        "and [Full Name]='" & List1.List(I) & "' order by employee.[Full Name]"
        Set rsEE = dbPayroll.OpenRecordset(sql, dbOpenSnapshot)
        If Not rs1.BOF Then
               <Statements>
        End If
    End If
Next i

http://WWW.VBCompare.Com

 

[33216CLC]

Sonaj,

I'll try that and let you know how it goes.

John,

I'll research the &quot;Where....IN List&quot; clause you mentioned. I am not familiar with it, but it sounds interesting.


Thank you, both.

 

[sunaj]

Hi Again

Yes I didn't look at your SQL statement before now.
Its much faster to:
------------------------------------------------------------
MySQL = &quot;&quot;
For I = 0 To List1.ListCount - 1
If List1.Selected(I) = True Then
MySQL = MySQL & &quot;'&quot; & List1.List(I) & &quot;',&quot;
End If
Next I
MySQL = Left(MySQL, Len(MySQL) - 1) ' Get rid of last comma

MySQL = &quot;select * from employee where company_id=&quot; & Company_ID & _
&quot;and [Full Name] IN (&quot; & MySQL & &quot order by employee.[Full Name]&quot;
Set rsEE = dbPayroll.OpenRecordset(sql, dbOpenSnapshot)
If Not rs1.BOF Then
'Statements here
End If
------------------------------------------------------------
Sunaj

 

[JohnYingling]

Careful. There may not be any SELECTED so

MySQL = Left(MySQL, Len(MySQL) - 1) ' Get rid of last comma

wlll blow up.
However you want to do it, use
IF List1.SelCount > 0
around, in whatever, your logic avoid problems.

if List1.SelCount = 0 then
.............No Selections
else
    MySQL = ""
    For I = 0 To List1.ListCount - 1
        If List1.Selected(I) = True Then
            MySQL = MySQL & "'" & List1.List(I) & "',"
        End If
    Next I
    MySQL = Left(MySQL, Len(MySQL) - 1) ' Get rid of last comma

    MySQL = "select * from employee where company_id=" & Company_ID & _
        "and [Full Name] IN (" & MySQL & ") order by employee.[Full Name]"
        Set rsEE = dbPayroll.OpenRecordset(sql, dbOpenSnapshot)
    If Not rs1.BOF Then
       'Statements here
    End If
End if

http://WWW.VBCompare.Com

 

[sunaj]

Yep, you're right John