Solved

Access 2003 Combo Box Table Look Up Display Limit?

Posted on 2013-01-29
3
426 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 250 total points
ID: 38832918
see this link for possible solution

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

Accepted Solution

by:
GrahamMandeno earned 250 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

691 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