1 | Download the sample Access database. |
2 | Import the module "modSearch" into your own database. |
3 | Add a new textbox above your listbox. We'll call
this the search box. Make the Default Value property: "(type to search)" |
4 | Add a textbox to the right of the search box. Make the control source ="x" We'll call this the clear button. |
5 | Add a label you want to display the count of rows displayed in the list box. We'll call this the count box. |
6 | Add this line to the top of your form's VB below the
"Option" statements: Private blnSpace As Boolean |
7 | Create a new event for your search box for the Key Press
event. Note that our search box is called "txtSearch."
Change any occurrences to your own search box control name. 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 |
8 | Create a new event for your clear button for the Click
event. Note that our clear button is called
"btnClearFilter." Change any occurrences to your own control name.
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 |
9 | Create events for your search box GotFocus and LostFocus
events. These will control when the user enters or leaves
the box what is displayed.
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 |
10 | Create a new event for your search box for the Change
event. This is the event that really drives the search. 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 RecordID, First, Last FROM tblNames ORDER BY First;" 'specify the way you want the rowsource to be filtered based on the user's entry 40 strFilteredList = "SELECT RecordID, First, Last FROM tblNames WHERE [First] LIKE ""*" & Me.txtSearch.Value & _ "*"" OR [Last] LIKE ""*" & Me.txtSearch.Value & "*"" ORDER BY [First]" 'run the search 50 fLiveSearch Me.txtSearch, Me.lstItems, strFullList, strFilteredList, Me.txtCount 60 End If End Sub In the above, the key changes are lines 30 and 40. You should set the yellow text in line 30 to represent the full listbox rowsource. You should set line 40 to be the filtered rowsource after the user enters in their search text. For line 50, replace the highlighted yellow items with your control names. The parameters for the function fLiveSearch(), part of the module "modSearch" you imported, are listed here: 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 '===================================================================================== |
11 | Test it out! If you run into any issues, most commonly it will be with step 10. Just make sure you've updated all the highlighted yellow areas. |