title: Searching Access List Boxes data as-you-type in MS access forms
body: <p>I am trying to implement search  list boxes data to populate as-you-type with the text box feild.</p>
  
  <p>I read couple documents and useful material in net found the following link useful to implement my requirement so I used almost same code but ended up with an issue. </p>
  
  <p><a href="http://www.opengatesw.net/ms-access-tutorials/Access-Articles/Search-As-You-Type-Access.html" rel="nofollow">http://www.opengatesw.net/ms-access-tutorials/Access-Articles/Search-As-You-Type-Access.html</a></p>
  
  <p>I have a "Primary_skill" List box feild in my form where it has 100+ items in it and I am trying implement data to auto display as per the search I enter txt box feild in the form.</p>
  
  <p>The problem I run here, I was unable to choose two different items in search here. (I get some error with the line Me.refresh in form code</p>
  
  <p>Example to elaborate: I want to choose user primary_skills are both "DB2" and "SQL server" where as I was able search intially and selected the check box of db2 and later I change search txt  i get error pointing debug at me.refresh line in "on change" event.</p>
  
  <pre><code>**Form search as-you-type List box code**
  
  Private Sub btnClearFilter_Click()
  'CODE FOR THE RED "X" BUTTON TO CLEAR THE FILTER AND SHOW ALL
  On Error Resume Next
  10      Me.txtsearch.Value = ""
  20      txtSearch_Change
  End Sub
  Private Sub txtSearch_Change()
  'CODE THAT HANDLES WHAT HAPPENS WHEN THE USER TYPES IN THE SEARCH BOX
  Dim strFullList       As String
  Dim strFilteredList   As String
  
  
  10    If blnSpace = False Then
  20      Me.Refresh 'refresh to make sure the text box changes are actually available to use
  
          'specify the default/full rowsource for the control
  30      strFullList = "SELECT TEMP.Primary_Skill FROM TEMP;"
  
          'specify the way you want the rowsource to be filtered based on the user's entry
  40      strFilteredList = "SELECT TEMP.Primary_Skill FROM TEMP WHERE [Primary_Skill] LIKE ""*" &amp; Me.txtsearch.Value &amp; _
                          "*"""
  
          'run the search
  50      fLiveSearch Me.txtsearch, Me.Primary_Skill, strFullList, strFilteredList, Me.txtCount
  60    End If
  
  End Sub
  
  
  Private Sub txtSearch_KeyPress(KeyAscii As Integer)
  'NECESSARY TO IDENTIFY IF THE USER IS HITTING THE SPACEBAR
  'IN WHICH CASE WE WANT TO IGNORE THE INPUT
  
  10    On Error GoTo err_handle
  
  20       If KeyAscii = 32 Then
  30            blnSpace = True
  40       Else
  50            blnSpace = False
  60       End If
  
  
  70    Exit Sub
  err_handle:
  80    Select Case Err.Number
            Case Else
  90          MsgBox "An unexpected error has occurred: " &amp; vbCrLf &amp; Err.Description &amp; _
                  vbCrLf &amp; "Error " &amp; Err.Number &amp; "(" &amp; Erl &amp; ")"
  100   End Select
  End Sub
  Private Sub txtSearch_GotFocus()
  ' USED TO REMOVE THE PROMPT IF THE CONTROL GETS FOCUS
  10    On Error Resume Next
  20      If Me.txtsearch.Value = "(type to search)" Then
  30        Me.txtsearch.Value = ""
  40      End If
  End Sub
  Private Sub txtSearch_LostFocus()
  ' USED TO ADD THE PROMPT BACK IN IF THE CONTROL LOSES FOCUS
  10    On Error Resume Next
  20      If Me.txtsearch.Value = "" Then
  30        Me.txtsearch.Value = "(type to search)"
  40      End If
  
  End Sub
  
  
  **Modsearach (Module Code):**
  
  Option Compare Database
  Option Explicit
  '************* Code Start **************
  ' This code was originally written by OpenGate Software
  ' It is not to be altered or distributed,
  ' except as part of an application.
  ' You are free to use it in any application,
  ' provided the copyright notice is left unchanged.
  ' OpenGate Software    http://www.opengatesw.net
  
  Function fLiveSearch(ctlSearchBox As TextBox, ctlFilter As Control, _
                        strFullSQL As String, strFilteredSQL As String, Optional ctlCountLabel As Control)
  '==================================================================================
  '  THIS FUNCTION ALLOWS YOU TO FILTER A COMBO BOX OR LIST BOX AS THE USER TYPES
  '  ALL YOU NEED TO DO IS PASS IN THE CONTROL REFERENCE TO THE SEARCH BOX ON YOUR
  '  FORM, THE LISTBOX/COMBO BOX YOU WANT TO FILTER, AND WHAT THE FULL AND FILTERED
  '  SQL (ROWSOURCE) SHOULD BE.
  '
  '  ctlSearchBox       THE TEXTBOX THE USER TYPES IN TO SEARCH
  '
  '  ctlFilter          THE LISTBOX OR COMBOBOX ON THE FORM YOU WANT TO FILTER
  '
  '  strFullSQL         THE FULL ROWSOURCE YOU WANT TO DISPLAY AS A DEFAULT IF NO
  '                     RESULTS ARE RETURNED
  '
  '  strFilteredSQL     THE FILTERED ROWSOURCE FOR THE LISTBOX/COMBOBOX; FOR EXAMPLE
  '                     YOU WOULD WANT TO USE '...like ""*" &amp; me.txtsearch.value &amp; "*"""
  '                     TO FILTER THE RESULTS BASED ON THE USER'S SEARCH INPUT
  '
  ' ctlCountLabel       (OPTIONAL) THE LABEL ON YOUR FORM WHERE YOU WANT TO DISPLAY THE
  '                     COUNT OF ROWS DISPLAYED IN THE LISTBOX/COMBOBOX AS THEY SEARCH
  '=====================================================================================
  
  'ADVANCED PARAMETERS - Change these constants to change the behaviour of the search
    Const iSensitivity = 1 'Set to the number of characters the user must enter before the search starts
    Const blnEmptyOnNoMatch = True 'Set to true if you want nothing to appear if nothing matches their search
  
  
  10    On Error GoTo err_handle
  
            'restore the cursor to where they left off
  20        ctlSearchBox.SetFocus
  30        ctlSearchBox.SelStart = Len(ctlSearchBox.Value) + 1
  
  40        If ctlSearchBox.Value &lt;&gt; "" Then
                   'Only fire if they've input more than two characters (otherwise it's wasteful)
  50               If Len(ctlSearchBox.Value) &gt; iSensitivity Then
  60                   ctlFilter.RowSource = strFilteredSQL
  70                   If ctlFilter.ListCount &gt; 0 Then
  80                       ctlSearchBox.SetFocus
  90                       ctlSearchBox.SelStart = Len(ctlSearchBox.Value) + 1
  100                   Else
  110                     If blnEmptyOnNoMatch = True Then
  120                      ctlFilter.RowSource = ""
  130                     Else
  140                      ctlFilter.RowSource = strFullSQL
  150                     End If
  160                   End If
  170             Else
  180               ctlFilter.RowSource = strFullSQL
  190             End If
  
  200        Else
  210           ctlFilter.RowSource = strFullSQL
  220        End If
  
              'if there is a count label, then update it
              'if there is a count label, then update it
  '230         If IsMissing(ctlCountLabel) = False Then
  '240           ctlCountLabel.Caption = "Displaying " &amp; Format(ctlFilter.ListCount - 1, "#,##0") &amp; " records"
  '250         End If
  260   Exit Function
  err_handle:
  270   Select Case Err.Number
      Case 91 'no ctlCountLabel
         'exit
  280       Case 94 'null string
         'exit
  290       Case Else
  300         MsgBox "An unexpected error has occurred: " &amp; vbCrLf &amp; Err.Description &amp; _
              vbCrLf &amp; "Error " &amp; Err.Number &amp; vbCrLf &amp; "Line: " &amp; Erl
  310   End Select
  
  
  End Function
  '   ***** Code End ******
  </code></pre>
  
  <p>Any idea what I am missing here. Thanks!</p>