By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,693 Members | 1,646 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,693 IT Pros & Developers. It's quick & easy.

How to Increase Combobox Limit

100+
P: 124
I've found that a combobox will only display 10,000 records. Is there a way to increase this limit? I want my users to be able to search for people by name and I thought it'd be easiest for them to have a combobox and as they type in the name it jumps to the record that most closely matches what they're typing in (I forget what this feature is called).
Jul 12 '10 #1

✓ answered by jimatqsi

There is not a limitation of 10,000 records in a combobox. You should be able to handle a lot more than that. But, in a multi-user system you want to make sure you are using a snapshot type query to for your record source to be sure you don't lock up a bunch of records and give the other users unnecessary hourglasses.

Allen Browne posted a nice tip about comboboxes a long while ago. His idea is basically to leave the combo box empty until the user types something, then go fill it match what has been typed. Check this out:
http://allenbrowne.com/ser-32.html

Jim

Share this Question
Share on Google+
8 Replies


Christopher Nigro
P: 53
Hmmm... even though it goes to the records while typing, 10,000 records is a bit much to work with. Could you add other controls to refine the search criteria further? The fact that you are hitting the limit may be telling you something.
Jul 12 '10 #2

100+
P: 124
Is a bit much to work with understandably. I do have another control where they can lookup the person by their id. However, then they'd have to know the person's id each time and usually it's easier to type in the name. I could have a free-form textbox but then I'd have to specify how to enter the name and, moreover, more than one person could have the exact same name. The combobox includes additional information to distinguish between people.
Jul 12 '10 #3

Christopher Nigro
P: 53
I don't know, maybe some sort of A-Z control where they can pick last names starting with [some letter] first and then have those results fill the combobox. Just thinking off the top of my head...
Jul 12 '10 #4

Expert 100+
P: 1,240
There is not a limitation of 10,000 records in a combobox. You should be able to handle a lot more than that. But, in a multi-user system you want to make sure you are using a snapshot type query to for your record source to be sure you don't lock up a bunch of records and give the other users unnecessary hourglasses.

Allen Browne posted a nice tip about comboboxes a long while ago. His idea is basically to leave the combo box empty until the user types something, then go fill it match what has been typed. Check this out:
http://allenbrowne.com/ser-32.html

Jim
Jul 12 '10 #5

missinglinq
Expert 2.5K+
P: 3,532
The "Wonder from Down-Under" also has a 'find-as-you-type' hack giving AutoExpand ability to a textbox:

http://allenbrowne.com/AppFindAsUType.html

Linq ;0)>
Jul 12 '10 #6

100+
P: 124
@jimatqsi
I really like his idea except that I can't get it to work. After I type in the first three letters (fer) I get an error stating: Invalid column name 'fer*'. I've reworked Allen Browne's code to suit my purposes (although it still didn't work when I had it almost verbatim). This is what I have:

Expand|Select|Wrap|Line Numbers
  1. Const conNameMin = 3
  2. ----
  3. cboSelectName_Change()
  4.     Dim strName As String
  5.  
  6.     strName = Me.cboSelectName.Text
  7.  
  8.     If Len(strName) >= conNameMin Then
  9.         Me.cboSelectName.RowSource = "SELECT * FROM vwStudentNames WHERE LastName Like """ & strName & "*"" ORDER BY FullName"
  10.     Else
  11.         Me.cboSelectName.RowSource = ""
  12.     End If
  13.  
Jul 13 '10 #7

Expert 100+
P: 1,240
Hmmm, I don't see the problem. Might be that "*"" ORDER by FullName" should be "*""" ORDER by FullName"
Try this
Expand|Select|Wrap|Line Numbers
  1. dim strSQL as string
  2. strSQL = "SELECT * FROM vwStudentNames WHERE LastName Like """ & strName & "*"" ORDER BY FullName"
  3. msgbox strSQL
  4. Me.cboSelectName.RowSource = strSQL
That will show you the SQL in a message box before it executes.

Jim
Jul 13 '10 #8

100+
P: 124
@jimatqsi
I got it to work. Because I'm working in SQL server I forgot I had to replace the * with %.
Jul 14 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.