DWUTKA at marlow.com
DWUTKA at marlow.com
Tue Nov 7 09:42:06 CST 2006
ALL combos/listboxes are doing the same thing as one populated by a callback function. It's just simply how a listbox/combobox 'window' works. All of the calls to a callback function are just simply being handled by an internal callback when one of the other three methods are used (recordsoure, fieldlist, value list). If you are experiencing slowdown in your callback function, you need to look at when your callback function is doing. If you are requerying at ANY point other then the initialization, then there is a problem, because that takes time. Drew -----Original Message----- From: Shamil Salakhetdinov [mailto:shamil at users.mns.ru] Sent: Tuesday, November 07, 2006 6:20 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Strategies for populating combo boxes <<< It seems like an interesting option. >>> Don, Yes, that is an interesting and useful option but be careful with combos having many entries filled by callback functions while using them in datasheet/continuous view forms - AFAIKR such combos are refilled every time they get shown while you're scrolling rows - as a result you get "brain damaged" datasheet forms reacting very slowly while scrolling them: if you, e.g. have a combo, which is filled by callback function in half a second them for a datasheet form with 10 visible rows this combo's filling will take ~5 seconds when you push [PgDn]/[PgUp]... Talking about that from memory - last time I used combos with callback functions in MS Access 97 and I had met this nasty side-effect effect mentioned above. Did they (MS) fix the issue in MS Access 2000/XP/2003/2007? - I doubt they did but I can be wrong and therefore my above remark has to be retested... Temporary tables refilled on demand are usually satisfactory when used as a base of dynamic combos. Of course these temp tables should be kept in dedicated temp databases, which should be automatically compacted once in a while... Temp tables or dynamic on demand SQL used for combos' rowsource when combos get focus of user typed a few chars are not the source of the "brain damaged datasheet" side effect - the easy guess here is that MS Access "knows" (/get cached) all the entries of such combos and therefore it (MS Access) is able to go through all the visible rows and to keep shown/clear combos' values - in the case of callback functions it (MS Access) have to get combos' entries for every combo of every (newly) displayed row.... -- Shamil -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray, Don [IT] Sent: Tuesday, November 07, 2006 7:36 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Strategies for populating combo boxes Wow. I stepped away for a couple of days and find a full blown discussion in my wake. There's only one table and it has about 1.1 million records and 35 columns, only about 10 of which contain data that they want to be able to select from the combos. I settled on a temp table to hold the desired lists. Col 1 has the control's name, col 2 the value. The report selection form sets the rowsource for each combo on open. Takes only a few seconds. I added a routine to populate the temp table upon demand whenever the main table has been refreshed (about once per quarter, I'm told.) So far, there are no more than about 27K rows in the largest combo list. The others are in the tens or hundreds. I'm glad to know of the limit, though. I'll be able to warn of a potential for difficulty there. Drew, thanks for the idea about callback functions. And, JC, thanks for the warnings about comprehending them. I've looked at them before and didn't quite get it. I feel better knowing that even you found them head-twisting. I'll have another go. It seems like an interesting option. Thanks to all for the suggestions! Don -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of DWUTKA at marlow.com Sent: Friday, November 03, 2006 3:11 PM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Strategies for populating combo boxes Oh, well I only use a callback when it's called for. (pun intended). Most common functions of a callback are just fine for 99% of what you need a combo for. The callback functionality is best for that other 1%. For instance, I wrote a utility that let you print or preview reports in a database. Using a value list runs into trouble if you have too many reports, because it can only be ~2048 characters in length. A callback did the trick. Also in tricky situations, like the one described in this thread, callbacks provide better control of a combo/listbox. Drew -----Original Message----- From: JWColby [mailto:jwcolby at colbyconsulting.com] Sent: Friday, November 03, 2006 5:02 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Strategies for populating combo boxes LOL. And I did and have, but to actually understand it... I was trying to generalize it. To make a callback where one callback could be used for more than one object, with more than one datasource. It got ugly pretty quick. If you have 500 combos in a big app and wanted to use a callback for many of them, it gets painful building a new one for each object. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of DWUTKA at marlow.com Sent: Friday, November 03, 2006 5:21 PM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Strategies for populating combo boxes Access 97's help file was a pretty easy read to do it, along with a sample function. Drew -----Original Message----- From: JWColby [mailto:jwcolby at colbyconsulting.com] Sent: Friday, November 03, 2006 4:17 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Strategies for populating combo boxes Cool! Good to know. And are you sure that combos are limited to 65K? I seem to remember actually running into the 32K limit, but it was awhile ago so don't sue me if I'm wrong. I do know that they slooowed waaaaaay doooowwwwwn at that size. Now you can talk him through a callback function. There's a subject not for the faint of heart (at least to truly understand). John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of DWUTKA at marlow.com Sent: Friday, November 03, 2006 4:43 PM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Strategies for populating combo boxes Combos are limited to 65k. But I just whipped together a quick routine to add a long integer to a collection. In debug mode, without external indication of how fast it was going, I stopped it for after about 20 seconds, and it had millions in the collection already. Populating the display with the collections count property ran over 65k in about a minute. I would say the limit is closer to 2^31. Drew -----Original Message----- From: JWColby [mailto:jwcolby at colbyconsulting.com] Sent: Friday, November 03, 2006 2:50 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Strategies for populating combo boxes Drew, I was going to suggest this, however IIRC (and I was going to test this the other day), collections are limited to 2^15 items in the collection (~32 k items). I seem to remember that he has some large tables. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of DWUTKA at marlow.com Sent: Friday, November 03, 2006 3:25 PM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Strategies for populating combo boxes Build a collection, pull the records once to fill the collection, then populate the combos from a Callback function (which would get the data from the collection, vs. the recordset) Drew -- 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/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com