Find or Filter Data on Form

When I started learning Microsoft Access in 1996 the first challenge that I have 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 Equipments and Vehicles. If I am the one who is going to use the System, then some how I could manage to find the information that I want to work with and nobody will know how much time I spent for 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 was struggling 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 Option Group is linked to a Macro (Customer Phone List) for testing, which button on the option group is clicked, and to filter the Company Names starting with that letter. There were no second thoughts on this and I straight away transplanted this method on my first MS-Access Project. It was developed without touching a single line of Access Basic Code, all automated procedures are run with macros and this is still in use.


We will look into the Finding or 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.

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 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 List as Caption. Click on the Label and change the character size to 20 or more, to your liking, and make it Bold using the Format Toolbar above.



NB: If you would like to create the same 3D Style Heading, 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. 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

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 Text Box is empty, it is 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 Numeric Value is entered the filter action will be ignored.


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



3. Usage of << Pattern Match Button.



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


• If clicked when the text box is empty, it is 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