Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Find or Filter Data on Form

Introduction

When I started learning Microsoft Access in 1996 the first challenge that I faced was how to find a particular record on the Data Editing Screen or Filter a group of records on some condition.

I was asked to develop a System for the Vehicles Division of our Company, for tracking pending Orders and Receipts of Heavy Equipment and Vehicles. If I am the one who is going to use the System, then somehow I could manage to find the information that I want to work with and nobody will know how much time I spent doing that. But it is going to be used by someone else and it is my job to make it as user-friendly as possible. Even though I have good knowledge of BASIC Language at that time, knew nothing about Access Basic (MS-Access Ver.2 language, a primitive form of Visual Basic).

I struggled with the problem for a while and finally decided to have a look in the Northwind.mdb sample database for clues. There it was, on the Customer Phone List Form, the technique that I struggled with for so long. Within an Option Group Control, 27 Buttons with Labels A-Z, All to filter Records of Company Name starting with the respective letter on the Button or to remove the filter using the All labeled button. The Options Group is linked to a Macro. When any of the buttons on the option group is clicked, the Company Names starting with that letter are filtered from the Customer List.

There were no second thoughts on this and I straight away transplanted this method to my first MS-Access Project. It was developed without touching a single line of Access Basic Code, all automated procedures are by macros only.

We will look into the Finding/Filtering records using three different methods on the same Form. You can use any one of the three methods or all of them in your Project. We will use the Products Table from the Northwind.mdb sample Database.

Get Sample Table and Design a Form

  1. Import the Products Table from the Northwind.mdb Database. Visit the Page Saving Data on Forms not in Table to find the location reference of the Northwind.mdb database, if you are not sure where to find it.

  2. Click on the Table and select Form from the Insert menu and select Autoform: Columnar from the displayed list, click OK to create the Form and save it with the suggested name: Products.

  3. Display the Form Header/Footer Sections, if not already visible. Select Form Header/Footer from View Menu.

  4. Create a Label on the Header Section of the Form and type Products Lists as Caption. Click on the Label and change the font size to 20 or more, to your liking, and make it Bold using the Format Toolbar above.

    NB: If you would like to create a fancy 3D Style Heading then visit the Page: Shadow 3D Heading Style and follow the procedure explained there.

  5. Create a Command Button at the Footer Section of the Form. Display the Property Sheet of the Button (Alt+Enter or select Properties from View Menu). Change the Property Values as shown:

    • Name = cmdExit

    • Caption = Exit

  6. Design a Text Box and four Command Buttons on the Form, as shown in the shaded area of the Form. Change the property values of the Text Box and Buttons as given below:

  7. Click on the Text Box, Display the Property Sheet, and change the property values:

    • Name = xFind

    • Back Color = 0

    • Text Box child-Label Caption = Find / Filter Product Name

  8. Click on the first button, Display the Property Sheet, and change the property Values.

    • Name = FindPID

    • Caption = << Product ID

    • Fore Color = 128

  9. Click on the second button, Display the Property Sheet, and change the property Values.

    • Name = FindFirstLetter

    • Caption = << First Letter

    • Fore Color = 128

  10. Click on the third Button, Display the Property Sheet, and change the property Values.

    • Name = PatternMatch

    • Caption = << Pattern Match

    • Fore Color = 128

  11. Click on the fourth Button, Display the Property Sheet, and change the property Values.

    • Name = cmdReset

    • Caption = Reset Filter

  12. Display the Visual Basic Module of the form, select Tools > Macros > Visual Basic Editor when the Products Form is still in Design View.

  13. VBA Code of Form Class Module

  14. Copy and paste the following Code into the VB Module of the Form and save the Form:

Private Sub cmdExit_Click()
DoCmd.Close
End Sub

Private Sub cmdReset_Click()
'Remove Filter effect
'Clear Text Box

Me!xFind = Null
Me.FilterOn = False
End Sub

Private Sub FindPID_Click()
'Find Record matching Product ID
Dim m_find, rst As Recordset

m_find = Me![xFind]
If IsNull(m_find) Then
   Me.FilterOn = False
   Exit Sub
End If

If Val(m_find) = 0 Then
  MsgBox "Give Product ID Number..!"
   Exit Sub
End If

If Val(m_find) > 0 Then
   Set rst = Me.RecordsetClone
   rst.FindFirst "ProductID = " & m_find
   If Not rst.NoMatch Then
      Me.Bookmark = rst.Bookmark
   End If
   rst.Close
End If

End Sub

Private Sub FindfirstLetter_Click()
'Filter Names matching First character
Dim xfirstletter

xfirstletter = Me![xFind]
If IsNull(xfirstletter) Then
  Me.FilterOn = False
  Exit Sub
End If
If Val(xfirstletter) > 0 Then
   Exit Sub
End If

xfirstletter = Left(xfirstletter, 1)
Me.FilterOn = False
Me.Filter = "Products.ProductName Like '" & xfirstletter & "*'"
Me.FilterOn = True

End Sub

Private Sub PatternMatch_Click()
'Filter Names matching the group of characters
'anywhere within the Name
Dim xpatternmatch

xpatternmatch = Me![xFind]
If IsNull(xpatternmatch) Then
  Me.FilterOn = False
  Exit Sub
End If

Me.FilterOn = False
Me.Filter = "Products.ProductName Like '*" & xpatternmatch & "*'"
Me.FilterOn = True
End Sub

How it Works.

  1. Usage of << Product ID Button.

    • Click the Button with a number less than or equal to the Product Code range of values in the Text Box.

    • If clicked with Text Value it will ask for Product ID Number.

    • If clicked when the TextBox is empty, it is the same as clicking Filter Reset Button.

  2. Usage of << First Letter Button.

    • Click the Button with any Alphabet A to Z or a to z in the Text Box.

    • If more than one character is entered only the first character will be taken.

    • If a Numeric Value is entered the filter action will be ignored.

    • If clicked when the TextBox is empty, it is the same as clicking the Filter Reset Button.

  3. Usage of << Pattern Match Button.

    • Click the Button with a group of characters that match anywhere within the Product Name.

    • If clicked when the text box is empty, it is the same as clicking the Filter Reset Button.

  4. Usage of Filter Reset Button.

    • Resets the earlier applied Filter action.

    • Empties the Text Box Control.

Share:

7 comments:

  1. The Example and Code is fully tested. Be specific on your issues perhaps I could help to sort it out.

    If you need a demo version of the example give your e-mail address.

    Regards,

    ReplyDelete
  2. I'm trying to adapt this code to an excel form. the Debugger says me.filter.on is not defined. I am only using the pattern match button. Is there a difference in the tags in excel? Any idea what I need to do to define me.filter?

    I can send a sample file if that would help.

    ReplyDelete
  3. The Syntax of Advanced Filter in Excel is like the sample code below:

    Range("A1:J78").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
    Range("L1:L2"), Unique:=False


    Range("A1:J78") is the database Range. Range("L1:L2") is the Criteria Range and Cell L1 will have the Field Name to look for data and L2 is the actual data to look for Like:

    CategoryID
    3

    Forward a copy of the Excel file to aprpillai@msaccesstips.com.

    Regards,

    ReplyDelete
  4. [...] Select Form Header/Footer from msaccesstips.com [...]

    ReplyDelete
  5. Hello I am using Access 2007, and I'm experiencing an error at this syntax
    rst.FindFirst "CodProdus = " & m_find
    When I debug the system is warning me about missing operator.
    Could you please give a suggestion

    ReplyDelete
  6. The statement Syntax is correct if m_find is numeric data

    rst.FindFirst “CodProdus = ” & m_find

    but the above statement will not generate any error.

    If m_find is text data then it should be

    rst.FindFirst “CodProdus = '” & m_find & "'"

    The Openrecordset statement should be something like the sample given below, if not correct it:


    Dim db As Database
    Dim rst As Recordset

    Set db = Currentdb
    Set rst = db.OpenRecordSet("myTable")
    rst.FindFirst “CodProdus = '” & m_find & "'"

    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