David Emerson
davide at dalyn.co.nz
Wed Sep 24 00:29:24 CDT 2003
The ID is an Autonumber - legacy from synchronising is that it was randomly generated (hence the large average size. Currently I am storing them in a table as they are being selected, then joining that table to the full customer table to do the filtering. The slowness is in updating the table each time (see the code at the bottom of the message). David At 23/09/2003, you wrote: >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 >_______________________________________________ >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com