testing for an empty Record Source when a form opens

[Paul James]

When a certain form opens, I'd like to run a line of VBA code to test to see
if the form's Record Source has any records in it, so I could take further
action in VBA.

Can anyone tell me how to test for zero records in the form's recordset?

Thanks in advance,

Paul

 

[Wayne Morgan]

A couple of options, which you use will depend on whether or not there is a
timing problem with the record source being available in the Open event. It
is available in the Load event, but this may or may not be too late for what
you're wanting to do.

If Me.Recordset.RecordCount = 0 Then

or

If DCount("*", "RecordSourceName") = 0 Then

 

[Lynn Trapp]

One way would be to do something like this in the Current event of your
form.

Dim rst As DAO.Recordset
Dim lngCount As Long
On Error GoTo CheckRecordsetError
Set rst = Me.RecordsetClone

With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With


CheckRecordsetExit:
Exit Sub

CheckRecordsetError:
MsgBox "No Records"
Resume CheckRecordsetExit

 

[Dirk Goldgar]

When a certain form opens, I'd like to run a line of VBA code to test
to see if the form's Record Source has any records in it, so I could
take further action in VBA.

Can anyone tell me how to test for zero records in the form's
recordset?

Thanks in advance,

Paul

In Access 2000 or later you can use code like this example, which
displays a message and cancels the form if there are no records:

'----- start of example code -----
Private Sub Form_Open(Cancel As Integer)

If Me.Recordset.RecordCount = 0 Then
MsgBox "No records to display!"
Cancel = True
End If

End Sub
'----- end of example code -----

In Access 97, use this If statement instead:

If Me.RecordsetClone.RecordCount = 0 Then

 

[Paul James]

That's what I needed, and then some.

Thanks, everyone.