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

Combo Box Filtering

100+
P: 134
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.
Jan 7 '07 #1
Share this Question
Share on Google+
13 Replies


hariharanmca
100+
P: 1,977
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
Jan 7 '07 #2

100+
P: 134
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.
Jan 7 '07 #3

ADezii
Expert 5K+
P: 8,616
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 programmatically 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:
Expand|Select|Wrap|Line Numbers
  1. Dim MySQL As String
  2. MySQL = "SELECT tblEmployees.FirstName FROM tblEmployees WHERE " _
  3.         & "tblEmployees.FirstName Like 'JAC*' ORDER BY tblEmployees.FirstName;"
  4.  
  5. Me![cboTest].RowSource = MySQL
  6. Me![cboTest].Requery
Output: Produces only First Names that begin with JAC...
Jan 7 '07 #4

100+
P: 134
'You can programmatically 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:
Expand|Select|Wrap|Line Numbers
  1. Dim MySQL As String
  2. MySQL = "SELECT tblEmployees.FirstName FROM tblEmployees WHERE " _
  3.         & "tblEmployees.FirstName Like 'JAC*' ORDER BY tblEmployees.FirstName;"
  4.  
  5. Me![cboTest].RowSource = MySQL
  6. 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 ?
Jan 7 '07 #5

NeoPa
Expert Mod 15k+
P: 31,307
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.
Jan 8 '07 #6

100+
P: 134
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
Jan 8 '07 #7

Expert 5K+
P: 8,434
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.
Jan 8 '07 #8

NeoPa
Expert Mod 15k+
P: 31,307
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 :().
Jan 8 '07 #9

Expert 5K+
P: 8,434
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.
Jan 8 '07 #10

NeoPa
Expert Mod 15k+
P: 31,307
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. :)
The idea had occurred to me too :)
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).
OnChange triggers only when you leave the field or hit enter to accept changes. You wouldn't trigger the event after each character I'm afraid.

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.
If the OP wants it sorted then I guess it's necessary ;)
Jan 8 '07 #11

Expert 5K+
P: 8,434
The idea had occurred to me too :)
OnChange triggers only when you leave the field or hit enter to accept changes. You wouldn't trigger the event after each character I'm afraid.
No, I have to disagree with you on that one. I have just used this test to prove it to my own satisfaction. Bear with me, as this is pretty complex stuff. ;)

1. Create a new form.
2. Stick a text box on it.
3. Put in an event procedure for On Change
4. Put this code in the procedure...
Expand|Select|Wrap|Line Numbers
  1. Private Sub Text0_Change()
  2.  Beep
  3.  Debug.Print Text0.Text
  4. End Sub
For me at least, it beeps and displays the updated text after every character.
Jan 9 '07 #12

NeoPa
Expert Mod 15k+
P: 31,307
Oh RATS!
That means I was talking rubbish again :(
Sorry.
Jan 9 '07 #13

Expert 5K+
P: 8,434
Oh RATS!
That means I was talking rubbish again :(
Well... a more polite way to put it might be that one of your assertions was later shown to be in error.

Still means the same, but sounds better. :)
Jan 9 '07 #14

Post your reply

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