[AccessD] Strategies for populating combo boxes

Hi Don and Shamil

One method to speed up a callback function is to use a static array as the source. When initializing, check if the array is empty - if not, fill it. Now, whenever the combo or listbox is requeried and that happens very often, the callback function reads from the static array which is very fast.

Here's an example:

Public Function OrderListPrint( _
  ctl As Control, _
  ID As Long, _
  row As Long, _
  col As Long, _
  Code As Integer)
  
  Static astrPrint(1 To 7) As String
  
  Dim varValue            As Variant
  Dim lngCount            As Long
  
  Select Case Code
    Case acLBInitialize
      If Len(astrPrint(1)) = 0 Then
        ' Fill array.
        For lngCount = 1 To 7
          astrPrint(lngCount) = GetMsg("SaleId90" & lngCount)
        Next
      End If
      varValue = True
    Case acLBOpen
      varValue = Timer
    Case acLBGetRowCount
      varValue = 7    ' Rows.
    Case acLBGetColumnCount
      varValue = 2    ' Columns.
    Case acLBGetColumnWidth
      varValue = -1   ' Column width.
    Case acLBGetValue
      If col = 0 Then
        varValue = row + 1
      ElseIf col = 1 Then
        varValue = astrPrint(row + 1)
      End If
    Case acLBGetFormat     ' Format the data.
      ' varValue = strFormat      
    Case acLBEnd
      ' Do something when form with listbox closes or
      ' listbox is requeried.
  End Select
  
  OrderListPrint = varValue
    
End Function

/gustav

>>> shamil at users.mns.ru 07-11-2006 13:19:36 >>>
<<<
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