[AccessD] Strategies for populating combo boxes

Shamil Salakhetdinov shamil at users.mns.ru
Tue Nov 7 06:19:36 CST 2006


<<<
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




More information about the AccessD mailing list