Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Re: Is there a recordlimit in an access combobox?

8 views
Skip to first unread message

Van T. Dinh

unread,
May 2, 2005, 6:31:48 AM5/2/05
to
The list is limited to 64K rows.

You need to use a set of cascaded Comboboxes or similar.

See The Access Web article:

http://www.mvps.org/access/forms/frm0028.htm

and Allen Browne's:

http://www.allenbrowne.com/ser-32.htm

--
HTH
Van T. Dinh
MVP (Access)


"jdj" <j...@discussions.microsoft.com> wrote in message
news:C86CFE8C-41FC-4FFF...@microsoft.com...
> I want to pick from a list of 150.000 records in a combo-box in Access
2003,
> but it only shows records from 'a' to 'k' (sorted alphabetically) - is
there
> a maximum limit on how many records can be used in a combobox? And if so,
is
> there a workaround?


Arvin Meyer

unread,
May 2, 2005, 6:49:51 AM5/2/05
to
"jdj" <j...@discussions.microsoft.com> wrote in message
news:C86CFE8C-41FC-4FFF...@microsoft.com...
> I want to pick from a list of 150.000 records in a combo-box in Access
2003,
> but it only shows records from 'a' to 'k' (sorted alphabetically) - is
there
> a maximum limit on how many records can be used in a combobox? And if so,
is
> there a workaround?

As Van mentioned 64K rows. A very fast work around is also using a search
form as in:

http://www.datastrat.com/Download/Search2K.zip
or:
http://www.datastrat.com/Download/ExpandingSearch2K.zip
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access


Mike Painter

unread,
May 2, 2005, 5:12:53 PM5/2/05
to
Van T. Dinh wrote:
> The list is limited to 64K rows.

I keep reading this but I have at least two clients that have well under 64k
*rows* and they stop seeing beyond about the "T"s in their combo boxes.
The informations is still there but the autofill stops.
E.G.
I type in "J" and get the first "J", JO gets the first JO etc, exactly as
expected.

I type in T nothing happens. You can type in "Thomas Mike" and get the first
"Thomas Mike" in the list but you can't see the second or third one.

This has been consistant for about ten years and only by geting the list
down to less than 10 k or so can the problem be solved.
I think there is a 64k buffer involved and it is not based on records.

>
> You need to use a set of cascaded Comboboxes or similar.
>
> See The Access Web article:
>
> http://www.mvps.org/access/forms/frm0028.htm
>
> and Allen Browne's:
>
> http://www.allenbrowne.com/ser-32.htm
>
>

AT @comcastdotnet Tom Wickerath

unread,
May 3, 2005, 2:27:06 AM5/3/05
to
To add a little to what Van and Arvin have already mentioned, you might want
to try the method shown starting at about the middle of this article:

Combo and list box controls
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacbk02/html/ODC_CookbookChapter8.asp

I haven't checked out Arvin's sample, so perhaps it is the same or similar....

Tom
_________________________________

"Van T. Dinh" wrote:

The list is limited to 64K rows.

You need to use a set of cascaded Comboboxes or similar.

See The Access Web article:

http://www.mvps.org/access/forms/frm0028.htm

and Allen Browne's:

http://www.allenbrowne.com/ser-32.htm

--
HTH
Van T. Dinh
MVP (Access)

___________________________________

Van T. Dinh

unread,
May 4, 2005, 11:07:03 AM5/4/05
to
Mike

I tested in A2K2 by populating a ComboBox RowSource with Records from
100000-record Table and the row number stopped exactly at 65536, i.e. 64K
rows.

It may take a bit of time to populate the RowSource. Try using the
following code in the Form_Load Event to fully populate the RowSource

Dim lngTemp As Long ' Only used for populating

lngTemp = Me.ComboBox.ListCount

After that, check the ComboBox and see if you can pick up 64K rows.

--
HTH
Van T. Dinh
MVP (Access)

"Mike Painter" <mddotp...@sbcglobal.net> wrote in message
news:pJwde.455$X21...@newssvr21.news.prodigy.com...

Mike Painter

unread,
May 4, 2005, 4:55:51 PM5/4/05
to
Van T. Dinh wrote:
> Mike
>
> I tested in A2K2 by populating a ComboBox RowSource with Records from
> 100000-record Table and the row number stopped exactly at 65536, i.e.
> 64K rows.

Create the source so that it shows a reasonable amount of information in the
view.
Last Name, First name, Phone number etc. Sort by last name. (Mine arrives in
a last name first name format.)

Tap the letter "D" and the combo will behave as expected.
Tap the letter "T" and I suspect you will find that only that letter appears
in the box.
The record is there and if there is only one "Thomas Mike" then entering
"Thomas Mi" will get that record.
There is no way to get the second "Thomas Mike" on the list. It does not
autofill as it does in the lower letters, nor do the records display in the
combo.
The client is currently using A2K or higher.


Van T. Dinh

unread,
May 4, 2005, 6:16:14 PM5/4/05
to
Did you try the ListCount as mentioned in my previous post to fully populate
the list?

--
HTH
Van T. Dinh
MVP (Access)


"Mike Painter" <mddotp...@sbcglobal.net> wrote in message

news:rFaee.1094$X21...@newssvr21.news.prodigy.com...

SOETENS CLAUDE

unread,
May 5, 2005, 6:59:46 AM5/5/05
to

"Van T. Dinh" <VanThi...@discussions.microsoft.com> a écrit dans le
message de news: %23zMKloL...@TK2MSFTNGP15.phx.gbl...

msnews.microsoft.com

unread,
May 6, 2005, 3:48:52 AM5/6/05
to
yuxiane
"Mike Painter" <mddotp...@sbcglobal.net> 写入邮件
news:rFaee.1094$X21...@newssvr21.news.prodigy.com...

DawnTreader

unread,
Oct 2, 2006, 5:15:02 PM10/2/06
to
Hello

just bookmarking this thread for later reference.

Albert D. Kallal

unread,
Oct 2, 2006, 6:49:54 PM10/2/06
to
I believe the limit is 64,000

However, really, if you have respect for your users, then any combo box with
more then a few 100 entries is going to be torture.

So, the limit of 64,000 is so far beyond what a reasonably user can
tolerate, that it is really is not much of a limit anyway.....

when lists start to get that large, then you likey need to build some type
of form to seach. I give some ideas here:

http://www.members.shaw.ca/AlbertKallal/Search/index.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOO...@msn.com


Tom Wickerath

unread,
Oct 2, 2006, 6:54:02 PM10/2/06
to
Hi Dawn,

I recommend bookmarking it using Google Advanced Groups, to find the
message. Here is a link to the search form:

http://groups.google.com/advanced_search?q=%22+group%3Amicrosoft.public.access.*&num=50&as_mind=1&as_minm=1&as_miny=2005&as_maxy=2006

and here is a link to this thread that is the better choice for bookmarking:

http://groups.google.com/group/microsoft.public.access/browse_frm/thread/8a6a4e70a19efb8b/c689964ab6c132f8

The reason I say this is that threads on server news.microsoft.com tend to
"age off" after some period of time. The fact that this thread was marked as
an answer by the OP (Original Poster) means that it will not age off as
quickly. For anyone else reading this, the only way to see that a reply was
marked as an answer is to use the MS Web Portal to view the message:

http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.access&mid=00b54c59-57e2-4a35-9931-fc3dc556a5f4

If you are viewing this message with any other newsreader (Outlook Express,
Thunderbird, etc.), then you will not see the "marked answer" status that I
mentioned.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

0 new messages