Solved

Access 2003 Combo Box Table Look Up Display Limit?

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Email question 19 88
Combo box question 6 51
2 Global Vars, 1 List Box 4 29
How to build a logic for passwords according to initials? 13 40
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

739 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