[AccessD] Slow loop through list box

Thanks Drew,

The problem with the IN() solution is that because the max length of the 
SQL variable is 8000 chars and the average length of the ID is 8 that 
limits us to 1000 customers selected at a time.

I think that I might have to rethink when I update the table with the 
selected ID's.  Perhaps I could do it after they have finished selecting 
all customers.

David

At 23/09/2003, you wrote:
>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
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com