RecordCount

[dgkindy]

When the program comes accross the recordset with more then one record it does not report the correct number of records. I took the SQL statement and put it in a query and it works. I read on the forum that I should put rs.movelast before recordcount but this causes an error so did not want to do it.

Do I need to put a rs.movelast before rs.recordcount?

Does RecordCount start at 1 or 0 like an array?

SQLLit = "SELECT tblLinkLibrary.Part_Number, tblLiterature.Literature FROM tblLinkLibrary INNER JOIN tblLiterature ON tblLinkLibrary.LinkLibraryID = tblLiterature.LinkLibraryID WHERE (((tblLinkLibrary.Part_Number)='" & rsParts!Part_Number & "'));"
Set rslit = db.OpenRecordset(SQLLit)
*Pauses program where I know there are two records*******
If rsParts!Part_Number = "3024164" Then
Temp = 1
End If
***********************************************
For X = 1 To rslit.RecordCount
rslit.MoveFirst
With rsVD
.AddNew
!Literature = rslit!Literature
!PartID = rsParts!PartID
.Update
End With
rslit.MoveNext
Next X
End If

 

[boblarson]

Yes, you would need to use a movelast to get the count. What error are you getting? You will get an errror on MoveLast if you have no records, so you should first check for

If rslit.BOF And rslit.EOF Then
   ' recordcount is 0 if it meets that condition   
Else
  ' whatever if their is 1 record or more
End If

 

[Moniker]

A few things to know about record counts.

1) They start with 1. Records are just records, not objects, per se. You cannot have recordcount = 0 to mean 1 record. Arrays are different (as are indexes, etc.)

2) Open the recordset with adOpenKeyset or adOpenStatic. If you open with adOpenDynamic, you will not get a proper recordcount result.

3) If all you want to know is how many records there are, use adReadOnly as your lock.

It looks like this:

Dim rsCount As ADODB.Recordset
Dim RecCount As Long

Set rsCount = New ADODB.Recordset

rsCount.Open "SELECT * FROM YourTableNameHere", CurrentProject.Connection, adOpenKeyset, adReadOnly
If Not rsCount.EOF Then
    RecCount = rsCount.RecordCount
Else
    RecCount = 0
End If

Set rsCount = Nothing

There are plenty of different ways to do that same thing, but that should push you in the right direction.