Searching Access List Boxes data as-you-type in MS access forms

Question

I am trying to implement search list boxes data to populate as-you-type with the text box feild.

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.

http://www.opengatesw.net/ms-access-tutorials/Access-Articles/Search-As-You-Type-Access.html

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.

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

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.

**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 ""*" & Me.txtsearch.Value & _
                        "*"""

        '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: " & vbCrLf & Err.Description & _
                vbCrLf & "Error " & Err.Number & "(" & Erl & ")"
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 ""*" & me.txtsearch.value & "*"""
'                     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 <> "" Then
                 'Only fire if they've input more than two characters (otherwise it's wasteful)
50               If Len(ctlSearchBox.Value) > iSensitivity Then
60                   ctlFilter.RowSource = strFilteredSQL
70                   If ctlFilter.ListCount > 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 " & Format(ctlFilter.ListCount - 1, "#,##0") & " 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: " & vbCrLf & Err.Description & _
            vbCrLf & "Error " & Err.Number & vbCrLf & "Line: " & Erl
310   End Select


End Function
'   ***** Code End ******

Any idea what I am missing here. Thanks!

Answer 1

Firstly, don't use "Allow Multiple Values". It will only give you headaches. If you want to use a one-to-many relationship you should use a join table instead.

Edit: You can allow multiple values on the listbox, but you'll have to iterate through it to find the values and put them into the db individually if you want to do it right. So I'd suggest avoiding it unless you know what you are doing or are willing to learn more about coding and SQL and stuff.

That being said, the issue doesn't look related to the code you've imported but is related to the listbox itself:

What is the control source of the list box? For example, if the control source of the listbox is a single column, it cannot by default also accept multiple values. You'd have to change it in the source table of your form. The list box needs to match the database: if the database only allows one value, the list box must follow, if the database allows multiple then list box can also have them.

When Me.Refresh runs, it causes the listbox to lose focus which means access will attempt to update the Control Source of the listbox. My guess is that the control source can't accept multiple values and is leading to the observed bug.