You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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.
What was the reason for using ADO over DAO, especially with the problems with ADO needing to be called using Late binding for compatibility currently?
I used ADODB since most of my work is with SQL Server and ADODB is my preferred method to retrieving data from SQL. The technique will work with DAO as well and I'll be posting a followup on my blog.
Thanks for the question!
1. I don't see anything that tries to control how many times the MouseMove routine runs. That could be REALLY expensive. How about setting the Control Tip at the Click event?
2. Has the Access community started moving away from these perpetually alive connections?
Sorry to hijack the thread, but I've asked on the thread about Access 2010 SP1 itself and over on the Facebook page with no reply so far, but have the problems with Access 2010 SP1 for both 32 bit and 64 bit been resolved yet? The last I heard there was a (not fully tested) hotfix available. Is that the final solution to the problems? Thanks.
Hasn't one of the fixes been to use Late binding for ADO? That way you are not tied to a specific DLL GUID. It does slow your code down, since it has to find the object, but it doesn't fail.
This was the problem I was referring to when I asked why you had coded with ADO instead of DAO.
Expensive? Just keep OrderID as a local or static variable, and test for change before querying.
Could you ever really put this in front of users on continuous forms (as given)? I know I couldn't enforce the " users must always click " provision.