MoveLast

[RichardH]

This is driving me mental!!
I'm trying to find the last record in a database so I can increment the next record by 1. I use the code rs.MoveLast but it only moves to the second last one and doesn't seem to see that there's another one after it.
What am I doing wrong? Please help for the sake of my sanity

 

[Mile-O]

Is the recordset you are opening, by any chance, a table? These are unordered and therefore the order of the records means nothing. If you want them ordered properly then you should use a query to sort the records and then use the query as the source of the recordset object.

 

[RichardH]

Bingo, cheers mate. I really need to learn what I'm doing

 

[modest]

A recordset is just a set of records (unordered unless defined by the user). If you open them from a table, Access will try to load the records into the Recordset object as soon as possible.

Because all you need it sorted for is to get the highest number, you don't necessarily need to sort them as you can use dmax() to find the max number of a field and just add one to it if needed.

All .Movelast does is point to the last record of the recordset, according to how the recordset was loaded. Therefore, if the recordset was not sorted it will still look at the last record, but the last record will not be the one you're looking for.

However, to sort the recordset, it would be best to sort it on load using a query as SJ suggested. This can be done using the Order By statement. An example is:
Set rst = CurrentDb.OpenRecordset("SELECT * FROM

ORDER BY [FIELD NAME]", dbOpenDynaset)

You probably didn't need this explanation, but I hope it helps. Good luck.

 

[RichardH]

Thanks Modest, any input is greatfully recieved. I still need to find out how to establich how many records are returned and store it in a variable.

 

[modest]

use the .RecordCount property or use dCount()

 

[DaveJHPT]

... and if you're using .RecordCount, make sure you do a .MoveLast first to make sure all the records are loaded.

Dave