rs.recordcount is wrong

[rando1000]

I've got a form pulling data from a table in the same database. The code below shows how the variables are set up. The problem is, the intRecs variable is 1, no matter how many rows I've got in the recordset. What am I doing wrong?

Dim dbs As Database
Dim rs As Recordset
Dim intRecs as Integer
Dim intCounter as integer
 
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("Select * from TodaysSalesReps")
 
intRecs = rs.RecordCount
intCounter = 0
 
Do While intCounter < intRecs

[JKing]

I've got a form pulling data from a table in the same database. The code below shows how the variables are set up. The problem is, the intRecs variable is 1, no matter how many rows I've got in the recordset. What am I doing wrong?

Dim dbs As Database
Dim rs As Recordset
Dim intRecs as Integer
Dim intCounter as integer

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("Select * from TodaysSalesReps")

intRecs = rs.RecordCount
intCounter = 0

Do While intCounter < intRecs

Hi, you need to move to the last record in the recordset before it will return the proper value. Give this a try.

Dim dbs As Database
Dim rs As Recordset
Dim intRecs as Integer
Dim intCounter as integer
 
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("Select * from TodaysSalesReps")
 
rs.MoveLast
intRecs = rs.RecordCount
intCounter = 0
 
Do While intCounter < intRecs
 

[FishVal]

I've got a form pulling data from a table in the same database. The code below shows how the variables are set up. The problem is, the intRecs variable is 1, no matter how many rows I've got in the recordset. What am I doing wrong?

Dim dbs As Database
Dim rs As Recordset
Dim intRecs as Integer
Dim intCounter as integer
 
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("Select * from TodaysSalesReps")
 
intRecs = rs.RecordCount
intCounter = 0
 
Do While intCounter < intRecs

Hi, there.

If you want to iterate records to the end of the recordset, you may use Recordset.EOF property.

While Not rs.EOF
............
rs.MoveNext
Wend
 

[Ian Brown]

I have a database that forcasts dates for each record in Tasks table and this is the code I use:

  Dim dbs As Database, rst As Recordset
  Dim staTotal
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("Tasks")
  staTotal = rst.RecordCount
  For Reza = 1 To staTotal
    SetupVer                              ' -------------------------- This is the loop out
    DoCmd.GoToRecord , , acNext
  Next Reza
  rst.Close
  Set dbs = Nothing
 

So staTotal contains the number of records. I would use the following code (change as needed):

  Dim dbs As Database, rst As Recordset
  Dim staTotal
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("Tasks")
  staTotal = rst.RecordCount
  rst.Close
  Set dbs = Nothing
 

[rando1000]

Thanks. I went with the rs.Movelast option. This works perfectly:

Dim dbs As Database
Dim rs As Recordset
Dim intCounter As Integer
Dim intRecs As Integer
 
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("Select * from TodaysSalesReps")
 
rs.MoveLast
intRecs = rs.RecordCount
intCounter = 0
rs.MoveFirst
 
Do While intCounter < intRecs