Results 1 to 3 of 3
  1. #1
    cardgage is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2011
    Posts
    50

    Filter dropdown of combo box to show records containing typed text

    Hello all,



    I have a combo box with two columns (ID field and short text field) which pulls all records from a table to select from (single select only). I noticed that the combo box will automatically jump to a record if the beginning of the short text field matches the partial text entered into the combo box by the user. Is there any way to filter the drop-down contents of the combo box to only show records that contain the partial text (anywhere in the short text field) entered into the combo box by the user.

    For example, say I have the following records in my table:
    Apple 1234
    Banana 3456
    Apple 9876
    Carrot 6789

    I would like the combo box drop-down to only show the following records if "34" is typed into the combo box:
    Apple 1234
    Banana 3456

    Thanks in advance for any help!
    cardgage

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    With VBA code that changes the RowSource after each character. For ideas, review http://allenbrowne.com/ser-32.html and http://allenbrowne.com/AppFindAsUType.html
    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.

  3. #3
    cardgage is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2011
    Posts
    50
    Thank you for the resource June7! I was able to get my combo box to behave as I wanted with the code from http://allenbrowne.com/ser-32.html. Here is the code that I ended up using, with my combox box called cmbListLit and desired searchable RowSource being a string called Lit_Cite from qryTaskListNames (primary key = SurveillanceID). I also modified the code a bit to automatically drop the combo box down once the RowSource was populated (minimum of 3 characters denoted by conLitMin):

    Code:
    Option Compare Database
    
    Dim strLitStub As String
    Const conLitMin = 3
    
    
    Function ReloadLit(strLit As String)
    'Code from: http://allenbrowne.com/ser-32.html
    
      Dim strNewStub As String    ' First chars of cmbListLit.Text
    
      strNewStub = Nz(Left(strLit, conLitMin), "")
        
      ' If first n chars are the same as previously, do nothing.
      If strNewStub <> strLitStub Then
        If Len(strNewStub) < conLitMin Then
          'Remove the RowSource
          Me.cmbListLit.RowSource = "SELECT SurveillanceID, Lit_Cite FROM qryTaskLitNames WHERE (False);"
          strLitStub = ""
        Else
          'New RowSource
          Me.cmbListLit.RowSource = "SELECT SurveillanceID, Lit_Cite FROM qryTaskLitNames WHERE (Lit_Cite Like ""*" & _
            strNewStub & "*"") ORDER BY Lit_Cite;"
          Me.cmbListLit.Dropdown
          strLitStub = strNewStub
        End If
      End If
    End Function
    
    
    Private Sub cmbListLit_Change()
    
      Dim cbo As ComboBox         ' cmbListLit combo box.
      Dim sText As String         ' Text property of combo box.
    
      Set cbo = Me.cmbListLit
      sText = cbo.Text
      
      Select Case sText
      Case " "                    ' Remove initial space
        cbo = Null
      Case Else                   ' Reload RowSource data.
        Call ReloadLit(sText)
      End Select
      
      Set cbo = Nothing
    
    End Sub
    
    
    Private Sub Form_Current()
    
      Call ReloadLit(Nz(Me.cmbListLit, ""))
    
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 12-25-2018, 03:59 PM
  2. Replies: 18
    Last Post: 07-11-2018, 03:14 AM
  3. Long list combo box filter as entry is typed?
    By NightWalker in forum Programming
    Replies: 2
    Last Post: 12-02-2017, 03:36 PM
  4. Replies: 9
    Last Post: 02-24-2015, 11:19 AM
  5. Replies: 12
    Last Post: 02-10-2013, 10:13 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