Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    aonsu209 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2014
    Location
    Rotterdam
    Posts
    24

    Search (While You Type) in a ComboBox

    Dear all,
    I know there are a lot similar thread in the forum, but I culdnt find exacly what Im looking. In experts-exchange there is a full thread for that, but needs payed account. I told that some one alredy seen and know the salution.My goal is during typing in combobox to can search by keyword in the combobox it self. As you can see in the picture below


    Click image for larger version. 

Name:	cheval-blanc.png 
Views:	112 
Size:	13.4 KB 
ID:	18958
    The part of the tread can be found here.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,504
    see this site for examples

  3. #3
    aonsu209 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2014
    Location
    Rotterdam
    Posts
    24
    Quote Originally Posted by orange View Post
    see this site for examples
    Thank you for the reply, but it is not for what I'm looking for.
    I don't what filtering subform, I want to filter the combobox itself.
    Combobox to take value from one column from table and while you typing to select match of your type.In the example below I'm having couple of names and when I type "E" the complete name popup, but if I type "M" for searching by last name it is not popping up.
    Click image for larger version. 

Name:	access.jpg 
Views:	103 
Size:	11.9 KB 
ID:	18978Click image for larger version. 

Name:	access1.jpg 
Views:	103 
Size:	9.1 KB 
ID:	18979

  4. #4
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Dubai
    Posts
    614
    The following lines of code achieves what you desire -
    Add a combo box to your form, set its rowsource and set the AutoExpand property (on data tab of combox property sheet) to No.
    I have used a demo table having all the country names. You need to alter the code for use in your case.
    Code:
    Private Sub Combo4_Change()
    Dim strSQL As String
    If Len(Me.Combo4.Text) > 0 Then
    strSQL = "SELECT AllCountries.CountryName FROM AllCountries WHERE (((AllCountries.CountryName) Like '*" & Me.Combo4.Text & "*')) ORDER BY AllCountries.CountryName;"
    Else
    strSQL = "SELECT AllCountries.CountryName FROM AllCountries ORDER BY AllCountries.CountryName;"    'This is the default row source of combo box
    End If
    Me.Combo4.RowSource = strSQL
    Me.Combo4.Dropdown
    End Sub

  5. #5
    aonsu209 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2014
    Location
    Rotterdam
    Posts
    24
    Quote Originally Posted by amrut View Post
    The following lines of code achieves what you desire -
    Add a combo box to your form, set its rowsource and set the AutoExpand property (on data tab of combox property sheet) to No.
    I have used a demo table having all the country names. You need to alter the code for use in your case.
    Code:
    Private Sub Combo4_Change()
    Dim strSQL As String
    If Len(Me.Combo4.Text) > 0 Then
    strSQL = "SELECT AllCountries.CountryName FROM AllCountries WHERE (((AllCountries.CountryName) Like '*" & Me.Combo4.Text & "*')) ORDER BY AllCountries.CountryName;"
    Else
    strSQL = "SELECT AllCountries.CountryName FROM AllCountries ORDER BY AllCountries.CountryName;"    'This is the default row source of combo box
    End If
    Me.Combo4.RowSource = strSQL
    Me.Combo4.Dropdown
    End Sub
    Thank you Amrut,
    Seems to work, but maybe I'm missing some settings. I made similar simple database, but when I'm trying to fill the combobox the combobox went blank. See pics below.
    Click image for larger version. 

Name:	access 2.jpg 
Views:	110 
Size:	124.5 KB 
ID:	18997Click image for larger version. 

Name:	access 3.jpg 
Views:	101 
Size:	30.1 KB 
ID:	18998Click image for larger version. 

Name:	access 4.jpg 
Views:	101 
Size:	13.3 KB 
ID:	18999

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,035
    Nothing can show because you don't include Employee field in the SELECT clause. The field is not in the combobox RowSource so there is nothing to show.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    aonsu209 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2014
    Location
    Rotterdam
    Posts
    24
    Quote Originally Posted by June7 View Post
    Nothing can show because you don't include Employee field in the SELECT clause. The field is not in the combobox RowSource so there is nothing to show.
    Work perfect thank you to all .
    Click image for larger version. 

Name:	access5.jpg 
Views:	101 
Size:	16.8 KB 
ID:	19000

  8. #8
    Grandad is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Location
    Australia
    Posts
    15
    Be kind please. First post.
    i'm a rank amateur beginner but I'm slowly learning and enjoying the process. I've been at it for about a month.

    I realise this is a very old thread but I've been trying to get this same VBA working and am still getting the same result as aonsu209 was getting.
    Namely, the list part of the combo box goes blank.

    Judging from the spaces being shown as I enter characters and the list contracts and knowing what is in the relevant table I'd say the search itself is working fine.

    Code:
    Private Sub SearchCombo_Change()Dim strSQL As String
    If Len(Me.SearchCombo.Text) > 0 Then
    strSQL = "SELECT CustomerT.Search FROM CustomerT WHERE (((CustomerT.Search) Like '*" & Me.SearchCombo.Text & "*')) ORDER BY CustomerT.Search;"
    Else
    strSQL = "SELECT CustomerT.Search FROM CustomerT ORDER BY CustomerT.Search;"    'This is the default row source of combo box
     
    End If
    Me.SearchCombo.RowSource = strSQL
    Me.SearchCombo.Dropdown
    End Sub
    the Row Source for my combo box is
    Code:
    SELECT CustomerT.[ClientID], CustomerT.[Search] FROM CustomerT;
    The ComboBox name is "SearchCombo"
    Table name is CustomerT
    I've used a calculated field to combine the columns "FirstName", "PartnersName" & "LastName" into one field called "Search" This is the field that populates the ComboBox

    Can anyone please help?

    Cheers
    Jim

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,035
    You should have started your own thread with a reference to this one if you think it would be helpful to readers. New threads get more attention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Grandad is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Location
    Australia
    Posts
    15
    Thank you. See, learning already?
    So, now that I have resurrected this thread is it now too late to start a new one?
    Persevere here or start again?

    Jim

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,035
    Not too late. Your thread won't already be flagged as Solved.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    jack1988 is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    5
    I have gotten the above solution to work but I am getting an intermittent problem with the combobox picked value dissappearing (goes blank) after populating the same combobox on a continous form with new values. There seems to be no pattern to the behavior, it does not do it all the time. The chosen value in the combobox is still in the table and everything usually shows when I quit and open the form again. It there a way to correct this behavior?
    thanks
    Jack

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,035
    See Post #9.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    jack1988 is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    5
    combobox test.ziphere is a database that exhibits an above described problem with a combobox value going blank after a few more entries on a continous form have been made

  15. #15
    jack1988 is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    5
    I played around with the database and noticed a pattern: the continous form diplays only the entries corresponding to the most recent item. So if I just chose the "A" item in the combobox, only "A"s will diplay in previous rows and other entries go blank. If I next choose "B", "A"s and others will dissapear and only "B"s entered before will show. Any idea on how to modify the code to prevent this behavior? It happens regardless of whether the form with a combox is a subform or the main form.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Search as you type
    By Farnarkle in forum Programming
    Replies: 3
    Last Post: 11-17-2014, 06:03 AM
  2. Replies: 11
    Last Post: 11-09-2014, 05:18 PM
  3. Find as you type textbox to combobox
    By michael.legge@rbc.com in forum Access
    Replies: 3
    Last Post: 07-09-2013, 09:59 AM
  4. Search as you type
    By CaptainKen in forum Programming
    Replies: 19
    Last Post: 04-25-2012, 12:55 PM
  5. Cannot type in form with combobox
    By fuller in forum Access
    Replies: 8
    Last Post: 12-09-2011, 10:32 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums