Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Filter Function Output In Listbox-2

Last week we have seen the usage of Filter() Function with a simple example and I hope you understood how it works. We have assigned constant values to the Source Array elements directly to keep the VBA Code as simple as possible.

We can filter data on Forms by setting Criteria on the Filter Property of Forms. Similarly, we can use conditions in Queries to filter information from Tables as well.

But, the Filter() Function gives a unique way of filtering data from an Array of information loaded from Tables or Queries and create output quickly based on matching or non-matching options.

Let us try out the Filter() Function in an Address Book Application to quickly find persons or places that matches with the specified search text and display them in a List Box. We will use Names and Addresses from the Employees Table of Northwind.mdb sample database for our experiment.

Following is the User Interface design that we planned to create and explains how the User will interact with it to display information quickly on the Form.

We will design a Form with a List Box, a Text Box Control, a Check-Box Control and a Command Button for our experiment. An image of such a Form in Design View is given below:

When the Form is open in normal view the List Box and Text Box Controls will be empty. The User can enter the word ALL in the Text Box Control and click the Command Button to display the Name and Addresses of all Employees in the List Box.

Or

The User can enter a word or phrase, like part of a Name or Address, that can match anywhere within the Name and Address text, and click on the Command Button to filter out the matching items and to display them in the List Box.

If the Matching Cases Check-Box is in selected state then the Filter action will select records that matches with the search text given in the Text Box Control otherwise it will select all records that do not match with the search text.

To provide the User with the above facility we need two Subroutines on the Form's Code Module and a User Defined Function in Standard Module of the Database to use the Filter() Function.

When the User opens the above Form the first Sub-Routine is run from the Form_Load() EventProcedure to read the data (First Name, Last Name & Address) from Employees Table, join all the three field values into a single row of text and load them into a Singly Dimensioned Array Variable in Memory. This data will remain in memory till the User closes the Form.

The second Sub-Routine is run when the User clicks on the Command Button to extract information from the Source Array with the help of Filter() function, based on the search text entered into the Text Box Control.

The Filter() Function will extract the entries that matches with the search Text in the Text Box Control, from the Source Array Variable, and save the output into the target variable xTarget. All we have to do is to take these values, format and insert as Row Source Property Value to display them in the List Box.

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

  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 Tool Box and draw a List Box as shown on the design above.
  4. While the List Box is in 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 of the Text Box 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 to 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 given exactly as I have mentioned 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:
    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 in 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
    

    We cannot use the Filter() Function in the Form Module because the function name clashes with the Form Property Filter.

    We have inserted the Filter() Function in the Standard Module enveloped in a User Defined Function GetFiltered() with necessary Parameters so that we can call it from Form Module. The first parameter to the Function is passed By Reference so that it can use the Source Array values directly.

  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 see that the Matching Cases check box is in selected state.
  16. Click on the Command Button.

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

  17. Remove the check-mark from the Matching Cases check box and click on the Filter Command Button again.

Now, all items except the lines with the word Ave are listed in the List Box.

If you go through the Programs that we have copied into the Form Module you can see that we have declared the xSource() Array Variable in the Global area of the Module so that we can use the data in both Sub-Routines in 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 Name and Address into these Variables the values will be left justified inside the Variable and balance area of the declared size will be space filled to the right. This method will space out items in fixed distance from each other and properly aligned when displayed.

It is important that we use a Fixed Width Font, like Courier New, for the List Box display and we set this in Step-4 above.

If you click the Filter Command Button when the Text Box is empty then the program terminates, otherwise it calls the GetFiltered() Function by passing the parameter values.

The output Values are returned in the xTarget Array and the next steps formats the Value List and displays them in the List Box.

Share:

2 comments:

  1. Unfortunately it did not work for me. There was something wrong with the xTarget events on click. I tried fixing it but it did not work. Also, some of the code, I had to edit to get working. For example, the OnLoad() event. Nothing showed up because there was no command that told the listbox to add an item. (Listbox1.AddItem strName)

    ReplyDelete
  2. If you can copy some sample data and the Form with the modified code into a new database and forward it to me (in .zip format) to my email address aprpillai@gmail.com, I can take a look at it. I shall correct the code and sent it back to you.

    Regards,
    a.p.r. pillai

    ReplyDelete

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Accesstips Menus and Toolbars MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery Array List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands Data ms-access functions msaccess functions msaccess graphs msaccess reporttricks Calculation msaccessprocess security advanced Access Security Custom Functions Data Type Macros Menus Property Report Top Values VBA Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Field Type Fields Form Join Microsoft Numbering System Objects Records Security Split SubForm Table Utility Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Class Module Color Palette Conditional Formatting Controls Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Ms-Access VBA Class Object Arrays

Last Week we had a brief introduction of a simple Custom Class Object with only two Variables, for Length and Width Values. A Method for ...

Labels

Blog Archive

Recent Posts