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.

VBA Code of Form Class Module

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

• Useful Report Functions
• Reminder Pop Up
• MS-Access & Graph Charts-2
• MS-Access & Graph Charts
• Reports Page Border