Access 2010

We had been using a combo box to look up records.  For 15 years This combo box
sorted all the records alphabetically on customer name. All of a sudden
we can only view up to customers beginning with " y ".  I could not scroll down
any further. Although I had thought something happen to all the record starting
with " z " I was  reassured when I looked in the table and varified that all the
records beginning with  "z" were in fact there. This look up has just about 65,900 records.
Are there limitation I am not aware about ?

Thanks
BillxxxxAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
You have reached the limits of the combo.  Generally you shouldn't use anything with more than 10,000 rows anyway.  There are some code options that take a couple of characters and then requery the RowSource or there are other options.  Let us know what you want to do.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<This look up has just about 65,900 records.>>

 As Pat said, you've surpassed the limit of the combo box, which if I remember right is about 64,000.

 I would add a filter to the form for the combo based on customer type if possible.  If you can't do that, then typically a row of tabs with one for each letter (like a Rolodex) works well.

 This is along the lines of what Pat was suggesting, but rather than typing into the combo first, you click on a tab and are then limited to customers starting with a single letter. Doing that, you're not currently in the control when you requery the row source.

 I've always felt that approach to be a little less problematic, although it does take up some screen real estate.

Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BillxxxxAuthor Commented:
I just set the combo box not include dates before 01/01/2002. That worked and
we now only only can't view a years worth of old records.
Is the limit 65,000 approx? I have read a lot of books on access but never read
that.  

Thanks
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

BillxxxxAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 250 points for PatHartman's comment #a41258806
Assisted answer: 250 points for JDettman's comment #a41259026
Assisted answer: 0 points for Billxxxx's comment #a41259068

for the following reason:

I made a mistake in accepting mine. I was just wanted to send a comment on
how i fixed it.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I have read a lot of books on access but never read
that.  >>

 That's been a restriction since version 1.x and I've seen it in a number of places, but I'll be darned if I can find one now (not surprising, the MSKB is all but gone now :(  )

 and it may be closer to what Pat said and most likely 65,535, although 64,000 sticks in my mind for some reason.

 In any case, you hit the limit<g>.

 and on the date, one of the things I usually do on historical stuff is put a "Back to date" and default it to one year in the past.

 That let's people go back farther if they need to, but keeps performance up for day to day stuff.   In this case that would not totally work because of the limit, but rather than sticking to a specific date, this would give your users a little more flexibility.

 You could warn them as well if not all are displayed by checking the list count property if they went back too far.

Jim
Nick67Commented:
The number of characters in memo fields and in  textboxes is 64K (65,535)
I suspect that .ListCount also maxes out at 64K

You should be able to test that out easily enough with a MsgBox of
MsgBox Me.MyComboBox.ListCount

This is an old trick for working around the limitation
http://allenbrowne.com/ser-32.html
It requires users to type in a number of characters (you decide how many) before the combo loads its now-filtered .RowSource

Note that you'd want to probably do these things as optimizations anyway.
I suspect your form loads slowly.
Comboboxes get initialized before the form does
One speed trick is to leave the .RowSource empty until the initial GotFocus occurs.
Another is to have cascading controls

If you have a combo with A-Z, and the user picks one, and then moves to the next where now only records starting with the previously selected letter are loaded, that cuts things down a lot.

Fewer records loaded means more responsive UI.

And really -- the user doesn't actually scroll 64K records, I suspect.
They start typing in the first few characters and then scroll for something that big :)
BillxxxxAuthor Commented:
All of the responses were good and very helpful. The note about speed interests
me because i never saw this as a problem. The combo Box I am talking about
Looks for customers work orders. Which is why it reached it's limitation.  the combo
box includes Customer, Work order number, date, and comments. This is also sorted
alphabetically  by customer.  When looking up a particular work order there are
many ways to view the info before selecting it. I never felt as though it was slow.  
However, I do use a lot of columns in many other Combo boxes. Should I anticipate
some problems?  I fixed my current problem by putting a limitation based on date. I then added
another combo box  called "Old Work orders" which would bring up everything prior to the date
set in the previous combo box. This of course will be a problem again some time in the near future Unless
i make the date a variable based on current date. I know it will require some code. Any thoughts?

Thanks
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I'd say two combo's cascaded; one for customers, then one for work orders, with the second being filtered by a "back to: mm/dd/yy" date to keep the speed up.

Jim.
PatHartmanCommented:
Decide how many years back you need to go on a regular basis.  For example, 5 years
Where SomeDate > DateAdd("yyyy", -5, Date())

Or, use a date range by adding two new controls to the form.  In this case, you'll need to verify that the date range is not longer than what you will allow.

If DateDiff("yyyy", Me.txtFromDT, Me.txtThruDT)) > 5 Then
    Msgbox "The date range must be no more than 5 years",vbokOnly
    Exit Sub
Endif

And the Where clause
WHERE SomeDate Between Forms!yourform!txtFromDT AND Forms!yourform!txtThruDT
Dale FyeOwner, Developing Solutions LLCCommented:
With long combo lists, I use a technique similar to Jim's comment above, but instead create a combo box which I had behind the label associated with your clients.  First, cut that label and then paste it back above the clients combo.  Then create a new combo, send it to back, behind the label.  Then set the RowSourceType to Value List and add in some options as you can see in the attached file.  Then modify the AfterUpdate of that 2nd combo box so that it sets the appropriate criteria and assigns a new RowSource for the clients combo box, sets the focus to the clients combo, and then drops it down.  This saves on the screen real estate needed for the filter.
LongCombos.accdb
Nick67Commented:
I have a combobox that would ostensibly hold ~6000 rows if I permitted that.
But the point of a combo box is to permit point-and-click, and to not require either scrolling or typing.
For that, you have to keep things down to around 30 rows.

So, I have a series of three comboboxes.
The first is a general categorization of the 6000 items
The second is a sub-categorization of each general category
The third is the item

The user selects one of ~30 general categories
The second box is then cascaded updated with its data and has around 30 items
The user chooses something from it
The third box is then cascade updated and has 20-50 items.

Three clicks, but little or no scrolling and typing.
30 x 30 x 30 = 27000 items cut down to three click.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.