RecordSet .RecordCount changing on it's own?! 2

[GOstlund]

I've no idea what is going on here, though I think I've had this problem before, only the "fix" I used then isn't working now because the problem seems to be compounding.

Private Sub DoStuff(RSP as RecordSet, ...)
	Dim I as Integer
[green]' At this point watch on RSP.RecordCount shows it to be 18[/green]
	RSP.MoveFirst
[green]' RSP.RecordCount is still 18[/green]
	I = RSP.RecordCount
[green]' 18 is stored in I[/green]
	While Not RSP.EOF And I < SomeMaxThing
[green]' Get a field or two from RSP[/green]
		Blah = RSP!FieldA
		Blah2 = RSP!FieldB
[green]' RSP.RecordCount is still 18[/green]
		RSP.MoveNext
[green]' Suddenly, for no apparent reason, RSP.RecordCount is now 1[/green]
	Wend
End Sub

If anyone has encounted this strange thing before and has a solution, I would really appreciate knowing about it as this is driving me crazy and that RecordCount plays a key role in setting up formatting and such for the form.

TIA,
Gavin

BTW, 'RecordSet' should be the DAO 3.6 version of the reference, although ADO is also Checked, it is below the DAO in priority.

 

[RoyVidar]

The recordcount of DAO recordset, will display the number of accessed records as long as the recordset isn't fully populated. To fully populate it, isse a RSP.movelast prior to the RSP.MoveFirst. But that it's giving 1 in the middle of looping, seems a bit strange.

To only loop as long as there are records, test the .eof property:

[tt]do while not RSP.eof
' code
loop[/tt]

My advice is not to rely on the order of the references, which will sooner or later bomb, but do explicit declaration:

[tt]dim rs as dao.recordset
dim db as dao.database
dim qd as dao.querydef
...[/tt]

Roy-Vidar

 

[payback]

The only true way to get the recordcount is to get to the end of the recordset i.e.

open the recordset
check that recordset.recordcount <> 0 (avoids future errors)
recordset.movelast
set your variable to the recordset.recordcount
recordset.movefirst

Do Until recordset.EOF
do actions on your recordset
recordset.movenext
Loop

close the recordset

 

[GOstlund]

Thanks so much guys! It makes sense to start by using MoveLast in order to ensure the length of the recordset.
Roy, thanks for letting me know I could specify what object library to get the object from, much appreciated!

Thanks,
Gavin