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 SubThe 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 SelectThe 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.
AnimatingLabel.zip
[...] with the co-ordinate values based on their placement) and make them visible with flashing text (Animating Label on Search success) based on the User's selection. Sample label addressing method: Wave-shaped reminder ticker [...]
ReplyDelete