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
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
130 End If
140 End With
150 Me.OrderStatus.ControlTipTextText = strSQL
160 Set rs = Nothing
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.
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.