I would like to create a simple search-as-you-type combobox in Microsoft Access as shown in the image below.
NB: the above image is from a complicated implementation of what I am trying to achieve from here
My combobox is named ctlSearch. Using Visual Basic, I would like to hook on to the onChange event, detecting the user input and consequently refining the list of possible results. Is it possible to take this approach to implement the search-as-you-type combobox?
This is my Function I use to do filter combo-box as typed:
Public Sub FilterComboAsYouType(combo As ComboBox, defaultSQL As String, lookupField As String)
Dim strSQL As String
If Len(combo.Text) > 0 Then
strSQL = defaultSQL & " WHERE " & 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
Set the combo-box Auto Expand property to False and call the Sub FilterComboAsYouType in Change event like this:
Private Sub cmbProductName_Change()
FilterComboAsYouType Me.cmbProductName, "SELECT * FROM Product", "ProductName"
End Sub
Thanks Vlado, your answer is inspiring, concise & with gif explanation. I got asked for the same functionality couple days ago from a demanding customer.
However, the request is to have a google like search. In other words, to allow including more than a phrase (ANDed ofcourse) to be searched [partially] for randomly (as you type) in the target combo. text (which was a concatenation of several fields "tags" including cocktail name, alcoholic(Y/N), ingredients, glassware, brand, category ... etc.
Now I have been asked for the same again. I thought to attach it here for future searches to help others (fellow stuck programmers). Thanks again Vlado.
here you go:
Public Sub GoogleSearch(combo As ComboBox, OriginalSQL As String, LookupField As String)
' - OriginalSQL is not the recursive one
' - Use queries to build up your sql statement, then copy/paste thier sql text in the combo row source
' do not just point the row source to the query name (keep the query for reference if you like.
' to avoid the quotation agony of VBA built-in editor to create your OriginalSQL
' - Always store your OriginalSQL in global module variable,
' - Initiate it on (form) load, restore it on cancel AND after_update
' Created by Walid Zohair, not to be used without the exact comments
If Trim(combo.Text) = "" Or IsNull(combo.Text) Then
combo.RowSource = OriginalSQL
combo.Requery
combo.Dropdown
combo.SetFocus
Exit Sub
End If
Dim SQLStr As String
SQLStr = Replace(OriginalSQL, ";", "") ' make sure a bar end sql is used
' make sure order_by, group_by, Having will not be after where clause (gives error)
' This also can be used to give clearer names in outer SQL to be used in Where clause later
SQLStr = "SELECT * FROM ( " & SQLStr & " ) WHERE "
Dim StrArray() As String
StrArray = Split(Trim(combo.Text)) ' for saftey could be limited to up to 100 records only = Split(combo.text, " ", 100)
For i = 0 To UBound(StrArray)
SQLStr = SQLStr & LookupField & " LIKE '*" & StrArray(i) & "*'"
If UBound(StrArray) - i > 0 Then
SQLStr = SQLStr & " AND " ' Add AND to the search string
End If
Next i
combo.RowSource = SQLStr
combo.Dropdown
End Sub
Also, according to SO community guides I believe this can be considered more toward the question (better answer) rather than a reaction to other answers.
Try using this This is much simpler than your mentioned source.
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
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 ******
I've searched far and wide and this has been the closest to what I've done, so I'd leave my script here. It's about putting multiple phrases in a textbox and having it search through a field and produce results in a listbox (like google). I work in the medical field therefore terminologies and phrases can be very complex.
Smart Man's post above is similar to what I've done, I tried his method but wasn't successful. What was challenging was the formulation of a For Loop as he did, but my version varied the WHERE clause to the SQL statement and it searches up to four phrases like google. I hard-coded for the number of expected phrases, if anyone comes up with a corrective For Loop feel free to share. I only got a For Loop to work for two phrases at a time as opposed to multiple phrases.
In other search methods I've seen, you have to type the phrases in the order they are in the table and that limits the users, especially in medicine. So here goes.....
Private Sub txtbox_Change()
Dim text As String
Dim output As String
Dim ArrayNo As Integer
text = Me.txtbox.text
Dim whereclause() As String
whereclause = Split(text, " ")
'output = Join(whereclause, "*") 'This will cause the search to be in order of the entry/array. We really want it in any order
'Using a method for 4 string phrases
ArrayNo = UBound(whereclause)
If ArrayNo < 1 Then
output = Join(whereclause(), " Table.FieldName LIKE '*") 'String that works for first array
ElseIf ArrayNo = 1 Then
output = whereclause(0) & "*' And Table.FieldName Like '*" & whereclause(1) & ""
ElseIf ArrayNo = 2 Then
output = whereclause(0) & "*' And Table.FieldName Like '*" & whereclause(1) & "*' And Table.FieldName Like '*" & whereclause(2) & ""
ElseIf ArrayNo = 3 Then
output = whereclause(0) & "*' And Table.FieldName Like '*" & whereclause(1) & "*' And Table.FieldName Like '*" & whereclause(2) & "*' And Table.FieldName Like '*" & whereclause(3) & ""
End If
Dim SQL As String
SQL = "SELECT Table.FieldName FROM icd10_codes_combined " & _
" WHERE Table.FieldName LIKE '*" & output & "*' "
List.Visible = True
List.RowSource = SQL
End Sub
Public Function ComboBoxSearch(P_ComboBox As Control, P_DefaultSql As String, P_FieldSeachName As String) Dim V_SearchStr As String If Me.ActiveControl.Name = P_ComboBox.Name Then V_SearchStr = P_ComboBox.Text Else V_SearchStr = P_ComboBox End If
If Len(V_SearchStr) Then
Dim P_DefaultSql2 As String
If P_DefaultSql Like "*ORDER*" Then
P_DefaultSql2 = Mid(P_DefaultSql, InStr(1, P_DefaultSql, "ORDER"))
P_DefaultSql = Left(P_DefaultSql, InStr(1, P_DefaultSql, "ORDER") - 1)
End If
P_ComboBox.RowSource = P_DefaultSql & " WHERE " & P_FieldSeachName & " LIKE '*" & V_SearchStr & "*'" & P_DefaultSql2
Else
P_ComboBox.RowSource = P_DefaultSql
End If
P_ComboBox.Dropdown
P_ComboBox.SelLength = Len(V_SearchStr)
End Function
