How to Increase Combobox Limit

[bullfrog83]

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).

[Christopher Nigro]

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.

[bullfrog83]

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.

[Christopher Nigro]

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...

[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

[missinglinq]

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)>

[bullfrog83]

@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:

Const conNameMin = 3
----
cboSelectName_Change()
    Dim strName As String
 
    strName = Me.cboSelectName.Text
 
    If Len(strName) >= conNameMin Then
        Me.cboSelectName.RowSource = "SELECT * FROM vwStudentNames WHERE LastName Like """ & strName & "*"" ORDER BY FullName"
    Else
        Me.cboSelectName.RowSource = ""
    End If
 

[jimatqsi]

Hmmm, I don't see the problem. Might be that "*"" ORDER by FullName" should be "*""" ORDER by FullName"
Try this

dim strSQL as string
strSQL = "SELECT * FROM vwStudentNames WHERE LastName Like """ & strName & "*"" ORDER BY FullName"
msgbox strSQL
Me.cboSelectName.RowSource = strSQL

That will show you the SQL in a message box before it executes.

Jim

[bullfrog83]

@jimatqsi
I got it to work. Because I'm working in SQL server I forgot I had to replace the * with %.