Help Please re Count the Records in a Table

[Guest]

Hi I have a simple issue that I can't seem to solve; How can I tell through
VBA code if a table has NO records in it. I need this as validation for
counting records in a table.

Typically to count number of records in a table I using VBA I use the
following:

wtih rst
..movefirst
..movelast
intValue = .RecordCount
end with

But if the table has no records in it(which is somtimes the case) you will
get a "No current record,,,,,, 3021 Error" as soon as the .movefirst occurs.

If anyone could please let me know how to pre test the table in VBA so that
I can tell if the table is empty before doing the count it would be great.

Thanks in advance.

James D.

 

[SusanV]

try rst.recordcount

 

[George Nicholson]

Test for end-of-file before you try any Moves:

with rst
If >EOF Then
' No Records
intValue = 0
Else
.movefirst
.movelast
intValue = .RecordCount
End If
end with

HTH,

 

[George Nicholson]

Recordcount will *not* return a reliable value unless you have moved to the
last record of the recordset, but if you try to move to the last record in a
recordset with no records you will get an error, as the OP has discovered.

 

[Guest]

sorry, did not work. From what I have seen you have to use the .moveLast and
..moveFirst commands to get an accurate count, otherwise you will just get a
value of 1 if the table has data and and error if the table has nothing.

 

[SusanV]

Sorry, my bad

Recordcount will *not* return a reliable value unless you have moved to
the last record of the recordset, but if you try to move to the last
record in a recordset with no records you will get an error, as the OP has
discovered.

 

[SusanV]

Not sure why, I use the following and it works fine:

Private Sub Form_Load()

Dim rst As New ADODB.Recordset
Dim strsql As String
Dim cnx As New ADODB.Connection

Set cnx = CurrentProject.Connection
Me.subfrmTMs.Form!chkVessel.ControlSource = strVessel
strsql = "Select * from tblEQ WHERE ShipName = '" & strVessel & "'"
rst.Open strsql, cnx, adOpenStatic
If rst.RecordCount = 0 Then
MsgBox "Equipment must be imported for " & strVessel & " before
proceeding!", vbExclamation
DoCmd.Close acForm, "frmEQ_To_TM", acSaveYes
End If

Set rst = Nothing
cnx.Close
End Sub

Why would that be?

 

[Douglas J. Steele]

I believe you meant:

with rst
If .EOF Then
' No Records
intValue = 0
Else
.movefirst
.movelast
intValue = .RecordCount
End If
end with

which is probably better stated as

with rst
If .EOF = True And .BOF = True Then
' No Records
intValue = 0
Else
.movefirst
.movelast
intValue = .RecordCount
End If
end with

Regarding a comment you made in another thread, though, .RecordCount should
(correctly) return 0 when an empty recordset is opened.

 

[John W. Vinson]

If anyone could please let me know how to pre test the table in VBA so that
I can tell if the table is empty before doing the count it would be great.

If DCount("*", "[tablename]") = 0 Then
<there are no records in the table>
End If

John W. Vinson [MVP]

 

[George Nicholson]

Yep, that's what i meant. sticky shift key strikes again.

 

[George Nicholson]

ahhh, that's because the ADO and DAO recordcount properties act differently.
DAO needs a MoveLast to insure accuracy.

That said, there also wasn't anything inherent in the OP to indicate whether
ADO or DAO was being used, so your initial response was perfectly valid. ADO
was off my radar when i "corrected" you. my bad.

 

[Douglas J. Steele]

An empty DAO recordset will report RecordCount = 0.

AFAIK, you need a MoveLast with both ADO and DAO to ensure a proper count
for larger recordsets.

 

[SusanV]

No problem George - it didn't occur to me he was using DAO LOL. Seems both
of us were right, even with our radar off!

;-D

 

[John W. Vinson]

Recordcount will *not* return a reliable value

However, if the only purpose is to make a binary decision - empty recordset or
non-empty recordset - it's not necessary to return a "reliable value". It will
be 0 if the recordset is empty, and nonzero (maybe 1, maybe the actual
recordcount, maybe something in between) if not. Just test for 0 and don't
worry about a movelast!

John W. Vinson [MVP]

 

[David W. Fenton]

with rst
If .EOF = True And .BOF = True Then
' No Records
intValue = 0
Else
.movefirst
.movelast
intValue = .RecordCount
End If
end with

I would do:

If rst.RecordCount <> 0 Then
rst.MoveLast
End If
lngValue = rst.RecordCount

I changed the name of that last variable, since it would be foolish
to use an Integer value for this, as it would limit you to fairly
small tables.

 

[David W. Fenton]

However, if the only purpose is to make a binary decision - empty
recordset or non-empty recordset - it's not necessary to return a
"reliable value". It will be 0 if the recordset is empty, and
nonzero (maybe 1, maybe the actual recordcount, maybe something in
between) if not. Just test for 0 and don't worry about a movelast!

Unless you need the recordcount.

 

[John W. Vinson]

Unless you need the recordcount.

Maybe I was misinterpreting. James' original question said

How can I tell through
VBA code if a table has NO records in it.


John W. Vinson [MVP]

 

[David W. Fenton]

Maybe I was misinterpreting. James' original question said

How can I tell through
VBA code if a table has NO records in it.

Yes, I know. I was just adding something that otherwise out of the
original context might confuse someone.

 

[Guest]

Thats perfect, thank you(and sorry for not specifing ADO or DAO.)