Combo list being limited

[Guest]

Access 2003. Table of personal information with about 100,000 records
(people). Have form with combo box in form header to look-up individual,
after which selection leads to display of all personal details for that
individual. The combo box is based on a query which I have created in design
view, and it displays four columns ( LastName, FirstName, Middle Initial,
HomePhone) . If I run the query and view recordset in datasheet view, all
records display properly.

My problem is the drop down of the combo box will not display all the
records. In effect I only get LastNames from A to P, and the rest will not
display, even after considerable patience, and waiting. Is there a setting
which limits the available records which can be displayed by a combo box?
Another thought I have is that this might be a memory issue. It seems to me
though that the 512MB I have should not be challenged by this.

The query looks across a LAN to the table, but on the computer where the
table resides, the problem also exists. I only throw this in to add
information that my inexperience fails to recognize as pertinent.

The great help I get here continues to be appreciated.

 

[Mark via AccessMonster.com]

Combo boxes are not made to display that many rows. There's a good
discussion you can search for that explains how to use an unbound combo box
and then assign a rowsource as users type in it. This way, your combo box
will only display the records that start with the values the user types.

For instance, you type "C" and your combo box rowsource is changed to show
only records that start with "C". If this still results in too many
records, you can wait until two or three letters are typed, which will
further limit the possible matches.

 

[Ken Snell [MVP]]

A combo box's dropdown list is limited to 65536 records. All records beyond
that number are "omitted". (List boxes have the same limit.)

If you indeed need to have "access" to all the records, then you will need
to use a filtering textbox or combobox to allow the user to "filter" the
list based on entering the first one or two characters, and then let the
combo box show just the filtered list.

Arvin Meyer (MVP) has some sample databases that you can download for one
approach. See ExpandingSearch97 and ExpandingSearch2K at
http://www.datastrat.com/Download2.html

Allen Browne (MVP) has another approach at his website:
http://members.iinet.net.au/~allenbrowne/ser-32.html

I have put together an approach that uses code to smoothly switch back and
forth between a textbox and combo box without the user needed to move from
one to the other, but I have not put it into a sample database yet. It uses
code to test each character entered into a textbox to see if the combo box's
item list would be < 65536, and if yes, it "switches" to a combo box with
the entered characters already showing in the combo box; and if the user
removes characters such that the list grows beyond 65536, it "switches" back
to a textbox. If this should be of interest, we can discuss how I might
prepare it for you.

 

[Guest]

Thank's Mark. Great guidance. Direct to the appropriate treasure chest of code.

 

[Guest]

Thanks Ken. My current problem solved with your great guidance. Your
personalized solution to morph back and forth between list and combo boxes
sounds very interesting. If okay, I may return for further guidance, when I
am not under time pressures, to discuss your unique solution.

I appreciate your superb assistance.

 

[Ken Snell [MVP]]

Thanks Ken. My current problem solved with your great guidance. Your
personalized solution to morph back and forth between list and combo boxes
sounds very interesting. If okay, I may return for further guidance, when
I
am not under time pressures, to discuss your unique solution.

Sure... I'll monitor this thread ...