move last , move first

[merlin72]

Set rst = db.OpenRecordset(SQL)
rst.MoveLast
rst.MoveFirst

Is it manditory , for access to work correctly , to have movelast first. I take it that these commands tells access to look at all records.

 

[DiscoDuane]

It's been my understanding that you movelast the movefirst to get an accurate record count.

 

[alley]

It is my understanding that Access already knows the record count in a rst, in
recsetname.RecordCount.

alley

 

[DiscoDuane]

I tried both in Access 97
method 1
Set rst = db.OpenRecordset(SQL)
intA = rst.recordcount
msgbox intA

method 2
Set rst = db.OpenRecordset(SQL)
rst.MoveLast
rst.MoveFirst
intA = rst.recordcount
msgbox intA


and got 2 different numbers. Let me know what your results are

 

[alley]

Both methods returned the same number. My rst is small (0-8), but don't see that
as making a difference

alley

 

[DaveSH]

Merlin,

If by "work correctly you mean that you can get an accurate record count, then yes as Disco stated. In Access 97, the RecordCount property will be either 0 or 1 after first creating the recordset. 0 is empty and 1 is 1 record or more. If you MoveLast / MoveFirst access then reads through all records and sets RecordCount = the actual number of records in the recordset and returns to the first record.

My customers table has 5 customers in the state of PA ...

Ex 1

Dim R as Recordset, SQLTxt
SQLText = "Select * From Customers Where State = 'PA';"
Set R = CurrentDB.OpenRecordset(SQLTxt)
MsgBox "Records = " & R.RecordCount

Result --> Records = 1 (even though there are 5 records)

EX 2

Dim R as Recordset, SQLTxt
SQLText = "Select * From Customers Where State = 'PA';"
Set R = CurrentDB.OpenRecordset(SQLTxt)
R.MoveLast
R.MoveFirst
MsgBox "Records = " & R.RecordCount

Result --> Records = 5

Also MoveLast will generate an error message if the recordset is empty so its a good idea to test for an empty record set before attempting to moveLast.
...
If R.RecordCount Then
Set R = CurrentDB.OpenRecordset(SQLTxt)
R.MoveLast
R.MoveFirst
Else
MsgBox " There are no matching records"
End IF

Alley, what version of Access ? Im curious.

Dave

 

[merlin72]

Thank y'all very much . This has explained alot. I take it that you must do a moveLast then movefirst to get an accurate record count.

 

[cmmrfrds]

I stopped doing the MoveLast in Access 2000. In Access 97 I did the MoveLast/MoveFirst but with Access 2000 it works to directly reference the record count. These are either DAO or ADODB recordsets. Also, I directly reference the rst.BOF and rst.EOF properties and it works as expected.

 

[christheprogrammer]

I always do the following:
function counter()
dim dbs as database, qdf as querydef, rst as recordset, number_of_records&
set dbs = currentdb
set qdf = dbs.createquerydef("SOME_QUERY_NAME","SELECT * FROM [TableName];&quot
set rst = dbs.openrecordset("SOME_QUERY_NAME&quot
rst.movefirst
number_of_records = rst.fields(0)
docmd.deleteobject acquery, "SOME_QUERY_NAME"
end function

Cheers
Chris