April 2009 ~ LEARN MS-ACCESS TIPS AND TRICKS

Filter by Character and Sort

a.p.r. pillaiApril 18, 2009Forms, msaccess controls, Utilities 4 comments

Introduction

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

Now, we will try a different and easier method (for the User) for filtering data by keying in one or more characters into a TextBox and getting an 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 TextBox (say the letter F) all Records with names starting with that letter will be filtered instantly. If the filtered list is large, with unwanted items, then the next character that matches can be entered to further narrow down the list and this method can be repeated.

If the Backspace key is pressed to delete the last character or more characters entered in the TextBox; 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 the 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 the sample code for this action at the end of this Article.

Sample Image of Form

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 use on Forms with a single record view.

Normally, the search or filter operation of this type will be implemented in 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.

Sample Data Table, Query & Form.

Import the Customers Table from the Northwind database.
Create a SELECT Query with the following SQL String and save the Query with the name CustomersQ.
```
SELECT Customers.[First Name], Customers.[Last Name], Customers.[Job Title]
FROM Customers;
```
Design a Tabular Form as shown in the Image given above. I have selected only three fields from the Table in this example. If you would like to use some other Fields in the Query you may do so.
Expand the Form Footer Section, if it is not visible (View - - >Form Header/Footer).
If the Tool Box is not visible display it by selecting Toolbox from View Menu.
De-select the Control Wizards Button (the top right one); if it is already in the selected state on the ToolBox, so that the Wizard will not start when we select the Combo Box Tool.
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.
Change the Caption value of the Child Label on the Combo Box to Filter/Sort Field:.
Click on the Combo Box to select it and display its Property Sheet (View- ->Properties).
Change the following Property Values as given below:
- Name = cboFields
- Row Source Type = Field List.
- Row Source = CustomersQ.
- Column Width = 1.5"
Create a Text Box below the Combo Box.
Change the Caption Value of the Child Label to FilterText:.
Change the Name Property Value of the Text Box to FilterText.
Create an Option Group Control, with two buttons with Label Captions ASC and DESC for Sorting option Ascending or Descending order.
Create a Command Button to the right of the Options Group control and change the following Property values:
- Name = cmdClose
- Caption = Close
The VBA Code
Display the VBA Code Module of the Form (View - ->Code or Alt+F11).

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, j As Integer, srt As String

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) 'delete the last character
        End If
        GoSub setfilter
    Case 37, 39 'left 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, "") 'save the value in Combo Box
  If Len(Nz(x, "")) = 0 Then
        Me.FilterOn = False ' remove filter
  Else 'set filter and enable
        Me.Filter = "[" & Me![cboFields] & "]" & " like '" & x & "*'"
        Me.FilterOn = True
  End If
  ' Set sort order
  j = Me!Frame10
  srt = IIf(j = 1, "ASC", "DESC")
  Me.OrderBy = "[" & Me!cboFields & "] " & srt
  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 = False
End Sub

Private Sub Form_Load()
    Application.SetOption "Behavior Entering Field", 2
    Set rst = Me.RecordsetClone
    Me!cboFields = rst.Fields(0).Name
    Me.Refresh
    rst.Close
End Sub

Test Run the Demo Form

Open the Form in Normal View.
The First Name field will appear as the default value in the Combo Box control.
Click on the Text Box below the Combo Box to set the focus on it.
Type the Character F and you will see that all the records with CustomerID values starting with the letter F are filtered.
If you look at the filtered Field values of records you may find that the second character of the field values are different and three items have the same letter in the second character position. Besides that, the field values are correctly sorted, in selected (Ascending/Descending) Alphabetical Order.
Type the second common character next to the earlier character in the FilterText control and the list of items narrows down. You can further filter and narrow down the list progressively this way if needed.
Press Backspace Key to delete the last character typed and to leave the rest of the characters in the Text Box. The list will expand and all items starting with the letters in the control are back on the List.
Press Backspace Key again to delete other characters one by one from the TextBox. 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, A to Z, 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.

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

Download Demo Database

Download Filter_ByCharSort2007.zip

Animating Label on Search Success

a.p.r. pillaiApril 11, 2009Animations, Forms, msaccess animation 1 comment

Introduction

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

Visual Indicator Design Task

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

To try an example with the sample VBA Code given below; import the Customers Table and Customers Form from the 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).
Open the Customers form in the design view.
Expand the Form Footer Section to create a 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:
Draw a Label on the Form Footer Section and change its Caption as Customer ID to Find:.
Draw a Text Box to the right of the Label and change its Name Property (View - - > Properties or Alt+Enter) Value to xFind.
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.
Create a Label below the Text Box and change the following Property Values shown against each Property:
- Name = lblMsg
- Caption = x
- Visible = False
Display the Code Module of the Form (View - - > Code or Alt+F11)

Animation Running VBA Code

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.

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.
Type record number 55 in the Record Navigation control below.
Highlight the CustomerID code and press Ctrl+C to copy the Customer Code into Clipboard.
Type 1 in the record navigation control to make the first record current.
Click on the Text Search Control on the Form Footer to select it and press Ctrl+V to Paste the Customer Code from Clipboard.
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 TextBox will be visible and will flash nine times with the text message ** Successful ** and stays on the screen.
Make the first record as current again and make some changes in the CustomerID in the search TextBox so that the search operation will fail with the modified Value.
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.

How does it Work?

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

A Better Approach.

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 has a particular Color.

Create a copy of the Customers Form with the name Customers2.
Open the Form in Design View and display the Form's Property Sheet.
Find the Picture Property and delete the WMF image file pathname. This action will display a message asking to reconfirm the delete action and respond to remove the entry.
Remove the After Update property value also. There is a Macro attached here to run in the after-update event of the form.
Without closing the Property Sheet Click on the Footer of the Form and change the Back Color Property value to -2147483633. This is the normal Form background color when you open a new form in Design View.
Click on the label below the Text Box and change the following Property Values:
- Back Style = Transparent
- Special Effect = Flat
- Border Style = Transparent
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 in 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 an 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.
Save the Form and open it in a normal view.
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.

Download Demo Database.

AnimatingLabel.zip

Form Background with Gradient Color

a.p.r. pillaiApril 04, 2009Art Work, Forms, msaccess forms 13 comments

Introduction

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 the same locations on the Form.

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

Creating a Sample Image

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

Opened a new Microsoft Word Document and went through the following steps to create a Gradient Bit Map Image:
Selected Format - - >Background - - > Fill Effects.
Under Colors, options selected One Color.
Clicked on the Color 1 drop-down control and selected a light color.
Used the Darker/Lighter slider control to adjust the color tone.
Selected the Horizontal Radio Button under Shading Styles.
Clicked on one of the Style Variants.
Clicked OK to apply the selected shade as the Background of the Word Document.
Maximized the Word Document Window.
Push the Prt Scrn key (at the top row right side). The full-screen image is now captured and copied to Clipboard.
Opened MS-Paint Program. You can find this in Start - - > Programs - - > Accessories.
Displayed the Toolbox (View - ->Toolbox or press Ctrl+T).
Selected the Select Tool (the one at the top right with a rectangle picture).
Drawn a rectangle from the left top corner of the gradient image area to the 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.
Use Ctrl+C to copy the selected area into Clipboard.
Clicked outside the selected area to deselect the earlier selection.
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 the position at the left end.
Pressed Ctrl+V to paste the copied image from the Clipboard.
Clicked outside the pasted image to de-select it.
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.
Similarly adjusted the right side of the image.
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 a background picture. But, there are some limitations to using images like this one as a background picture, but we can overcome this with a little trick. If you are thinking about the increase in the 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 software like Adobe Photoshop then you can make better quality .GIF images.

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.

Inserting the Image on the Form Background

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.

Open your Database and open a Form in Design View or create a new one.
Display the Property Sheet of the Form (View - -> Properties).
Find the Picture Property and click on it.
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.
The next four Properties are used for formatting and displaying the Background Picture.

Picture Type
Picture Size Mode
Picture Alignment
Picture Tiling

Linked or Embedded Image Methods

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 making 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 in 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 the Linked option which 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 as the image wherever you take your database.

The Picture Size Mode has 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 on the left side.

The Stretch/Zoom/Tile Picture Methods

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.

The One Pixel Width Image

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.

Open the earlier saved .bmp image in MS Paint Program.
Select Attributes. . . from Image Menu.
Change the Width value to 1 and let the Height value remain without change.
Check the Units selected is Pixels.
Save the image with a different name.
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.
Set the Picture Tiling Property Value to Yes and save the Form.
Open the Form in Normal View and enjoy your creation.