Filter Function Output In Listbox-2

Introduction

Last week, we explored the Filter() function with a simple example, demonstrating 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:

Right-click to open Large Image in New Tab/Window.

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.

1. Form_Load Event:
   When the Form opens, the first Subroutine runs from the Form_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.

2. Command Button Click Event:
   The second Subroutine runs when the user clicks the Command Button. It uses the Filter() 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.

Right-click to open Large Image in New Tab/Window.

The Address Book Project.

Let us prepare for the Address Book's Quick Find Project.

The Design Task

1. Import Employees Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample database.

2. Open a new Form in Design View.

3. Select the List Box Control from the Toolbox and draw a List Box as shown in the design above.

4. 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

5. Position the Child Label attached to the List Box above and change the Caption value to Address Book.

6. Draw a Text Box below the List Box. Change the Name Property value of the TextBox to xFind. Position the Child Label above the Text Box and change the Caption value to Search Text/ALL.

7. 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.

8. 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.

9. Display the Code Module of the Form (View ->Code).

10. 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 Sub

11. Save the Form with the name Filter Form.

12. 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 Function
    

    Filter() 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 named GetFiltered() 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

13. Open the Filter Form in Normal View.

14. 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 Name and Addresses of all Employees from the xSource() Array loaded from the Employees Table.

15. Enter the text Ave in the Text Box and ensure that the 'matching cases' check box is in the selected state.

16. Click on the Command Button.

    This time you will find that only two employees (Nancy & Laura) names and addresses are filtered, and the word Ave is appearing in their Address Lines.

17. Clear the check mark from the 'matching-cases' check box, then click the Filter command button again.

Now, all items except those containing 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.

• Hiding Report Lines Conditionally-3
• Hiding Report Lines Conditionally-2
• Hiding Report Lines Conditionally
• Network and Report Page Setup-3
• Network and Report Page Setup-2