Solved

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

Posted on 2012-03-30
21
213 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
  • 5
  • 3
  • 3
  • +6
21 Comments
 
LVL 8

Expert Comment

by:gpizzuto
Comment Utility
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
Comment Utility
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 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
 
LVL 20

Expert Comment

by:clarkscott
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
see this link as reference

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

hth
0
 
LVL 20

Assisted Solution

by:clarkscott
clarkscott earned 249 total points
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
... 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now