[AccessD] Strategies for populating combo boxes

DWUTKA at marlow.com DWUTKA at marlow.com
Tue Nov 7 16:28:18 CST 2006


I completely understand how often the callback function is called, however,
what I was explaining is that the process is identical to how the other
methods work, internally.

Take for example the Window APIs and their callbacks.  Enum windows will go
through all of the windows on your machine, and you can enumerate child
windows too.  I have a little utility that I wrote (to move windows that
have 'lost' their coordinates, and appear off the screen) that enumerates
ALL windows on your machine, including child windows.  It takes a split
second to run, and a lot of the processing time is in building the treeview.

Sometimes you have to take the hit with performance though.  The advantage
of a callback function is that you can present data on the fly, so if you
need to process something when it's is asking for values you can, it's just
going to be slower then a static array (or a collection, or another method
for a data source).

Drew

-----Original Message-----
From: Shamil Salakhetdinov [mailto:shamil at users.mns.ru] 
Sent: Tuesday, November 07, 2006 12:30 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Strategies for populating combo boxes

Drew and Gustav,

I will try to "cook" a sample where static array caching can't be used and
then a combo could result in datasheet form scrolling slowdown...

If I will not come with such a sample here in a month (no time currently to
trick around it) then the case I mentioned was the case of the bad callback
function design.

BTW, in MS Access online help is written: "The number of times your
user-defined function is called for acLBGetValue and acLBGetFormat to get
list entries and to format strings varies depending on the number of
entries, the user's scrolling, and other factors." 

"The user's scrolling" they say - be aware of that when using callback
functions - this is what I meant first of all...

--
Shamil 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of DWUTKA at marlow.com
Sent: Tuesday, November 07, 2006 6:42 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Strategies for populating combo boxes

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