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:



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.

---

Drill-Down Inquiry Screen-2
Drill-Down Inquiry Screen
Command Button Animation-2
Cardinal Text Format in Access
Custom Report Wizard