Find or Filter Data on Form

Introduction

When I began learning Microsoft Access in 1996, the first challenge I encountered was figuring out how to locate a specific record on a data entry form or how to filter some records based on certain conditions.

I was assigned to develop a system for the Vehicles Division of our company to track pending orders and receipts of heavy equipment and vehicles. If I am the only one using the system, I could have managed to locate the required information on my own—regardless of how long it took—without anyone noticing. But since the system was intended for use by others, it was my responsibility to ensure that it was as user-friendly and efficient as possible. Although I had a solid background in the BASIC programming language at the time, I had no prior experience with Access Basic (the programming language in MS Access Version 2, an early form of Visual Basic).

I struggled with the problem for some time before deciding to explore the Northwind.mdb sample database for ideas. There, on the Customer Phone List form, I discovered the very technique I had been searching for. An Option Group Control was used, featuring 27 buttons labeled A to Z, along with an "All" button to remove the filter. When a user clicks one of the lettered buttons, the form filters the records to display only company names beginning with that letter. The option group was linked to a macro that executed the filtering action. This simple yet effective method provided the solution I had been looking for.

There were no second thoughts on this, and I immediately applied 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.

In this exercise, we will explore three different methods for finding and filtering records—all implemented on the same form. You may choose to use any one of these techniques individually or incorporate all of them into your own project, depending on your needs. For demonstration purposes, we will use the Products table from the Northwind.mdb sample database.

Get a Sample Table and Design a Form.

1. Import the Products table from the Northwind.mdb database. If you're unsure about the file's location, refer to the article titled Saving Data on Forms Not in Table for help.

2. Once the table is imported, select it in the Navigation Pane, then go to the Insert menu and choose Form. From the list of available options, select AutoForm: Columnar, and click OK to generate the form. Save the newly created form with the name Products.

   

3. Display the Form Header/Footer sections, if they are not already visible. To do this, go to the View menu and select Form Header/Footer.

   Next, add a label control to the Form Header section and set its Caption to "Products List". Click on the label, then use the Format toolbar to increase the font size to 20 (or higher, depending on your preference) and apply bold styling for emphasis.

   Tip: If you’d like to give the heading a stylish 3D shadow effect, refer to the article [Shadow 3D Heading Style] for step-by-step instructions.

4. Add a Command Button to the Footer section of the form.

   To configure the button:

   1. Select the button after placing it on the form.

   2. Press Alt + Enter or choose Properties from the View menu to open the Property Sheet.

   3. Update the button's properties with the following values:

   • Name = cmdExit

   • Caption = Exit

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

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

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

   • Name = FindPID

   • Caption = << Product ID

   • Fore Color = 128

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

   • Name = FindFirstLetter

   • Caption = << First Letter

   • Fore Color = 128

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

   • Name = PatternMatch

   • Caption = << Pattern Match

   • Fore Color = 128

10. Click on the fourth Button, display the Property Sheet, and change the property Values.

    • Name = cmdReset

    • Caption = Reset Filter

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

12. 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 the Product ID Number.

   • If clicked when the TextBox is empty, it is the same as clicking the 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 the 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