Ms-Access Advanced VBA Programming Techniques, Tips and Tricks.

Ads

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

Trending

Search

Infolinks Text Ads

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

Activity Dates and Quarterly Reports

There are four Quarters in a Year: Jan - Mar = 1st Quarter Apr - Jun = 2nd Jul - Sep = 3rd Oct - Dec = 4th First three months of the yea...

Labels

Blog Archive

Recent Posts