?
Solved

Access 2003 Combo Box Table Look Up Display Limit?

Posted on 2013-01-29
3
Medium Priority
?
427 Views
Last Modified: 2013-01-29
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
0
Comment
Question by:jtflex
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 total points
ID: 38832918
see this link for possible solution

http://allenbrowne.com/ser-32.html
0
 
LVL 20

Accepted Solution

by:
GrahamMandeno earned 1000 total points
ID: 38832998
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
 

Author Closing Comment

by:jtflex
ID: 38833266
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

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question