Solved

Access 2003 Combo Box Table Look Up Display Limit?

Posted on 2013-01-29
3
424 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

749 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