Combo box limitation

Hi All, we have a combo box that is used to locate records on a form, we have auto expand set to true and when the user starts typing the initial wording, the rest gets filled in, and when they finally click enter the desired record appears on the screen.
Everything worked perfectly until... one day we realized our list of records is growing and the combo box somehow cannot handle it, since the list is sorted by name alphabetically, the last few letters don't get displayed on the screen.
Does anybody has an workaround to this limitation?
LVL 6
bfuchsAsked:
Who is Participating?
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.

Rey Obrero (Capricorn1)Commented:
one way to handle this, is to load/filter the rowsource of the combo box as you typed in the letter.

see this link for reference
http://allenbrowne.com/ser-32.html
0

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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Here is a trick:

Private Sub Form_Load()

Dim x
x = Me.YourComboBoxName.ListCount

End Sub

This will force Access to load the entire combo when the Form opens.

mx
0
bfuchsAuthor Commented:
Thanks for your replies.

@mx
tried that first, but didn't work, when I ran in debug mode the value of x only had 65k.

@capricorn1
saw that suggestion, will give it a try tom & let u know.

@All just forgot to mention, I would really appreciate the most efficient solution (even if that takes longer to program) as this is something that being constantly used in our app.

Thanks again
Ben
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
How many records are in the combo box row source?

In fact, seems I recall the max records for a combo box is around 65K.
0
bfuchsAuthor Commented:
the list currently over 100k.
if you're saying that the limit is 65, and till there I have no problems with anyway (up till letter N it works perfectly), for when exactly does your trick above apply?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well, the 'trick' applies I guess up to the combo limit - which again I think ... is 65K records.

mx
0
Jeffrey CoachmanMIS LiasonCommented:
Another way to look at this is to ask if you really need 100,00 items listed...
In other words do you really need every record in your entire table available for every search?
You can instead filter the items that the combobox displays. (For example:  Only Active Customers, Only Aluminum Parts, Only Stores in the USA, only Newly Hired employees, ...etc)

In very large systems (Government, E-commerce, ...etc) you simply enter a value in a text box and "search" for it.
For example the DMV, does not have a combobox with all Drivers Licenses, ...you give them your Drivers License number, and they search for you. (or your records)

In other words, the "Type Ahead" matching (The Autoexpand=Yes property of the combobox), is sometimes what slows down the functionality.  
So try turning this off.
You will still be alerted if the item is not on the list if you set the Limit To List property to YES.

JeffCoachman
0
bfuchsAuthor Commented:
@mx
according to current testing in my scenario, I have access up to record 65.5K regardless if I apply your trick or not, so again I'm not sure how will this help in the current issue.

@boag2000
Limiting the list is not something that can come in consideration at the moment.
Re autoexpand, I know that can cause the list to perform slower, but this is how they prefer to use it...however for the shake of testing, I tried to disable it, but it did not help with displaying further records then letter N, as described above (record #65.556).

I guess my remaining option here is to follow capricorn1 link..
0
Jeffrey CoachmanMIS LiasonCommented:
OK, keep us posted...
0
bfuchsAuthor Commented:
Preliminary testing shows working fine, would really need users input on that, however since I am planning to take vacation for the next two weeks, I'm accepting this as the correct answer, thanks again to all participants.
0
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.