Filter Function Output In Listbox-2.
Last week, we explored the Filter() function, a simple example that demonstrates how it works. In that example, we assigned constant values directly to the source array elements to keep the VBA code straightforward and easy to follow.
Just as we can filter data on Forms by setting criteria in the Filter property, or use conditions in Queries to extract information from Tables, the Filter() function provides yet another powerful way to process data — but this time, directly from an Array.
By loading data from a Table or Query into an array, we can use the Filter() function to quickly generate results that match (or exclude) a specified pattern.
To understand this better, let’s try an example using the Employees table from the Northwind.mdb sample database. We’ll create a simple Address Book application that uses the Filter() function to find and display matching names or addresses in a List Box based on user input.
The following section outlines the User Interface (UI) design we plan to create and explains how users will interact with it to display information quickly on the Form.
For this experiment, we will design a Form containing the following controls:
-
A List Box to display the filtered results.
-
A Text Box for entering the search text.
-
A Check Box to toggle between matching and non-matching results.
-
A Command Button to execute the filter operation.
An image of the Form in Design View is shown below:
When the Form opens in Normal View, both the List Box and Text Box controls will be empty.
-
To display all records, the user can type ALL in the Text Box and click the Command Button. This action will load and display the names and addresses of all employees in the List Box.
-
Alternatively, the user can enter a word or phrase — such as part of a name or address — and click the Command Button to display only those records where the entered text appears anywhere within the name or address fields.
If the Matching Cases Check Box is selected, the filter will include only records that match the search text.
If it is not selected, the filter will instead display all records that do not match the entered text.
To provide the user with the functionality described above, we need two Subroutines in the Form’s Code Module and a User-Defined Function in a Standard Module that utilizes the Filter() function.
-
Form_Load Event:
When the Form opens, the first Subroutine runs from theForm_Load()event. It reads data — specifically the First Name, Last Name, and Address fields — from the Employees table. These values are combined into a single text string per record and stored in a single-dimensional array variable in memory. This array remains active for as long as the Form is open. -
Command Button Click Event:
The second Subroutine runs when the user clicks the Command Button. It uses theFilter()function to extract matching entries from the source array based on the search text entered in the Text Box control.The
Filter()function returns all array elements that match (or, optionally, do not match) the specified search text and saves them into a target array variable (xTarget). The resulting data is then formatted and assigned to the Row Source property of the List Box, which displays the filtered results to the user.
The Address Book Project.
Let us prepare for the Address Book's Quick Find Project.
The Design Task.
Import the Employees Table from the C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample database.
Open a new Form in Design View.
Select the List Box Control from the Toolbox and draw a List Box as shown in the design above.
While the List Box is in the selected state, display the Property Sheet (View -> Properties) and change the following Property Values as given below:
- Name = AddBook
- Row Source Type = Value List
- Width = 4.5"
- Height = 1.75"
- Font Name = Courier New
- Font Size = 10
Position the Child Label attached to the List Box above and change the Caption value to Address Book.
Draw a TextBox below the ListBox. Change the Name Property value of the TextBox to xFind. Position the Child Label above the TextBox and change the Caption value to Search Text/ALL.
Create a Check-Box Control to the right of the Text Box. Change the Name Property of the Check-Box to MatchFlag. Change the Default Value Property to True. Change the Caption value of the child label of Matching Cases.
Create a Command Button to the right of the Check-Box control. Change the Name Property Value of the Command Button to cmdFilter and the Caption Property Value to Filter.
NB: Ensure that the Name Property Values of the above controls are the same as given above. This is important because we are referencing these names in Programs.
Display the Code Module of the Form (View ->Code).
Copy and paste the following Code into the Form Module:
The VBA Code
Option Compare Database Option Explicit Dim xSource() As Variant Private Sub Form_Load() Dim db As Database, rst As Recordset, J As Integer Dim FName As String * 12, LName As String * 12, Add As String * 20 'Take the count of records J = DCount("*", "Employees") 'redimension the array for number of records ReDim xSource(J) As Variant Set db = CurrentDb Set rst = db.OpenRecordset("Employees", dbOpenDynaset) 'load the name and addresses into the array J = 0 Do Until rst.EOF FName = rst![FirstName] LName = rst![LastName] Add = rst![Address] xSource(J) = FName & LName & Add rst.MoveNext J = J + 1 Loop rst.Close Set rst = Nothing Set db = Nothing End Sub Private Sub cmdFilter_Click() Dim x_Find As String, xlist As String, xTarget As Variant Dim x_MatchFlag As Boolean, J As Integer Me.Refresh x_Find = Nz(Me![xFind], "") x_MatchFlag = Nz(Me![MatchFlag], 0) 'if no search criteria then exit If Len(x_Find) = 0 Then Exit Sub End If 'initialize list box xlist = "" Me.AddBook.RowSource = xlist Me.AddBook.Requery If UCase(x_Find) = "ALL" Then 'Take all values from the Source Array 'Format it as listbox items For J = 0 To UBound(xSource()) xlist = xlist & xSource(J) & ";" Next Else 'Call the Filter Function xTarget = GetFiltered(xSource(), x_Find, x_MatchFlag) 'format the returned values as list box items If Len(xTarget(0)) > 0 Then For J = 0 To UBound(xTarget) xlist = xlist & xTarget(J) & ";" Next End If End If 'remove the semicolon from 'the end of the list box Value List If Len(xlist) > 0 Then xlist = Left(xlist, Len(xlist) - 1) End If 'insert the list item string 'and refresh the list box Me.AddBook.RowSource = xlist Me.AddBook.Requery End SubSave the Form with the name Filter Form.
- Copy and paste the following Function into a Standard Module and save the Module:
Public Function GetFiltered(ByRef SourceArray() As Variant, ByVal xFilterText As Variant, ByVal FilterType As Boolean) As Variant GetFiltered = Filter(SourceArray, xFilterText, FilterType) End FunctionFilter() Function and Filter Property of the Form.
We cannot use the Filter() Function in the Form Module because the function name clashes with the Form Property Filter.
We have placed the
Filter()function inside a User-Defined Function namedGetFiltered()within a Standard Module, along with the required parameters. This allows us to call it easily from the Form’s Module. The first parameter of the function is passed By Reference, enabling direct access to the source array values without creating a separate copy — improving both performance and efficiency.The Demo Runs.
Open the Filter Form in Normal View.
Enter the word ALL (in this case, the Matching Cases flag has no effect) in the Text Box control and click on the Filter Command Button.
This action will display the names and Addresses of all Employees from the xSource() Array loaded from the Employees Table.
Enter the text Ave in the Text Box and ensure that the 'matching cases' check box is in the selected state.
Click on the Command Button.
This time, the employees' (Nancy & Laura) Names and Addresses are filtered, and the word Ave in their Address Lines.
Clear the check mark from the 'matching-cases' CheckBox, then click the Filter command button again.
Now, all items except those records having the word Ave will appear in the List Box.
If you review the code we added to the Form Module, you’ll notice that the xSource() array variable is declared in the global section of the module. This allows the same data to be accessed and reused across both Subroutines within the Form Module.
In the Form_Load() Event Procedure, we have declared three Variables as fixed-length String Type (see the declaration line given below).
Dim FName As String * 12, LName As String * 12, Add As String * 20
When we read employee names and Addresses into these Variables, the values will be left-justified inside the Variable, and the balance area of the declared size will be space-filled to the right. This method will space out items at a fixed distance from each other and properly align them when displayed.
It’s important to use a fixed-width font, such as Courier New, for the List Box display. This ensures that all text lines are properly aligned and easy to read. We’ve already configured this setting in Step 4 above.
If you click the Filter Command Button when the TextBox is empty, then the program terminates; otherwise, it calls the GetFiltered() Function and passes the parameter values.
The output Values are returned in the xTarget Array, and the next steps format the Value List and display it in the List Box.
