Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Filter by Character and Sort

Introduction

Search, Find, Filter, and Sort operations are necessary to organize data into a manageable form so that finding the required information becomes easier. We have seen some of these actions through the following earlier Posts Titled:

Now, we will try a different and easier method (for the User) for filtering data by keying in one or more characters into a TextBox and getting an instant result, rather than Typing Search Text on Controls and Clicking on different Buttons or Tool Bars.

When the first character is entered into a TextBox (say the letter F) all Records with names starting with that letter will be filtered instantly. If the filtered list is large, with unwanted items, then the next character that matches can be entered to further narrow down the list and this method can be repeated.

If the Backspace key is pressed to delete the last character or more characters entered in the TextBox; the filtered list will expand progressively and reverse this process. Since this action is instantaneous the User can get the result the moment she touches a key.

If we need values from one of these filtered records transferred to some other open form, then it can be done by writing a routine on the Double-Click Action at the Form level as well. Double-Clicking on the left border of a particular record (Record Selector) can trigger this action and can transfer required values into a different open Form. We will see the sample code for this action at the end of this Article.

Sample Image of Form

The only limitation is that we have created this method for use on a Tabular type Form so that several records can be viewed at one time. The earlier methods (see the references given above) were created for use on Forms with a single record view.

Normally, the search or filter operation of this type will be implemented in a single field, like the Employee Code, Company Code, or Company Name; that has more relevance while looking for information.

But, here we will try to go one step further by providing a list of Field Names of the Source Object (Table or Query) attached to the Form (in a Combo Box) so that the user can select the Field that she likes to filter the data on. This will provide more choices and flexibility.

To try this example, we need the Customers Table from the Northwind.mdb sample database. If you don't know the location of this file, then you can find this in last week's post, the third item out of the three links given above.

Sample Data Table, Query & Form.

  1. Import the Customers Table from the Northwind database.

  2. 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;
    
  3. 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 would like to use some other Fields in the Query you may do so.

  4. Expand the Form Footer Section, if it is not visible (View - - >Form Header/Footer).

  5. If the Tool Box is not visible display it by selecting Toolbox from View Menu.

  6. 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 we select the Combo Box Tool.

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

  8. Change the Caption value of the Child Label on the Combo Box to Filter/Sort Field:.

  9. Click on the Combo Box to select it and display its Property Sheet (View- ->Properties).

  10. Change the following Property Values as given below:

    • Name = cboFields
    • Row Source Type = Field List.
    • Row Source = CustomersQ.
    • Column Width = 1.5"
  11. Create a Text Box below the Combo Box.

  12. Change the Caption Value of the Child Label to FilterText:.

  13. Change the Name Property Value of the Text Box to FilterText.

  14. Create an Option Group Control, with two buttons with Label Captions ASC and DESC for Sorting option Ascending or Descending order.

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

  16. Display the VBA Code Module of the Form (View - ->Code or Alt+F11).

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

    Test Run the Demo Form

  18. Open the Form in Normal View.

  19. The First Name field will appear as the default value in the Combo Box control.

  20. Click on the Text Box below the Combo Box to set the focus on it.

  21. Type the Character F and you will see that all the 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 field values are different and three items have the same letter in the second character position. Besides that, the field values are correctly sorted, in selected (Ascending/Descending) Alphabetical Order.

  22. 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 this way if needed.

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

  24. Press 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 you may select that Field's name from the Combo Box above before trying the filter action explained from Step 19 onwards.

You may try it on the third Field Job Title for different results.

Download Demo Database



Share:

4 comments:

  1. Thanks...

    I really need it,thank you very much!!!...

    ReplyDelete
  2. [...] is an example with Code: Filter by Character and Sort __________________ http://www.msaccesstips.com (Learn MS-Access Tips and Tricks) Learn Advanced [...]

    ReplyDelete
  3. i've try the steps & code, but when in form view the code is not fuction. Please help me.

    ReplyDelete
  4. Copy some sample data and the Form with the Code in a database and forward it to me. Email address: aprpillai@gmail.com. Let me have a look at it.

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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