Understanding Recordsets

[tilda]

Just started with Recordsets and Code so please be patient.
My problem is form navigation, I’ve created (mega deal for me) a set of navigation buttons on a form whose record source is a query. The navigation buttons work fine, however the recordcount is the problem. When I open the form its shows 1 of 1, when I move to the next record it then shows the correct amounts i.e. 2 of 250, if I then move back to the first record it’s got the correct amount i.e. 1 of 250.

Can anyone help me, not only to solve the problem which I’m sure will happen by accident one day but to understand what I’m doing wrong!

 

[willem]

The computer needs to go to the last record to know how many records there are.

form load event

recordset.movelast
recordset.recordcount
recordset.movefirst

 

[tilda]

Thanks, great it works, except the middle line recordset.recordcount but if I comment it out it works great. Do you think you could tell me why?

A happy tilda

 

[Rich]

Form Current
Me.YoutxtBox=Form.RecordsetClone.RecordCount + IIf(Form.NewRecord, 1, 0)
HTH

 

[accesswatch]

You ask why. The reason is as follows:
Once you know that the RECORDCOUNT property does NOT mean a count of the number of records in the recordset but in fact is a record of the number of rows accessed so far or The higest record number accessed within the recordset so far then it starts to become clear.

By using the MOVELAST you can think as having accessed ALL the rows in the recordset

A fairly standard way of getting the count is to assign it to a variable e.g

dim myCount as Integer
''
'Your Rset declarations etc,,

MyRst.MoveLast
MyCount = MyRst.Recordcount
Myrst.MoveFirst
Msgbox MyCount

(which is pretty much what willem laid out)

Hope this helps a little
Regards Trevor from http://www.sparkdb.co.uk




[This message has been edited by accesswatch (edited 08-02-2001).]

 

[tilda]

Thanks Everybody
Its starting to make sense at last, just got to try and put it all in to practice!
Can I hit you with another question, I’ve got the navigation almost beat, just one last problem, when I filter the records on the form and then click a command button which opens another form and finds specific data, if no data matches I get a Run time error 3021, if I use the Debug it stops on the Recordset.Movelast line.

Thanks in advance
tilda

 

[willem]

Use the following

set recordset= currentdb().openrecordset("name recordset")
do while not recordset.eof
recordset.movenext
loop
if recordset.recordcount <> 0 then
docmd.openform "frmname"
else
exit sub
end if


[This message has been edited by willem (edited 08-03-2001).]