Solved

Combo box with data from table with over 260,000 records

Posted on 2012-03-30
21
252 Views
Last Modified: 2014-11-26
I need to have a drop-down box for users to enter a product number (100-585, 32792-10, 4-50, 77932-1) and then have the box open to that entry for them to view an additional attribute (the product name).  Using just the standard combo box functionality, there appears to be a record limit for what will appear and the list truncates.  How do I have the entire list available?

/tom
0
Comment
Question by:BENNERT9
[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
  • 5
  • 3
  • 3
  • +6
21 Comments
 
LVL 8

Expert Comment

by:gpizzuto
ID: 37786766
make your own control (textbox) that searchs for the first value available, each time you press a key (or better change the text value)
0
 
LVL 61

Accepted Solution

by:
mbizup earned 85 total points
ID: 37786773
Create a popup "search" form for the user to drill down to the desired product number/name - and return that product to the "main" form.

This type of search functionality can be done with combo or text boxes fitlering product records that are displayed in a listbox or subform in datasheet or continuous forms view, with a double-click event that closes the popup and returns the product to the main form.
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 37786785
one method would be to have a separate combo box with a rowsource of something like:

Select DISTINCT LEFT([YourFieldName], instr([YourFieldName], "-")-1) as temp
FROM yourTable

or you could use a textbox to enter the first series of numbers.

Then, use either the change event of that control, or a button to dynamically configure the combo boxes rowsource, something like:

Private Sub text_Filter_Change

    me.cbo_ProductNum.RowSource = "SELECT ProductNum, ProductName " _
                                                         & "FROM yourTable " _
                                                         & "WHERE [ProductNum] Like """ & me.txt_Filter & "*"""

End Sub
0
Increase your protection from Zero Day threats!

Running two Antivirus' is never a good idea.
Taking advantage of Multiple Security layers on the other hand can often save your hide.
See which top notch security software brands have been proven to happily coexist together.
Reduce your chances of becoming a statistic.

 
LVL 20

Expert Comment

by:clarkscott
ID: 37786979
I would suggest you think about having a combo box with anywhere near that amount of data.  If you want to make sure someone enters a correct value, I'd use the AFTERUPDATE event and run a query to see if the entered data is correct.

I'd never put that many items in a combo box (or list box).

Scott C.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37787130
see this link as reference

http://allenbrowne.com/ser-32.html

hth
0
 
LVL 20

Assisted Solution

by:clarkscott
clarkscott earned 249 total points
ID: 37787262
Ps... instead of AFTER UPDATE - do it in the BEFORE UPDATE (of the combo box) so you can set CANCEL = TRUE and stop the entry before it's accepted.

Scott C
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 166 total points
ID: 37787412
In some rare cases you could also filter the list...

For example do you really need *every* Product ever created listed?

You may like to "Filter" the products, ...if that is practical...
For example:
Only products made after 1990.
Only "Active" (not discontinued) products.
USA orders only see a list of "English" measurement parts.
Perhaps "Hazardous" products get listed separately.
Perhaps only "Credit Worthy" customer can see highly priced parts
...etc

JeffCoachman
0
 
LVL 75
ID: 37788039
Tom ... you are defeating the purpose of a combo box, with that many records ... unless you use some very specialized techniques.  But in the mean time, you can try this ... put this code in your Form Load event:

Private Sub Form_Load()
Dim x
x = Me.YourComboBoxName.ListCount

End Sub

This will force Access to populate the entire combo initially ..

mx
0
 
LVL 20

Assisted Solution

by:clarkscott
clarkscott earned 249 total points
ID: 37788092
... plus... depending on how fast your backend responds, as your users type in characters, Access (the combo) will try to find the match on the list (character per character as each is typed),  and this could cause a pause between each character input - which could be a real pain-in-the-neck for your users.  If they click a mistake, they would have to wait to discover their error.  ???

Scott C
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 37788118
That (clarkscott's post) is why I prefer to use cascading combos or a text box with a filter button.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 37788833
All that said, I'm running A2003 and I cannot find a limit to the number of rows a combo box will display ??
0
 
LVL 75
ID: 37788853
The limit I believe is ~65K ... that's in print somewhere ... pretty sure.

mx
0
 
LVL 20

Assisted Solution

by:clarkscott
clarkscott earned 249 total points
ID: 37788909
Plus... how much "memory" does it require to maintain that much info in a combo box??  Even when you read tons of data in a "datasheet view" mode... you're not loading ALL the data at once (like Excel).   You can read millions of records from Oracle, SQL Server on a datasheet view... but Access is NOT loading all the data and then showing it to you.   As you scroll, it's reads more data).  You don't see it happening... but that's how it works.

Scott C
0
 
LVL 75
ID: 37788933
That's what happens in a combo/list box ...  which is why - with a LARGE row source, it's seems the Scrolling never catches up (eventually it does).  So, doing the trick (thanks Marcus) I showed in the Form Load eliminates that ... in a combo box, and to some degree, but not completely ... in a List box.
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 37788954
That's also why the combo box will run slow (seemingly not responsive) while typing in characters.  It must find the match FIRST before it displays in the combo.  Thus, the user can type several characters before the match (MAY) be found.  I don't know about you, but I backspaced 3 times typing this comment ???).  I'm glad I didn't have to wait 3 seconds or longer to notice my typos.  

Scott C
0
 
LVL 44

Expert Comment

by:GRayL
ID: 37807892
Jeff pointed out in other traffic that Options/EditFind provides a box where you can insert a value.  It just so happens on my machine running A2003 with XP Pro and 1.5 gbytes of memory I have an upper limit of 32, 766 or (2^15) - 2  for that option.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 166 total points
ID: 37808155
Curiouser and Curiouser...

Inspired by Ray, I get this when I mouse over this setting in 2007:
Access 2007 limit
But like the rest of you, I still think that the 260,000 requirement here is too high...
0
 

Author Comment

by:BENNERT9
ID: 37808556
Thank you for all of the comments.  Nothing has solved my problem directly.  The comment from boag2000 got my hopes up but it apparently doesn't apply to combo boxes.  I really do need the large number of records for the client.  Ideally, they would key in the number in one field and then the corresponding data fields (product name, active ingredient) would appear to validate that they keyed it correctly.  They are discarded pesticides, so in theory, they could be collecting products that were produced in 1976, so I really can't date-range them.  I have created the filter box option (i.e. For 100-585, enter the 100 in one box, then tab to the 2nd box and type the 585, which does then allow for the fields to be be displayed), but that causes extra keystrokes for the clients which they indicated in the business requirements was something that they wanted to decrease in the rewrite.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37809225
Obviously there are exceptions to every rule.

In your case 260000 records just threw up red flags...
Can't you filter on the Category?
http://www.epa.gov/pesticides/about/types.htm

I mean, ...if your list is being truncated so you can't see all the records, ...it's no good for you anyway...

What about just displaying a textbox for them to enter a "partial" part number.
Then run a query off this value to return a wildcard match.
Then they can use the results of this query to find the exact PartNumber.

..Just a variation what what other Experts have posted...

JeffCoachman
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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 view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

751 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