How to Create a Search Button in Access
Search-as-You-Type has become a pervasive feature of any modern user interface. As you begin entering characters, Google now instantly returns search results. As you type, Bing will begin predicting what you are searching for in the search box. Access even helps with this when you type into a dropdown box by navigating to the row that begins with the characters you are entering. Good Access form design includes keeping up with modern UI conventions. This page and Access sample download can help you add search-as-you-type capability to your own Access forms.
Searching Access List Boxes
The first area where you can implement search-as-you-type is the Access listbox control. Listboxes are commonly used when you want to give users a more extensive visual representation of a list. It requires more space than a combobox, but it also helps users see a partial list without any action to pull down the list. Listboxes don't have the same nice search-as-you-type you find in a combobox where the cursor is relocated as you type in the box. You can accomplish it with a few lines of VB code in your forms and a module we have created that you can import to your own project.
| 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) 10 On Error GoTo err_handle 20 If KeyAscii = 32 Then 70 Exit 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() |
| 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() Private Sub txtSearch_LostFocus() 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() 10 If blnSpace = False Then 'specify the default/full rowsource for the control 'specify the way you want the rowsource to be filtered based on the user's entry 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, _ |
| 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. |
You can also apply this same technique to a form instead of just a listbox with some minor tweaks. All you would need to change is the function fLiveSearch to change references from RowSource to RecordSource, and ListCount with RecordSetClone.RecordCount.
More Access database resources
How to Create a Search Button in Access
Source: https://www.opengatesw.net/ms-access-tutorials/Access-Articles/Search-As-You-Type-Access.html