Solved Search as you type with in Combobox / instant filter combobox itself (1 Viewer)

Ihk

Member
Local time
Today, 12:20
Joined
Apr 7, 2020
Messages
280
I am looking for code "want to filter combo-box (drop down data) by key word while typing". On a form I have three comboboxes. 1) for Articles 2) for Company 3) for Catalogues
I was able to do this search filter for one combobox (Articles), but dont know how to change it for the other two comboboxes on the same form. Example Picture (what I want):

combo.png

I have used the following code for this. (1) 1st Public Sub FilterComboAsYouType(combo As ComboBox, defaultSQL As String, lookupField As String) (2) on particular combobox change envent.
Code:
Public Sub FilterComboAsYouType(combo As ComboBox, defaultSQL As String, lookupField As String)
Dim strSQL As String

        If Len(Article.Text) > 0 Then
          strSQL = defaultSQL & " WHERE " & lookupField & " LIKE '*" & Article.Text & "*'"
  Else
          strSQL = defaultSQL    'This is the default row source of combo box
  End If
           Article.RowSource = strSQL
           Article.Dropdown
End Sub
Then combobox event on Change
Code:
Private Sub Article_Change()
FilterComboAsYouType Me.Article, "SELECT * FROM ArticleNameTbl", "ArticlesName"
End Sub
Now the problem is , above 1st code "FilterComboAsYouType etc" is fixed with "Article" combobox. I dont know how to make this possible for the other two comboboxes on the same form 1) company 2) Catalogue.

I tried different ways, it did not work. I need expert opinion. Thanks
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:20
Joined
Jan 23, 2006
Messages
15,361
MajP on this site has a Find As You Type combo. He also has many posts on the site explaining various concepts. You may try searching this site for related posts.
See the Similar Threads at the bottom of this page too.
Good luck.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:20
Joined
May 7, 2009
Messages
19,169
you need to make your function generic:
Code:
Public Sub FilterComboAsYouType(ByRef combo As ComboBox, ByVal defaultSQL As String, ByVal lookupField As String)
    Dim strSQL As String

    If Trim(combo.Text & "") <> "" Then
        strSQL = "SELECT * FROM (" & defaultSQL & ") WHERE CStr([" & lookupField & "]) LIKE '*" & combo.Text & "*'"
    Else
        strSQL = defaultSQL    'This is the default row source of combo box
    End If
    combo.RowSource = strSQL
    combo.Dropdown
End Sub

there are 3 combobox, so on your Form's code add 3 variable to save the 3 combo's rowsource:
Code:
Option Compare Database
Option Explicit

Dim sComboSrc1 As String
Dim sComboSrc2 As String
Dim sComboScr3 As String

Private Sub Form_Load()
    sComboSrc1 = Me.Combo1Name.RowSource
    sComboSrc2 = Me.Combo2Name.RowSource
    sComboSrc3 = Me.Combo3Name.RowSource
End Sub
on each Combo's Change Event:

Code:
Private Sub Combo1Name_Change()
    Call FilterComboAsYouType(Me.Combo1Name, sComboSrc1, "put the column name to search here")
End Sub

Private Sub Combo2Name_Change()
    Call FilterComboAsYouType(Me.Combo2Name, sComboSrc2, "put the column name to search here")
End Sub

Private Sub Combo3Name_Change()
    Call FilterComboAsYouType(Me.Combo3Name, sComboSrc3, "put the column name to search here")
End Sub

/// remember to replace Combo1Name, Combo2Name, Combo3Name with the correct name of your combos.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:20
Joined
May 7, 2009
Messages
19,169
confused? you can check the code on here
 

Attachments

  • sampleCombos.zip
    25.7 KB · Views: 810

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:20
Joined
May 21, 2018
Messages
8,463
Here is my version. This includes FAYT combobox, listbox, and form. They all handle multi column searches and you can define the search pattern
*Text
Text*
or
*Text*
All require a single line to instantiate. Traps multi events such as the forms on current.
 

Attachments

  • MajP FAYT V12.zip
    153.3 KB · Views: 721

Ihk

Member
Local time
Today, 12:20
Joined
Apr 7, 2020
Messages
280
@arnelgp
I am very very thankful for your instant and detailed coding. Actually I tried and Unfortunately It is not working in my case as I want "key word filter within combobox".

@MajP
I liked your demo database and I liked the "keyword search in your combobox. It is really perfect in your database, Even a single keyword filters the data. But I spent hours to implement those codes in my case, Unfortunately I could not do so. It is because probably I am new and learning. It gives me errors.


I have attached my database, with only relevant data related to my issue. If someone can sort it out.
please note: it does not contain any of above mentioned codes, it is as default.

It has three comboboxes on "userOrderForm".

I will be very thankful, If someone can solve my issue. Thanks.
 

Attachments

  • Database -V5.7.zip
    127.8 KB · Views: 429

Ihk

Member
Local time
Today, 12:20
Joined
Apr 7, 2020
Messages
280
Here is my version. This includes FAYT combobox, listbox, and form. They all handle multi column searches and you can define the search pattern
*Text
Text*
or
*Text*
All require a single line to instantiate. Traps multi events such as the forms on current.
Thank you very much.
I liked your demo database "filter within combobox". It is really very impressive. I tried to fit it in my case but unfortunately I could not do it. I spent hours, but there were still errors. Probably I am new to this.
I have attached my database with default coding for that form "UserOrderForm"
 

Attachments

  • Database -V5.7.zip
    127.8 KB · Views: 402

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:20
Joined
May 21, 2018
Messages
8,463
Code:
'At the top of your module you need a variable for each combo
Private faytCompany As New FindAsYouTypeCombo
Private faytArticle As New FindAsYouTypeCombo
Private faytCat As New FindAsYouTypeCombo

'On forms on load must instantiate and initialize
Private Sub Form_Load()
  faytCompany.InitalizeFilterCombo Me.Company, "companyNameslist", , , False
  faytArticle.InitalizeFilterCombo Me.Article, "articlesname", , , False
  faytCat.InitalizeFilterCombo Me.Cat_No, "cat_no", , , False
End Sub
 

Attachments

  • Database -V5.7_MajP.zip
    142.7 KB · Views: 417

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:20
Joined
May 7, 2009
Messages
19,169
here test your db using your
FilterComboAsYouType() function.
 

Attachments

  • Database -V5.7.zip
    86.3 KB · Views: 560
  • Like
Reactions: Ihk

Ihk

Member
Local time
Today, 12:20
Joined
Apr 7, 2020
Messages
280
@MajP
@arnelgp
I am really very very thankful. It is working best. You are very nice. Words fail me to express thanks.
It solved my problem, which was I was looking for many days and spent hours. Learnt a lot from you.
I wish you good day, from Germany.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:20
Joined
May 7, 2009
Messages
19,169
majp and i are happy to help you.
 
  • Love
Reactions: Ihk

Mike Krailo

Well-known member
Local time
Today, 07:20
Joined
Mar 28, 2020
Messages
1,030
arnelgp, does your code work with SQL as rowsource? Somehow, this doesn't work in my case. I just have a single combo box that has three columns in the dropdown via sql string in the rowsource but it just blanks out all of the data when a letter is pressed instead of filtering.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:20
Joined
May 7, 2009
Messages
19,169
the sample db from the lhsang uses SQL as rowsource of his combo.
can you upload a sample (only the form with combo + the table where the combo gets it combolist).
 

Mike Krailo

Well-known member
Local time
Today, 07:20
Joined
Mar 28, 2020
Messages
1,030
Attached the DB using your code. On the main form just click any item and select "Move Item". Then select option "Fill Hole In System" and press OK. That will present the correct form "MoveItem" with the correct SQL filter in the combo box. There is some logic that switches the rowsource and visibility of the combo box based on the options selected from the Action form.
 

Attachments

  • FilterCombo.zip
    504.2 KB · Views: 412

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:20
Joined
May 7, 2009
Messages
19,169
minor change to the function.

changed:

If InStr(strSQL, "WHERE") > 0 Then

with:

If InStr(strSQL, "WHERE T.") > 0 Then
 

Attachments

  • FilterCombo.zip
    597.3 KB · Views: 552

Mike Krailo

Well-known member
Local time
Today, 07:20
Joined
Mar 28, 2020
Messages
1,030
That was it arnelgp, it's working perfectly now. Thank you for looking at it.
 

nirmal

Member
Local time
Today, 16:50
Joined
Feb 27, 2020
Messages
82
confused? you can check the code on here
Sir, actually I wanted to know that why I have to press the backspace to get all the results.

e.g.
in the lastname combo, when I type word 'w', I get only Wacker
but on the contrary I should get Wacker & Edward in dropdown
And to get both the dropdown values matching to 'w' I have to press backspace
Sir please solve this issue, as I have noticed this issue with the FAYT module as it happens in all comboboxes
 

Mike Krailo

Well-known member
Local time
Today, 07:20
Joined
Mar 28, 2020
Messages
1,030
Interesting, I just tested it and I get both Wacker and Edwards in the combo after typing just 'w'. No need to hit the backspace key.
If you turn off that Auto Expand property by setting it to 'No' for the combo properties, then maybe that's the type of behavior you are looking for. I have no idea why you need to hit the backspace key to see all the values. Just click the dropdown arrow if you need to see all the values or type something to narrow the results. Very simple. Keep in mind this is not searching from the beginning of the last name, it is searching everywhere in the name. So two or three characters will need to be typed to really narrow down the search in most cases.

You can also force the combo to dropdown using Got Focus event.

Code:
Private Sub Combo0_GotFocus()
   Me.Combo0.Dropdown
End Sub
 
Last edited:

Users who are viewing this thread

Top Bottom