[AccessD] Slow loop through list box

What kind of ID are you using...a string?  I would recommend making a table
that has an Autonumber to represent each customer's ID, that should shorten
the length of their ID.

You can always go with the class collection method, but that might be a
little slower to run the query.

Drew

-----Original Message-----
From: David Emerson [mailto:davide at dalyn.co.nz]
Sent: Tuesday, September 23, 2003 11:23 PM
To: Access Developers discussion and problem solving
Subject: RE: [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

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