David Emerson
davide at dalyn.co.nz
Sun Sep 28 04:03:48 CDT 2003
I ended up using Andy's suggestion below. Thanks everyone for all your input. David At 24/09/2003, you wrote: >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 > > > > > > >_______________________________________________ >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com