Simple SQL code question

[Samantha]

Can anyone tell me why the following SQL code is only giving me 1 record?
There are supposed to be 2 records when I use MsAccess' QBE.

'assy is predefined
Dim db As Database
Set db = CurrentDb
Dim s As String
Dim rs As DAO.Recordset
s = " SELECT dbo_BOM.Component, dbo_PartMaster.DescText"
s = s & " FROM dbo_BOM RIGHT JOIN dbo_PartMaster ON dbo_BOM.Component =
dbo_PartMaster.PartNumber"

s = s & " WHERE ((dbo_BOM.Assembly)= '" & assy & "');"
Set rs = db.OpenRecordset(s, DB_OPEN_DYNASET)
MsgBox "rs.recordcount = " & rs.RecordCount

Why is rs.recordcount only giving me 1 record? Please help. thanks in
advance.

 

[Klatuu]

I don't know exactly why, but when you first open a record set, the record
count always seems to say 1 regardless of how may rows are actually in the
recordset. If it is 0 it will say 0. Make the following addition to your
code and see if it works:

Set rs = db.OpenRecordset(s, DB_OPEN_DYNASET)
If rs.Recordcount = 0 Then
MsgBox "No Records Found"
Else
rs.MoveLast
rs.MoveFirst
MsgBox "rs.recordcount = " & rs.RecordCount
Endif

 

[Graham R Seach]

Small correction.

You won't be able to accurately check rs.RecordCount straight after opening
the recordset, because most likely the dataset won't be fully populated.
That's why you used rs.MoveLast to get the recordcount later on.

What Samantha wants to do is check if there are *any* records, and if so,
get a recordcount. Different people have different ways of doing this, and
this is just one:
Set rs = db.OpenRecordset(s, dbOpenDynaset)
If rs.AbsolutePosition = -1 Then
MsgBox "No Records Found"
Else
rs.MoveLast
rs.MoveFirst
MsgBox "rs.recordcount = " & rs.RecordCount
End If

But if all you want is a count of the records, you can do it this way:
Set rs = db.OpenRecordset("SELECT Count(*) As NoOfRecords FROM Table1",
dbOpenSnapshot)
MsgBox rs!NoOfRecords

In most cases, dbOpenSnapshot is faster than dbOpenDynaset.

....or more simply...
MsgBox DCount("*", "Table1")

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Klatuu]

Graham,
The "fully populated" makes perfect sense; however, I have been using the
method I posted for several years without a problem and I always get an
accurate record count.

I am not disagreeing with you, but I wonder if I have just been lucky or if
the movelast has to wait until the recordset if populated before it can
execute?

 

[Douglas J. Steele]

The MoveLast causes the recordset to be fully populated.

 

[Klatuu]

Thanks, Douglas, that is what I thought.

The MoveLast causes the recordset to be fully populated.