Introduction
Search, find, filter, and sort operations are essential for organizing data into a manageable form, making it easier to locate specific information. We have already explored some of these operations in the following earlier posts titled:
Now, let’s look at a simpler and more user-friendly method for filtering data, by typing one or more characters directly into a text box and seeing instant results, rather than entering search text in various controls and clicking multiple buttons or toolbar options.
When the first character is entered into the text box (for example, the letter F), all records with names starting with that letter are instantly displayed. If the filtered list is still large or includes unwanted items, entering additional characters narrows the list. This process can be repeated as needed.
If the Backspace key is used to delete one or more characters, the filtered list will expand accordingly, gradually reversing the process. Since the filtering occurs instantly, the user can see results as soon as a key is pressed.
If you need to transfer values from one of these filtered records to another open form, you can do so by writing a routine for the Double-Click event at the form level. Double-clicking the record selector (the left border of a record) can trigger this action and transfer the required values to another open form. We’ll look at the sample code for this action at the end of this article.
Sample Image of Form
The limitation of this method is that it is designed for use with tabular-type forms, where multiple records can be viewed simultaneously. The earlier methods (referenced in the previous articles) were intended for single-record forms.
Typically, a search or filter operation of this kind is implemented for a single field, such as Employee Code, Company Code, or Company Name—fields that are most relevant when searching for information.
However, in this example, we’ll take it a step further by offering greater flexibility. We’ll provide a combo box listing all the field names from the form’s source object (table or query). This allows the user to select which field to filter the data on, making the search feature far more versatile.
To follow along with this example, you’ll need the Customers table from the Northwind.mdb sample database. If you’re unsure where to find this file, refer to last week’s post—specifically, the third item in the list of three links provided there.
Sample Data Table, Query & Form.
Import the Customers Table from the Northwind database.
Create a SELECT Query with the following SQL String and save the Query with the name CustomersQ.
SELECT Customers.[First Name], Customers.[Last Name], Customers.[Job Title] FROM Customers;
Design a Tabular Form as shown in the Image given above. I have selected only three fields from the Table in this example. If you like to use some other Fields in the Query, you may do so.
Expand the Form Footer Section, if it is not visible (View ->Form Header/Footer).
If the Toolbox is not visible, display it by selecting Toolbox from the View Menu.
De-select the Control Wizards Button (the top right one); if it is already in the selected state on the ToolBox, so that the Wizard will not start when the Combo Box Tool is selected.
Select the Combo Box Tool from the Toolbox and draw a Combo Box on the Footer Section of the Form, as shown in the design above.
Change the Caption value of the Child Label on the Combo Box to Filter/Sort Field:.
Click on the Combo Box to select it and display its Property Sheet (View -> Properties).
Change the following Property Values as given below:
- Name = cboFields
- Row Source Type = Field List.
- Row Source = CustomersQ.
- Column Width = 1.5"
Create a Text Box below the Combo Box.
Change the Caption Value of the Child Label to FilterText:.
Change the Name Property Value of the Text Box to FilterText.
Create an Option Group Control, with two buttons with Label Captions ASC and DESC for the Sorting option, Ascending or Descending order.
Create a Command Button to the right of the Options Group control and change the following Property values:
- Name = cmdClose
- Caption = Close
The VBA Code
Display the VBA Code Module of the Form (View -> Code or Alt+F11).
Copy and paste the following Code into the Module and save the Form with the name Customers or any other name you prefer.
'Global declaration Dim x, rst As Recordset Private Sub cmdClose_Click() DoCmd.Close End Sub Private Sub FilterText_KeyUp(KeyCode As Integer, Shift As Integer) Dim i As Integer, tmp, j As Integer, srt As String On Error GoTo FilterText_KeyUp_Err i = KeyCode Select Case i Case 8 'backspace key Me.Refresh If Len(x) = 1 Or Len(x) = 0 Then x = "" Else x = Left(x, Len(x) - 1) 'delete the last character End If GoSub setfilter Case 37, 39 'left and right arrow keys SendKeys "{END}" 'ignore action Case 32, 48 To 57, 65 To 90, 97 To 122 'space, 0 to 9, A to Z, a to z keys x = x & Chr$(i) Me![FilterText] = x GoSub setfilter End Select FilterText_KeyUp_Exit: Exit Sub setfilter: Me.Refresh tmp = Nz(Me!cboFields, "") 'save the value in Combo Box If Len(Nz(x, "")) = 0 Then Me.FilterOn = False ' remove filter Else 'set filter and enable Me.Filter = "[" & Me![cboFields] & "]" & " like '" & x & "*'" Me.FilterOn = True End If ' Set sort order j = Me!Frame10 srt = IIf(j = 1, "ASC", "DESC") Me.OrderBy = "[" & Me!cboFields & "] " & srt Me.OrderByOn = True Me![cboFields] = tmp Me.FilterText.SetFocus SendKeys "{END}" Return FilterText_KeyUp_Err: MsgBox Err.Description, , "FilterText_KeyUp()" Resume FilterText_KeyUp_Exit End Sub Private Sub Form_Close() Application.SetOption "Behavior Entering Field", 0 Me.FilterOn = False Me.OrderByOn = False End Sub Private Sub Form_Load() Application.SetOption "Behavior Entering Field", 2 Set rst = Me.RecordsetClone Me!cboFields = rst.Fields(0).Name Me.Refresh rst.Close End SubTest Run the Demo Form
Open the Form in Normal View.
The First Name field will appear as the default value in the Combo Box control.
Click on the Text Box below the Combo Box to set the focus on it.
Type the Character F and you will see that all records with CustomerID values starting with the letter F are filtered.
If you look at the filtered Field values of records, you may find that the second character of the filter field values is different, and three items have the same letter in the second character position. Besides, the field values are correctly sorted in selected (Ascending/Descending) Alphabetical Order.
Type the second common character next to the earlier character in the FilterText control, and the list of items narrows down. You can further filter and narrow down the list progressively.
Press the Backspace Key to delete the last character typed and to leave the rest of the characters in the Text Box. The list will expand, and all items starting with the letters in the control are back on the List.
Press the Backspace Key again to delete other characters one by one from the TextBox. This time, the Filter action is removed, and all the records are back in the Form.
NB: The Filter Criteria Text Values are limited to the Characters 0 to 9, A to Z, and a to z only.
If you want to try the Filter action on one of the other two fields, select that Field's name from the Combo Box above before trying the filter action explained from Step 19 onwards.
Try it on the third Field Job Title for different results.
Download Filter_ByCharSort2007.zip
AnimatingLabel.zip
