McGillivray, Don [IT]
Donald.A.McGillivray at sprint.com
Mon Nov 6 22:35:34 CST 2006
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