[AccessD] Listbox-type browse control for large dataset

Chris,

I have used another technique which might help you, that doesn't use a
subform. I don't populate the row-source attribute until the user enters 3
characters at least. Then I use those characters in a SELECT statement to
grab only the relevant rows.

1. User enters "Ful"
2. I build the rowsource:
    strSQL = _
    "SELECT PK, Surname FROM Employees WHERE Surname LIKE " & chr(39) &
me.combo & "*" & chr(39)     '<--- supplying the double quotes and asterisk
3. me.combo.rowsource = strSQL

No need to requery. Changing the rowsource does that automatically.
Typically this approach reduces the number of rows to something very
manageable while also satisfying the requirement that all 50K rows are
"accessible".

hth,
Arthur

On 8/2/07, A.D.TEJPAL <adtp at airtelbroadband.in> wrote:
>
> Chris,
>
>     My sample db named Form_SubformAsListBox might be of interest to you.
> It is available at Rogers Access Library (other developers library). Link -
> http://www.rogersaccesslibrary.com/OtherLibraries.asp#Tejpal,A.D.
>
>     You could adapt the underlying approach suitably, for your specific
> needs.
>
> Best wishes,
> A.D.Tejpal
> ---------------
>
> Form_SubformAsListBox (Sample db)
> Brief Description
> ====================================
>     This sample db demonstrates use of subform as list box.
>
>     Two alternatives are covered:
>     (a) Continuous subform type list box.
>     (b) Datasheet subform type list box.
>
>     For each alternative, three styles of  list box simulation are
> demonstrated as follows:
>     (a) Multi-Select - Extended
>     (b) Multi-Select - Simple
>     (c) Single Select
>
>     Selection behavior in each case is similar to that of typical normal
> style for the pertinent list box. Extraction of information regarding
> selected items is relatively more convenient as compared to a conventional
> list box.
>
>     Subform  based list box affords the added facility of convenient
> formatting & alignment. In datasheet based alternative, the user can even
> adjust the row height if required, so as to suit multi-line content.
>
>     Note:
>     (a) Overall performance regarding prompt rendering of highlight colors
> (based upon conditional formatting) is found to be best under Access 2003
> (even better than Access 2007).
>     (b) For multi-select (extended) list box based upon datasheet subform
> in versions other than A2K3 (i.e. A2K, XP, A2K7), highlight colors
> representing multi-selection take complete effect only when Shift key is
> finally released.
>     (c) For multi-select (extended) list box based upon continuous
> subform, the performance in versions other than A2K7 is found to be OK
> (though it appears to be best in A2K3). In Access 2007 however, there is a
> slight time lag in rendering the highlights, though it does not wait till
> the shift key is finally released.
>
> 5 - Version: Access 2000 File Format
>
> 6 - References: DAO 3.6
> ====================================
>
>   ----- Original Message -----
>   From: Christopher Jeris
>   To: Access Developers discussion and problem solving
>   Sent: Thursday, August 02, 2007 03:06
>   Subject: [AccessD] Listbox-type browse control for large dataset
>
>
>   -----BEGIN PGP SIGNED MESSAGE-----
>   Hash: SHA1
>
>   Hi everybody,
>
>   I have a form on which I'd like to provide a listbox-type browse control
> on a dataset with a large number of rows (tens of thousands).
>   Specifically, the user interface control for this dataset needs to
>   support the following interactions:
>
>   1. Allow the user to click on a row, or select it using arrow keys, to
>   bring that row up in a form for editing
>   2. Recenter the control's viewable area around a specific row which is
>   known by key (not by ordinal in the listbox's dataset)
>   3. Ideally, browse from top to bottom of the entire dataset using a
>   scroll bar, although this can be weakened if necessary
>
>   In our prototype, we are using a regular ListBox, and the problem is
>   that #2 -- that is, selecting a row using   box.Value = someRowKey
>   is too slow, perhaps almost a second with 50,000 rows.  Also, I have no
> idea whether there is a hard limit (2^16?) on the number of rows in a
> ListBox.
>
>   I have searched briefly for third-party ActiveX controls, but the only
>   likely candidate I've seen -- FarPoint ListPro -- doesn't seem to work
>   in Access, only standalone VB.  (As in, I tried it, and I can't get it
>   to work.)
>
>   At this point I'm looking at implementing "paging" by hand, that is,
>   binding a set of a few hundred records at a time to the listbox and
>   forcing the user to make transitions from one page to the next
> explicitly.
>
>   Can anyone suggest alternative ways to attack the problem?
>
>   thanks, Chris Jeris
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>