VBA If/Then based on Record Count

Question

I'd like to create an If/Then statement in VBA where if there are multiple records in the recordset one message populates, and if there is only record another message populates.

Right now I'm using

If rst.EOF = False Then...

Else ...

End If

This is only returning the first condition regardless of how many records are in the recordset though.

Is there something else I'm missing?

Answer 1

You are missing to count the records:

If rst.EOF = False Then
    rst.MoveLast
    rst.MoveFirst
    If rst.RecordCount = 1 Then
        ' One record.
    Else
        ' More records.
    End If
Else
    ' No records.
End If

Answer 2

Depending on the size of your recordset, .MoveLast can severely impact on performance and since .RecordCount will not yield the total number of records, but rather the number of accessed records, a call to .MoveLast is required.

To offer an alternative, consider the following:

If rst.EOF Then
    ' No records
Else
    rst.MoveFirst
    rst.MoveNext
    If rst.EOF Then
        ' 1 record
    Else
        ' More than 1 record
    End If
End If

This first tests whether the recordset is already at .EOF, and if so, there are no records in the recordset.

If we have some records, it moves the cursor to the first record (.MoveFirst), and then to the next record (.MoveNext) if it exists.

If we have now reached .EOF, there must be only one record in the recordset; else, there are more records.

Since you only need to branch for the case there is a single record, this method means you are accessing the minimum number of records to determine this condition.