Combo Box Filtering

[jamesnkk]

I have create a combo box, which show all student names in the 1st column, If I want to find name call Jack, I would type the frist few character say JAC, automatically the combo box will point me to the nearest name that start with JAC,

How can I only show those students names that start with JAC..., in the combo box list, and hide the rest of the student names that not start with JAC.

[hariharanmca]

I have create a combo box, which show all student names in the 1st column, If I want to find name call Jack, I would type the frist few character say JAC, automatically the combo box will point me to the nearest name that start with JAC,

How can I only show those students names that start with JAC..., in the combo box list, and hide the rest of the student names that not start with JAC.

for this better you can use a listview and textbox because if you want to do that in comboBox you have to clear the combo list and there may clearing combo text also.

so, better you can use a listview and textbox

[jamesnkk]

for this better you can use a listview and textbox because if you want to do that in comboBox you have to clear the combo list and there may clearing combo text also.

so, better you can use a listview and textbox

I know how to build this in a List box, but may not suitable in my current form development, would you mind explain what you mean by clea the combo list and combo text.

[ADezii]

I have create a combo box, which show all student names in the 1st column, If I want to find name call Jack, I would type the frist few character say JAC, automatically the combo box will point me to the nearest name that start with JAC,

How can I only show those students names that start with JAC..., in the combo box list, and hide the rest of the student names that not start with JAC.

'You can programmaticall y change the RowSource Property of the Combo Box. Assuming a Combo Box name of cboTest with Column Count = 1, Bound Column = 1, RowSourceType = Table/Query, and a [FirstName] Field in tblEmployees:

Dim MySQL As String
MySQL = "SELECT tblEmployees.FirstName FROM tblEmployees WHERE " _
        & "tblEmployees.FirstName Like 'JAC*' ORDER BY tblEmployees.FirstName;"
 
Me![cboTest].RowSource = MySQL
Me![cboTest].Requery

Output: Produces only First Names that begin with JAC...

[jamesnkk]

'You can programmaticall y change the RowSource Property of the Combo Box. Assuming a Combo Box name of cboTest with Column Count = 1, Bound Column = 1, RowSourceType = Table/Query, and a [FirstName] Field in tblEmployees:

Dim MySQL As String
MySQL = "SELECT tblEmployees.FirstName FROM tblEmployees WHERE " _
        & "tblEmployees.FirstName Like 'JAC*' ORDER BY tblEmployees.FirstName;"
 
Me![cboTest].RowSource = MySQL
Me![cboTest].Requery

Output: Produces only First Names that begin with JAC...

Thanks for the code, but in your sql statement, you defined Jac, what I mean is just an example user may type in jac or Pet or any characters, how would I put into the sql ?

[NeoPa]

James,
What you're asking for is a redefinition of the ComboBox object itself. Alternatively, handle the keypresses. You will discover just how many different situations need to be handled for it to work properly.
I'm afraid the short answer is that this is not supported.

[jamesnkk]

James,
What you're asking for is a redefinition of the ComboBox object itself. Alternatively, handle the keypresses. You will discover just how many different situations need to be handled for it to work properly.
I'm afraid the short answer is that this is not supported.

Thanks, you understand my question, I got this idea which I saw an application was able to handle it. You are right, When I try different method, I realize th hardest part was the keypress. Adezil also provide a very good solution

[Killer42]

Thanks, you understand my question, I got this idea which I saw an application was able to handle it. You are right, When I try different method, I realize th hardest part was the keypress. Adezil also provide a very good solution

Your simplest option might be to have a separate textbox where the user types the search string. Each time the value is changed in the textbox, requery the combobox with the modified WHERE clause.

Question for the experts - does the Access combobox have a Sorted property, as in VB6? I couldn't see one.

[NeoPa]

I read your post Killer and thought 'He's ripped that from my post.'
Then I reread my post and remembered I'd taken that idea out :sheepish grin:
A problem with that idea is that getting access to the characters passed is not too easy until BeforeUpdate is triggered.
You can do it, but it's very fiddly.

A ComboBox doesn't have a sorted facility, but the RowSource can easily include a sort (Either an ORDER BY if it's SQL or sorting within a QueryDef - Tables are more complicated :().

[Killer42]

I read your post Killer and thought 'He's ripped that from my post.'
Then I reread my post and remembered I'd taken that idea out :sheepish grin:

This is not the first time we've been guilty of thinking alike.
It's nice to meet another intelligent person now and then. :)

A problem with that idea is that getting access to the characters passed is not too easy until BeforeUpdate is triggered.
You can do it, but it's very fiddly.

I'm afraid you lost me there. Which "characters passed" are you referring to? If you mean the value of the textbox, I'd have thought the OnChange event would allow you to do it pretty easily. Not that I have much experience in this area (in VB, yes; Access, no).

A ComboBox doesn't have a sorted facility, but the RowSource can easily include a sort (Either an ORDER BY if it's SQL or sorting within a QueryDef - Tables are more complicated :().

Yeah, the SQL posted here included the ORDER BY, and I just wondered whether it was necessary. Guess I just find it hard to accept how much functionality MS threw away when "porting" VB to Access, etc.