Andy Lacey
andy at minstersystems.co.uk
Wed Sep 24 03:15:24 CDT 2003
Hi David Firstly I agree you should wait until selection is all done before updating the table. That'll save loads of time. Secondly I'd suggest not running any code at all when user makes a selection. Add a "Select" button or somesuch with much the same code behind it. The user can then make several selections (make sure multi-select enabled) and then hit the Select button. Your code will then be run a lot less often. Andy Lacey http://www.minstersystems.co.uk > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of > David Emerson > Sent: 24 September 2003 06:29 > To: Access Developers discussion and problem solving > Subject: RE: [AccessD] Slow loop through list box > > > 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 > > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/a> ccessd > Website: > http://www.databaseadvisors.com > >