Display real-time information with the ControlTip Property

Today's Power Tip is provided by Juan Soto, Microsoft Access MVP and an expert on Access with SQL Server.

Using ControlTipText property to display real time information

The ControlTipText property is usually associated with providing static information, usually a helpful tip for the user - for example, a control called ContactStatus might have a ControlTipText text of "Select status from dropdown." The property can also be used in a dynamic way, allowing you to display real-time info to the user in a wide range of scenarios.

In the image below you can see a list of items ordered when you hover over the order number in the list.  Allowing the user to "peak" before opening the order will take the guess work out of what the order may contain and help them decide if they wish to click and see all details.

Figure 1 List of items on the order 11-222 is displayed once user clicks on the record's Order Status field.
Order 11-222 has two items in it. When the user clicks in the Order Status field and then hovers over the value, the item numbers for the corresponding order pop up in a ControlTip. Each order can have different items, so it's important to update the ControlTipText property of the Order Status field every time a different record is clicked.  We capture the event by using the "On Mouse Move" event of the order status control. The code used is displayed below:

'Place this code in a generic module and call it when your project starts

          Public con As ADODB.Connection

          Dim strConnection As String

Public Sub StartConnection()

10        strConnection = "ODBC,...."  'Use a valid connection string to SQL Server here...

20       Set con = New ADODB.Connection

30       con.ConnectionString = strConnection

End Sub

Private Sub OrderStatus_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)

          Dim strSQL As String

          Dim rs As ADODB.Recordset

10        If IsNull(Me.OrderID) Or Me.OrderID = "" Then

20            Exit Sub

30        End If

40        strSQL = "Select ItemNum from tblOrderItems Where OrderID = " & Me.OrderID

41        Set rs = New ADODB.Recordset

42       rs.CursorLocation = adUseClient

50        rs.Open strSQL, con, adOpenStatic, adLockReadOnly

60        With rs

70            If .RecordCount > 0 Then

80                strSQL = ""

90                Do While .EOF = False

100                   strSQL = strSQL & !ItemNum & vbCrLf

110                   .MoveNext

120               Loop

130           End If

140       End With

150       Me.OrderStatus.ControlTipTextText = strSQL

160       Set rs = Nothing

End Sub

Note: The above code assumes you are using an ODBC source such as SQL Server. If you are instead connecting with a local Access table, you just need to change rs.Open statement to use CurrentProject.Connection:

        rs.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly

Our technique uses an ADODB recordset to tap directly into a SQL Server. In order to use ADODB you will need to add a reference to your project to the ADODB provider installed on your PC.

Code Analysis

Public Sub StartConnection

This creates an ADODB connection object when the project is launched, allowing all of our code to use just one connection to SQL Server and keeping the connection open for all processes. You want to avoid having to open and close a connection when using this technique: users can and will click on many orders once they discover this feature, and so having an open connection at all times makes it more efficient to connect with the server.

Private Sub OrderStatus_MouseMove

Lines 10 through 30 make sure there is a valid OrderID; otherwise you end up making a trip to your server only to discover there is no order at all. Exit Sub is used to terminate execution prior to the End Sub.

Notice the use of CursorLocation on line 42. It allows us to later use the RecordCount property in line 70; otherwise your recordset may default to a server side cursor and the property will not be available.

In line 80 we reuse strSQL to store the list of items by resetting it to an empty string. We than proceed to loop through all the items on the order and build the property string, which is then used to set the ControlTipText of the OrderStatus field.

Note: In order to display the correct items for the right order, users must always click inside the OrderStatus field; otherwise the system may display the wrong items on the order.

Great for dashboards too!

Sometimes you don't want to cram too much information on the screen, or you don't want Access retrieving information that is rarely used. To that end I recommend using this technique on your dashboards, allowing users to quickly see more information as needed when they hover on fields.

Juan Soto is senior Access developer at AccessExperts.net and blogs at AccessExperts.net/blog. He's a frequent speaker at Access and SQL Server user groups nationwide and is available for speaking engagements through his company's website.