[AccessD] Listbox-type browse control for large dataset

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