I was selected to be a guest blogger on the official Microsoft Access blog! You can read the article here:
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.
