Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Filter by Character and Sort

Search, Find, Filter and Sort operations are necessary to organize data into manageable form so that finding required information becomes easy. We have seen some of these actions through the following earlier Posts Titled:

Now, we will try a different and easy method (for the User) for filtering data by keying in one or more characters into a Text Box and obtain instant result, rather than Typing Search Text on Controls and Clicking on different Buttons or Tool Bars.

When the first character is entered into a Text Box (say the letter F) all Records with names starting with that letter will be filtered instantly. If the filtered list is large then the next character that matches can be entered to further narrow down the list and this method can be repeated.

If Backspace key is pressed to delete the last character or more characters entered in the Text Box; the filtered list will expand progressively and reverse this process. Since, this action is instantaneous the User can get the result the moment she touches a key.

If we need values from one of these filtered records transferred to some other open form then it can be done by writing a routine on the Double-Click Action at Form level as well. Double-Clicking on the left border of a particular record (Record Selector) can trigger this action and can transfer required values into a different open Form. We will see sample Code for this action at the end of this Article.

A sample image of a Form is given below.

Click to Enlarge

The only limitation is that we have created this method for use on a Tabular type Form so that several records can be viewed at one time. The earlier methods (see the references given above) were created for using on Forms with single record view.

Normally, the search or filter operation of this type will be concentrated on a single field, like the Employee Code, Company Code or Company Name; that has more relevance while looking for information.

But, here we will try to go one step further by providing a list of Field Names of the Source Object (Table or Query) attached to the Form (in a Combo Box), so that the User can select the Field that she likes to filter the data on. This will provide more choices and flexibility.

To try this example we need the Customers Table from the Northwind.mdb sample database. If you don't know the location of this file then you can find this in last week's Post, the third item out of the three links given above.

  1. Import the Customers Table.
  2. Design a Tabular Form as shown in the Image given above. I have selected only the first three fields from the Table for this example. If you would like to use all the Fields in the Table you may do so.
  3. Expand the Form Footer Section, if it is not visible (View - - >Form Header/Footer).
  4. If the Tool Box is not visible display it by selecting Toolbox from View Menu.
  5. De-select the Control Wizards Button (the top right one); if it is already in selected state on the ToolBox, so that the Wizard will not start when we select the Combo Box Tool.
  6. Select the Combo Box Tool from the Toolbox and draw a Combo Box on the Footer Section of the Form as shown in the design above.
  7. Change the Caption value of the Child Label of the Combo Box to Filter/Sort Field: .
  8. Click on the Combo Box to select it and display its Property Sheet (View- ->Properties).
  9. Change the following Property Values as given below:
    • Name = cboFields
    • Row Source Type = Field List
    • Row Source = Customers
    • Column Width = 1"
  10. Create a Text Box below the Combo Box.
  11. Change the Caption Value of the Child Label to FilterText: .
  12. Change the Name Property Value of the Text Box to FilterText.
  13. Create a Command Button to the right of the Text Box and change the following Property Values as shown:
    • Name = cmdClose
    • Caption = Close
  14. Display the VBA Code Module of the Form (View - ->Code or Alt+F11).
  15. Copy and Paste the following Code into the Module and Save the Form with the name Customers or any other name you prefer.
    'Global declaration
    Dim x, rst As Recordset
    
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub
    
    Private Sub FilterText_KeyUp(KeyCode As Integer, Shift As Integer)
    Dim i As Integer, tmp
    
    On Error GoTo FilterText_KeyUp_Err
    i = KeyCode
    
    Select Case i
        Case 8 'backspace key
            Me.Refresh
            If Len(x) = 1 Or Len(x) = 0 Then
                x = ""
            Else
                x = Left(x, Len(x) - 1)
            End If
            GoSub setfilter
        Case 37, 39 'eft and right arrow keys
            SendKeys "{END}" 'ignore action
        Case 32, 48 To 57, 65 To 90, 97 To 122 'space, 0 to 9, A to Z, a to z keys
            x = x & Chr$(i)
            Me![FilterText] = x
            GoSub setfilter
    End Select
    
    FilterText_KeyUp_Exit:
    Exit Sub
    
    setfilter:
      Me.Refresh
      tmp = Nz(Me!cboFields, "")
      If Len(Nz(x, "")) = 0 Then
            Me.FilterOn = False
      Else
            Me.Filter = Me![cboFields] & " like '" & x & "*'"
            Me.FilterOn = True
      End If
      Me.OrderBy = Me.RecordSource & ". " & Me!cboFields
      Me.OrderByOn = True
      Me![cboFields] = tmp
      Me.FilterText.SetFocus
      SendKeys "{END}"
    Return
    
    FilterText_KeyUp_Err:
    MsgBox Err.Description, , "FilterText_KeyUp()"
    Resume FilterText_KeyUp_Exit
    End Sub
    
    Private Sub Form_Close()    Application.SetOption "Behavior Entering Field", 0    Me.FilterOn = False    Me.OrderByOn = FalseEnd Sub
    
    Private Sub Form_Load()
        Application.SetOption "Behavior Entering Field", 2
        Set rst = Me.RecordsetClone
        Me!cboFields = rst.Fields(0).Name
        rst.Close
    End Sub
  16. Open the Form in normal View.
  17. The CustomerID Field will appear as default value in the Combo Box control.
  18. Click on the Text Box below the Combo Box to set the focus on it.
  19. Type the Character F and you will see that all the records with CustomerID values starting with the letter F is filtered.

    If you look at the CustomerID Field values of the filtered records you may find that the second character of the CustomerIDs are different and three items have the letter R in the second character position. Besides that the CustomerIDs are correctly sorted in Alphabetical Order.

  20. Type the Character R next to the character F in the FilterText control and the list narrows with starting CustomerID characters FR. You can further filter and narrow down the list progressively this way, if needed.
  21. Press Backspace Key to delete the character R and to leave F character alone in the Text Box. The list will expand and all items starting with the letter F are back in the List.
  22. Press Backspace Key again to delete the character F from the Text Box. This time the Filter action is removed and all the records are back in the Form.

NB: The Filter Criteria Text Values are limited to the Characters 0 to 9 and A to Z only.

If you want to try the Filter action on one of the other two fields you may select that Field's name from the Combo Box above before trying the filter action explained from Step-19 onwards.

Since the CustomerIDs are created based on the CompanyName Field; trying on both those fields will give almost same result.

You may try it on the third Field ContactName for different results.

The sample code given below assumes that you have Double-Clicked on the left border of a record (Record Selector) to transfer the CustomerID code into CusID field of another open Form; Orders.

Private Sub Form_DblClick(Cancel As Integer)
Forms!Orders![CusID] = Me![CustomerID]
End Sub
Share:

Animating Label on Search Success

We have seen how to find or filter records using values entered into a text box. We have used three different methods to find or filter data after entering text or numeric search values into a text box. But, we have not used any visual indicator to announce that the search operation was successful or not. If the search operation was successful then the record that matches the criteria will become current or filtered, that was the only clue to know that the search was successful.

Here, we will see how to animate a Label few times with an indicative message; announcing the search operation was successful or not..

  1. To try an example with the sample VBA Code given below; import the Customers Table and Customers Form from Northwind.mdb sample database. If you are not sure, where to find this database, check the location C:\Program Files\Microsoft Office\Office11\Samples Folder (in Office2003).
  2. Open the Customers form in design view.
  3. Expand the Form Footer Section to create few controls for Quick Search operation and for our magic label animation. Check the sample image of the Form given below with controls added at the Footer of the Form:
  4. Draw a Label on the Form Footer Section and change its Caption to Customer ID to Find: .
  5. Draw a Text Box to the right of the Label and change its Name Property (View - - > Properties or Alt+Enter) Value to xFind.
  6. Create a Command Button to the right of the Text Box and change its Name Property Value to cmdFind. Change the Caption of the Command Button to << Find.
  7. Create a Label below the Text Box and change the following Property Values shown against each Property:
    • Name = lblMsg
    • Caption = x
    • Visible = False
  8. Display the Code Module of the Form (View - - > Code or Alt+F11).
  9. Copy and Paste the following Code into the Form Module and save the Form:
    'Global Declarations
    Dim backcolor As Long, forecolor As Long
    Dim L As Integer
    
    Private Sub cmdFind_Click()
    '---------------------------------------------------------------
    'Author : a.p.r. pillai
    'Date    : April-2009
    'URL     : www.msaccesstips.com
    'All Rights Reserved by www.msaccesstips.com
    '---------------------------------------------------------------
    Dim m_Find, rst As Recordset
    
    On Error GoTo cmdFind_Click_Err
    
    backcolor = -2147483633
    
    m_Find = Me![xFind]
    If IsNull(m_Find) Then
        Exit Sub
    End If
    Set rst = Me.RecordsetClone
    rst.FindFirst "CustomerID = '" & [m_Find] & "'"
    If Not rst.NoMatch Then
        Me.Bookmark = rst.Bookmark
        Me.lblMsg.Caption = "** Successful ***"
        forecolor = 16711680
        Me.lblMsg.forecolor = forecolor
    Else
        Me.lblMsg.Caption = "Sorry, Not found...!"
        forecolor = 255
        Me.lblMsg.forecolor = forecolor
    End If
    L = 0
    Me.lblMsg.Visible = True
    Me.TimerInterval = 250
    
    cmdFind_Click_Exit:
    Exit Sub
    
    cmdFind_Click_Err:
    MsgBox Err.Description, , "cmdFind_Click()"
    Resume cmdFind_Click_Exit
    End Sub
    
    Private Sub Form_Timer()
    L = L + 1
    Select Case L
        Case 1, 3, 5, 7, 9, 11, 13, 15, 17
            Me.lblMsg.Visible = True
        Case 2, 4, 6, 8, 10, 12, 14, 16, 18
            Me.lblMsg.Visible = False
        Case 19
           Me.lblMsg.forecolor = forecolor
           Me.lblMsg.Visible = True
           Me.TimerInterval = 0
    End Select
    End Sub
    
    Private Sub xFind_GotFocus()
         Me.lblMsg.Visible = False
    End Sub

    The first two lines of Code should go at the Global level of the Form Module.

  10. Open the Customers form in normal View to try out our creation. When you open the form the Label that we have created below the Text Box will not be visible.
  11. Type record number 55 in the Record Navigation control below.
  12. Highlight the CustomerID code and press Ctrl+C to copy the Customer Code into Clipboard.
  13. Type 1 in the record navigation control to make the first record as current.
  14. Click on the Text Search Control at the Form Footer to select it and press Ctrl+V to Paste the Customer Code from Clipboard.
  15. Click on the Command Button to find the first record that matches the Customer Code.

    If the search operation was successful, the first record that matches the CustomerID will become current and the Label below the Text Box will be visible and will flash nine times with the text message ** Successful ** and stays on the screen.

  16. Make the first record as current again and make some change in the CustomerID in the search Text Box so that the search operation will fail with the modified Value.
  17. Click on the Command Button to search for the wrong CustomerID Code.

This time we will get the Sorry, Not Found. . .! Message flashing nine times and the message stays on the screen.

In this example we have made the label visible and hidden intermittently within an interval time of 250 Milliseconds. This method is ideal on all type of Forms with different backgrounds, like the one we have used with a Background picture.

We can make the Label flash by changing the text Color (rather than hiding and displaying the label as we did in the above example) with the same timing mechanism, if the Form background have a particular Color.

  1. Create a copy of the Customers Form with the name Customers2.
  2. Open the Form in Design View and display the Form's Property Sheet.
  3. Find the Picture Property and delete the .WMF image file path name. This action will display a message asking to reconfirm the delete action and respond to remove the entry.
  4. Remove the After Update property value also. There is a Macro attached here to run on the after update event of the form.
  5. Without closing the Property Sheet Click on the Footer of the Form and change Back Color Property value to -2147483633. This is the normal Form background color, when you open a new form in Design View.
  6. Click on the label below the Text Box and change the following Property Values:
    • Back Style = Transparent
    • Special Effect = Flat
    • Border Style = Transparent
  7. Replace the following Select Case . . . End Select code segment with the Code given below, in the Sub Form_Timer() Event Procedure:
    Select Case L
        Case 1, 3, 5, 7, 9, 11, 13, 15, 17
            Me.lblMsg.forecolor = forecolor
        Case 2, 4, 6, 8, 10, 12, 14, 16, 18
            Me.lblMsg.forecolor = backcolor
        Case 19
           Me.lblMsg.forecolor = forecolor
           Me.lblMsg.Visible = True
           Me.TimerInterval = 0
    End Select
    

    The lines under the first two Case. . . statements only we have changed in the above code segment to change the Color of the Font.

    The first statement gives the Font with Blue Color, if the search operation was successful otherwise Red Color.

    The line under the second Case. . . statement replaces the Font Color with the Form's Background Color making the text on the Label invisible.

    When the value in the control variable L is an Odd Number the line under the first Case. . . statement executes, when it is Even Number the line under the second Case. . . statement executes. This happens interchangeably at every 250 milliseconds interval; making the label animate nine times. When the value in the Control Variable L = 19; the Interval Timer is turned off and the Label's Font Color is changed according to the search result (Blue or Red) and keeps the Label visible on the Form till the User Clicks on the Text Box again to enter a new search criterion.

  8. Save the Form and open it in normal view.
  9. Repeat the procedure explained under Steps-10 to 17 above.

This time the Label will flash with Blue Color when the search result is successful and with Red Color when the search fails. In both situations the colors are exchanged with the background color intermittently with the Form Background Color Value -2147483633.

If you want to slow down the action then increase the interval time value from 250 Milliseconds to a higher Value or reduce it to flash the Label faster.

Share:

Form Background with Gradient Color

Form background pictures are not only used for giving the Form a different look but also for other useful purposes as well.

For example, if the user is keying in data from a pre-printed Document like a Telephone Bill or Electricity Bill etc.; it is a good idea to scan an image of this Document and use it as the Data Entry Form's background picture. The Data Fields can be positioned in appropriate locations on the image matching the physical Document so that it will be easier for the user to find information on the Document and key-in exactly on the same locations on the Form.

Microsoft Access Form Wizard have several images to use as background picture, but I didn't find the one that I need; the one with a gradient background. So, I thought I will make one of my own and use it.

The steps that I have taken are given below to create the image for the Form Background picture.

  1. Opened a new Microsoft Word Document and gone through the following steps to create a Gradient Bit Map Image:
  2. Selected Format - - >Background - - > Fill Effects.
  3. Under Colors options selected One Color.
  4. Clicked on the Color 1 drop down control and selected a light color.
  5. Used the Darker/Lighter slider control to adjust the color tone.
  6. Selected the Horizontal Radio Button under Shading Styles.
  7. Clicked on one of the Style Variants.
  8. Clicked OK to apply the selected shade as Background of the Word Document.
  9. Maximized the Word Document Window.
  10. Push the Prt Scrn key (at the top row right side). The full screen image is now captured and copied to Clipboard.
  11. Opened MS-Paint Program. You can find this in Start - - > Programs - - > Accessories.
  12. Displayed the Toolbox (View - ->Toolbox or press Ctrl+T).
  13. Selected the Select Tool (the one at Top right with a rectangle picture).
  14. Drawn a rectangle from left top corner of the gradient image area to right bottom corner to select the image large enough for my Form's width and height. If you find the width is not enough to cover your form then change the Page Orientation of the Word Document into Landscape before capturing it in MS-Paint.
  15. Used Ctrl+C to copy the selected area into Clipboard.
  16. Clicked outside the selected area to deselect the earlier selection.
  17. Selected Clear Image from Image Menu (Ctrl+Shift+N) to clear the Canvas. Inspected the right Scroll Bar to check whether it is positioned at the top or not. It was positioned slightly down so I dragged it up to the top. Similarly adjusted the Scroll bar at the bottom to position at the left end.
  18. Pressed Ctrl+V to paste the copied image from the Clipboard.
  19. Clicked outside the pasted image to de-select it.
  20. Dragged the Sizing control at the bottom of the Canvas up and stopped at the edge of the pasted image to eliminate the white area of the canvas.
  21. Similarly adjusted the right side of the image.
  22. Selected Save from File Menu to save the image at a convenient location with a name.

If you were with me so far then we will enter into the next stage of implementing the gradient image as background picture. But, there are some limitations to use images like this one as background picture, but we can overcome this with a little trick. If you are thinking about the increase in size of the database then you are in the right direction. If you are considering the idea of saving the image in .jpeg or .GIF format; then you are far ahead of me. Saving the image in .GIF format in MS-Paint will not give you a good quality image. If you have the software like Adobe Photoshop then you can make a better quality .GIF image.

We will do another trick to use the .bmp image itself to maintain the background image quality without affecting the database size.

But, first let us see how to apply the gradient image as the Form's Background picture.

Before opening your database if you would like to check the present size of your database you may open Windows Explorer, check and note down the current size of the database. I suggest you better Compact the Database before you take its file size. You may check it again after the background picture is added to a Form to find the difference.

  1. Open your Database and open a Form in Design View or create a new one.
  2. Display the Property Sheet of the Form (View - -> Properties).
  3. Find the Picture Property and click on it.
  4. Click on the build button (. . .) at the right side of the Property Sheet and click on it to browse to the location of the image you have saved and select the image.
  5. The next four Properties are used for formatting and displaying the Background Picture.
  • Picture Type
  • Picture Size Mode
  • Picture Alignment
  • Picture Tiling

The Picture Type Property has two choices Embedded or Linked to select from and the default value is embedded. The embedded option is provided for invoking the default Image Editor Program from within MS-Access by double-clicking on the image and to make changes to the image, if needed. To facilitate this; the necessary image editor program invocation information is also stored along with the image in the database, resulting a substantial increase of the database size for each image you add this way on your Form or Report. But, the background image inserted this way cannot be edited by double-clicking on the background as we do on the inserted image as an OLE object, on the Form.

A better choice is Linked option that will keep the image outside and the database size is not affected. In either case the image should go along with your database in the same location of the image wherever you take your database.

The Picture Size Mode have three options Clip (default) displays the picture in actual size wherever you position it by setting one of the Picture Alignment property Values (Top Left, Top Right, Bottom Right, Bottom Left, Center and Form Center). The Center option will position the image vertically and horizontally centered on the Form and the Form Center option will position the image vertically centered at the left side.

The Stretch Picture Size Mode option will stretch the image to fit the dimension of the Form and result in distortion of the image, if it is a picture of something.

The Zoom Picture Size Mode option will attempt to maintain the right proportions of the image when stretched to match the dimensions of the Form. But both the Stretch and Zoom options will show stretch marks when the Form is restored to its original size. This is very much evident when a .jpeg or .GIF image is used.

We can modify our earlier .bmp file to a one pixel width image and Tile it across the Form that will give a good quality gradient picture effect and the image size also will be small.

  1. Open the earlier saved .bmp image in MS-Paint Program.
  2. Select Attributes. . . from Image Menu.
  3. Change the Width Value to 1 and let the Height value remain without change.
  4. Check the Units selected is Pixels.
  5. Save the image with a different name.
  6. Change the name of the background image to the new image in the Picture property of the Form.

    Caution: Do not attempt to use the Picture Size Mode property options Stretch or Zoom; MS-Access will hang up.

  7. Set the Picture Tiling Property Value to Yes and save the Form.
  8. Open the Form in Normal View and enjoy your creation.
Share:

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation 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 DOS Commands ms-access functions msaccess functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security Array Custom Functions Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Variables Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter 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 Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users 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

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts