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

combobox with textbox search as you type

P: 34
Hi,
would like to type in a textbox and have the combobox open and display filtered customers. I am looking at having 2 character typed before anything happens. I have done it already with a listbox but I was wondering if anyone has an elegant code they used in their code. A lot of examples online I know but which do you prefer?

Many thanks
Mar 20 '19 #1
Share this Question
Share on Google+
8 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,328
Neruda,

If you have been able to get it to work with a List Box, the Combo Box should be very similar. What have you tried so far and what are the specific challenges you are facing?
Mar 21 '19 #2

ADezii
Expert 5K+
P: 8,669
I threw together a simple Demo for you to illustrate how this can be done. Notice that the Auto Expand Property must be set to No for this to work. The Code will execute only when the characters in the box are >= 2.
Attached Files
File Type: zip Combo Search.zip (37.8 KB, 163 views)
Mar 26 '19 #3

ADezii
Expert 5K+
P: 8,669
Just realized that I Uploaded the wrong Demo, ignore the prior Attachment and reference this one.
Attached Files
File Type: zip Combo Search_2.zip (38.1 KB, 254 views)
Mar 26 '19 #4

P: 34
Thanks ADezii, could not make your sample work, I am using access 2010,seems to be searching for an excel library, not sure.

I can filter the combobox by typing in the textbox using :
Like "*" & [forms]![frmMain]![txtCustomerSearch].[Text] & "*" in the query, works fine but i would like the combo to dropdown as you type. If I put setfocus in the change event of the combobox I end up writing in the combobox and the focus goes from combo to textbox everytime a letter is typed.

can it be done?
Mar 27 '19 #5

ADezii
Expert 5K+
P: 8,669
Not sure what is going on here since it works fine on my end. Can you Upload your DB stripped of any sensitive data?
Mar 27 '19 #6

twinnyfo
Expert Mod 2.5K+
P: 3,328
Neruda,

I have tested ADezii's solution, as well, and it works fine.
Mar 27 '19 #7

P: 34
Ok, maybe its me but when i open it i get a black form with a combobox and an exit button? And it definitely searches for excel reference when i type
Mar 27 '19 #8

twinnyfo
Expert Mod 2.5K+
P: 3,328
Take a look at his code (I've modified it slightly for formatting):

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboDemo_Change()
  2. Dim strSQL As String
  3.  
  4. Me.cboDemo.RowSourceType = "Table/Query"
  5. strSQL = "SELECT Company FROM tblCompanies ORDER BY Company"
  6.  
  7. 'Nothing in Combo Text Box, show ALL Companies
  8. If Me.cboDemo.Text = "" Then
  9.     Me.cboDemo.RowSource = strSQL
  10.     Exit Sub
  11. End If
  12.  
  13. With Me
  14.     If Len(.cboDemo.Text) >= 2 Then 'At least 2 characters entered
  15.         'Partial Match on the 2 characters?
  16.         If DCount("*", _
  17.                   "tblCompanies", _
  18.                   "Company Like '" & .cboDemo.Text & "*'") > 0 Then
  19.             'There is a Partial Match, change the Row Source
  20.             .cboDemo.RowSource = _
  21.                 "SELECT Company " & _
  22.                 "FROM tblCompanies " & _
  23.                 "WHERE Company Like '" & .cboDemo.Text & "*' " & _
  24.                 "ORDER BY Company"
  25.             .cboDemo.Dropdown
  26.         Else
  27.             'No Partial Match on 2 or more characters, show ALL Companies
  28.             .cboDemo.RowSource = strSQL
  29.         End If
  30.     Else
  31.         'Less than 2 characters, show ALL Companies
  32.         .cboDemo.RowSource = strSQL
  33.     End If
  34. End With
  35.  
  36. End Sub
Place that code within your Form. Replace all the references to cboDemo with the name of your combo box, and replace references to the tables and fields to represent what you have in your DB.

This is another reason why we ask folks to post their own code so that we don't have to do these switcheroos after the fact because we don't know what your controls, tables and fields have for names.
Mar 27 '19 #9

Post your reply

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