Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Filter by Character and Sort

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

Now, we will try a different and easy method (for the User) for filtering data by keying in one or more characters into a Text Box and obtain 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 Text Box (say the letter F) all Records with names starting with that letter will be filtered instantly. If the filtered list is large then the next character that matches can be entered to further narrow down the list and this method can be repeated.

If Backspace key is pressed to delete the last character or more characters entered in the Text Box; 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 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 sample Code for this action at the end of this Article.

A sample image of a Form is given below.

Click to Enlarge

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 using on Forms with single record view.

Normally, the search or filter operation of this type will be concentrated on 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.

  1. Import the Customers Table.
  2. Design a Tabular Form as shown in the Image given above. I have selected only the first three fields from the Table for this example. If you would like to use all the Fields in the Table you may do so.
  3. Expand the Form Footer Section, if it is not visible (View - - >Form Header/Footer).
  4. If the Tool Box is not visible display it by selecting Toolbox from View Menu.
  5. De-select the Control Wizards Button (the top right one); if it is already in selected state on the ToolBox, so that the Wizard will not start when we select the Combo Box Tool.
  6. 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.
  7. Change the Caption value of the Child Label of the Combo Box to Filter/Sort Field: .
  8. Click on the Combo Box to select it and display its Property Sheet (View- ->Properties).
  9. Change the following Property Values as given below:
    • Name = cboFields
    • Row Source Type = Field List
    • Row Source = Customers
    • Column Width = 1"
  10. Create a Text Box below the Combo Box.
  11. Change the Caption Value of the Child Label to FilterText: .
  12. Change the Name Property Value of the Text Box to FilterText.
  13. Create a Command Button to the right of the Text Box and change the following Property Values as shown:
    • Name = cmdClose
    • Caption = Close
  14. Display the VBA Code Module of the Form (View - ->Code or Alt+F11).
  15. 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()
    End Sub
    Private Sub FilterText_KeyUp(KeyCode As Integer, Shift As Integer)
    Dim i As Integer, tmp
    On Error GoTo FilterText_KeyUp_Err
    i = KeyCode
    Select Case i
        Case 8 'backspace key
            If Len(x) = 1 Or Len(x) = 0 Then
                x = ""
                x = Left(x, Len(x) - 1)
            End If
            GoSub setfilter
        Case 37, 39 'eft 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
    Exit Sub
      tmp = Nz(Me!cboFields, "")
      If Len(Nz(x, "")) = 0 Then
            Me.FilterOn = False
            Me.Filter = Me![cboFields] & " like '" & x & "*'"
            Me.FilterOn = True
      End If
      Me.OrderBy = Me.RecordSource & ". " & Me!cboFields
      Me.OrderByOn = True
      Me![cboFields] = tmp
      SendKeys "{END}"
    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 = FalseEnd Sub
    Private Sub Form_Load()
        Application.SetOption "Behavior Entering Field", 2
        Set rst = Me.RecordsetClone
        Me!cboFields = rst.Fields(0).Name
    End Sub
  16. Open the Form in normal View.
  17. The CustomerID Field will appear as default value in the Combo Box control.
  18. Click on the Text Box below the Combo Box to set the focus on it.
  19. Type the Character F and you will see that all the records with CustomerID values starting with the letter F is filtered.

    If you look at the CustomerID Field values of the filtered records you may find that the second character of the CustomerIDs are different and three items have the letter R in the second character position. Besides that the CustomerIDs are correctly sorted in Alphabetical Order.

  20. Type the Character R next to the character F in the FilterText control and the list narrows with starting CustomerID characters FR. You can further filter and narrow down the list progressively this way, if needed.
  21. Press Backspace Key to delete the character R and to leave F character alone in the Text Box. The list will expand and all items starting with the letter F are back in the List.
  22. Press Backspace Key again to delete the character F from the Text Box. 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 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.

Since the CustomerIDs are created based on the CompanyName Field; trying on both those fields will give almost same result.

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

The sample code given below assumes that you have Double-Clicked on the left border of a record (Record Selector) to transfer the CustomerID code into CusID field of another open Form; Orders.

Private Sub Form_DblClick(Cancel As Integer)
Forms!Orders![CusID] = Me![CustomerID]
End Sub


  1. Thanks...

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

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

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

  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.


Comments are subject to moderation before publishing.


Subscribe in a reader
Your email address:

Delivered by FeedBurner

Blog Directory Visit blogadda.com to discover Indian blogs


Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email


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

Featured Post

WithEvents and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captu...


Blog Archive

Recent Posts