Access 2003 Combo Box Table Look Up Display Limit?

I have an access data base that has street addresss that the user can seach in a query.

The street addresses are stored in an Oracle database.  

I am using a form that has a combo box linked to a Access query that returns those addresses from the Oracle database.  I have Auto Expand set to "yes'' and Limit To List set to "Yes".

The problem I have is that as the user types in the address, the drop down list of choices works only for the 1st 60,000 records.  Then the display of choices stops.  The user can still type in the address after that limit and it does prefill what it thinks the user wants, but the display is no longer focused in that area or choices.

Is there a limit to displaying records in combo box?  Is there a work around or am I missing some kind of setting that is needed when the amount of choices is very large?

Thanks,

Joe
jtflexAsked:
Who is Participating?
 
GrahamMandenoConnect With a Mentor Commented:
Hello Joe

The RowSource of a combo box is limited to 64K rows.  However, I would expect that pulling anything like that number of records in from a linked Oracle table would result in unacceptable performance.

Perhaps a workaround, both for performance and the 64K limit, would be to have a textbox into which the user types the first few characters of the address, and populate the RowSource of the combo box with only the addresses starting with that "stub".

You can actually do this by using the combo box itself for typing in the first characters, but start with an empty RowSource and only load the RowSource when the number of characters typed reaches a certain length.  Allen Browne has an article with example code here:
http://www.allenbrowne.com/ser-32.html

Good luck!
Graham [Access MVP 1996-2013]
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
see this link for possible solution

http://allenbrowne.com/ser-32.html
0
 
jtflexAuthor Commented:
Since you both pointed me to this solution I guess that's the direction I need to go.  Thanks and Thank you Grahnam for the explanaination.

Joe
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.