[AccessD] Slow loop through list box

Just a thought, I would go in one of two different directions with this.
One method would be to set a Global string variable.  Then when I looped
through the selected items, I would add them to the string, comma delimited.
Then I would build a function that returns the string, and change the query
criteria to look like this: In(MyStringReturningFunction()).

The other approach would be to create a global 'collection'.  Then make a
class to hold the ID's.  Then I would add an instance of that class for each
ID that is selected, and set that instance's key in the collection to the ID
(if the id is numeric, the key has to be a string, so I would set it as
"ID:" & MyIDField.  Then I would write a public function that returned true
or false if that ID was in the collection or not (you can just use error
handling, and set a empty class variable to the collection with that ID, if
it returns, it exists, if not, it doesn't.).

I think the In() statement would be faster, especially if you have indexing
on that field, where you are querrying it, because I think it can use the
string in the In() clause against it's index tables, instead of having to go
through each record determining if it's true or false.

Drew 

-----Original Message-----
From: David Emerson [mailto:davide at dalyn.co.nz]
Sent: Tuesday, September 23, 2003 6:42 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Slow loop through list box


I have a listbox which is used to select clients for including in 
reports.  When the listbox is updated I have a separate text box that shows 
the names of the clients selected and the number selected.  At the same 
time I store the ID's of the clients in a table that is used to filter out 
the required client records. The initial list box has over 1800 names.  It 
is taking about 10 seconds to update the lists each time a client is 
selected.  If I rem out the adding of ID's to the table then the time 
reduces to about 3 seconds.  The main part of the code is -

     With Me!lstClientList
         For Each varItem In .ItemsSelected
             If .Selected(varItem) = True Then
                 strList = strList & .Column(1, varItem) & vbCrLf  ' Adds 
client name to selected list
                 intClientNo = intClientNo + 1                            ' 
Counts number of selected clients
                 rst.AddNew 
'Add client ID to table
                     rst!CustIDNo = .Column(0, varItem)
                 rst.Update
             End If
         Next varItem
     End With

Any thoughts as to how I can speed up the process?  The BE is SQL2000.


Regards

David Emerson
DALYN Software Ltd
25b Cunliffe St, Johnsonville
Wellington, New Zealand
Ph/Fax (877) 456-1205 

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com