I was selected to be a guest blogger on the official Microsoft Access blog! You can read the article here:

http://blogs.office.com/b/microsoft-access/archive/2011/11/28/display-real-time-information-with-the-controltip-property.aspx

Please read and come back here for some final thoughts on the technique.

Another alternative: DAO

When I wrote the guest post I automatically selected ADODB since that’s what I use almost always in my code. Here’s the same concept using DAO:
Private Sub OrderStatus_MouseMove( _
Button As Integer, _
Shift As Integer, _
X As Single, _
Y As Single _
)          
Dim strSQL As String
          Dim rst As DAO.Recordset
Dim db As DAO.Database
10        If IsNull(Me.OrderID) Or Me.OrderID = "" Then
20            Exit Sub
30        End If
32        strSQL = "Select ItemNum from tblOrderItems Where OrderID = " & Me.OrderID
33 Set db = CurrentDB
34      Set rs = db.OpenRecordset(strSQL)
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.ControlTipText = strSQL
160       Set rs = Nothing
End Sub
 

Persistent Recordset

If you find yourself constantly opening and closing the recordset, you can choose to create a module level variable, move out the line 30-32 into form’s load or current event and not closing the recordset by commenting out line 160 above.  If you do go down this route then you would need to retrieve all related records in one trip. You also may want to open the recordset as a snapshot-type recordset to ensure the request is pulled only once. If it’s a lot of records in memory it may not be worth it.

After all these years I’m continually amazed at the capabilities of Access and how we can stretch the envelope of what can be done.