VBA: test for empty recordset; use of assistant baloon object

[Clif McIrvin]

Hello again <g>.

This time I'm looking for comments / suggestions as to whether I'm on
the right track or not. The code sample below is doing what I expect it
to, I'm just wondering about my coding technique.

Actually, I have at least three questions:

1 and 2. The code was working fine until I stumbled into an attempt to
exit the Remarks control with an empty recordset which threw an object
not defined error on the 'If Me.Box.Value ' line. Is my solution of
testing 'me.recordset.bof' valid from both coding style and Access / Jet
usage?

3. My use of the assistant balloon object appears to be working as I
expect; but as I was unable to find any documentation of this particular
technique I'm wondering if I'm setting myself up for future trouble.

Comments, anyone?

The form in question is actually a sub-form but I also sometimes use it
as a stand-alone form. The controls are bound; Remarks is Text and Box
is Boolean. My environment is XP Pro SP2, Access 2003 SP3 w/Hotfix using
DAO 3.6.

Begin Code Sample=============

Option Compare Database

Public myBalloon As Balloon

Private Sub Form_Load()
With Assistant
.On = True
.Visible = False
End With

Set myBalloon = Assistant.NewBalloon
myBalloon.Close
End Sub

Private Sub Form_Unload(Cancel As Integer)
Set myBalloon = Nothing
With Assistant
.Visible = False
.On = False
End With
End Sub

Private Sub Remarks_Enter()
If Me.Recordset.BOF Then 'skip all if empty recordset
ElseIf Me.Box.Value = True Then
With myBalloon
.Text = Me!Remarks.StatusBarText
.Mode = msoModeModeless
.Button = msoButtonSetNone
.Show
End With
End If
End Sub

Private Sub Remarks_Exit(Cancel As Integer)
If Me.Recordset.BOF Then 'skip all if empty recordset
ElseIf Me.Box.Value = True Then
myBalloon.Close
End If
End Sub

End Code Sample===============

 

[Arvin Meyer [MVP]]

Try this:

With Me.RecordsetClone
If Not (.EOF And .BOF) Then
With myBalloon
' yada yada
End With
End If
End With

 

[Clif McIrvin]

Try this:

With Me.RecordsetClone
If Not (.EOF And .BOF) Then
With myBalloon
' yada yada
End With
End If
End With

Thanks, Arvin.

I see the improvement to the self-documentation here as well as the more
particular test.