Finding the last Record in a Recordset

  • Thread starter Thread starter Bill Linares
  • Start date Start date
B

Bill Linares

I have had great difficulty updating a table because of a weird behavior
with the recordset.MoveLast Method:

It does not return the last record! It gets stuck in the middle of the
table.

For instance, I declare a recordset

Set CDsRS = db.OpenRecordset("TableNames") 'db is the actual database

Now if I take a look at the RecordCount property, it has the correct value
of entries. If I then try to go to the last record by

CDsRS.MoveLast

I end up somewhere in the middle. However, the AddNew method works; it does
not overwrite the entry returned by MoveLast

???
 
Never assume anything about the order of the rows in a table. Relational
database tables are "sacks of data": rows are put in wherever they'll fit.
The only way to guarantee the order in which the rows are returned is to use
a query with an ORDER BY clause in it.
 
Bill said:
I have had great difficulty updating a table because of a weird behavior
with the recordset.MoveLast Method:

It does not return the last record! It gets stuck in the middle of the
table.

For instance, I declare a recordset

Set CDsRS = db.OpenRecordset("TableNames") 'db is the actual database

Now if I take a look at the RecordCount property, it has the correct
value
of entries. If I then try to go to the last record by

CDsRS.MoveLast

I end up somewhere in the middle.
Click to expand...

No, you are on the last record.
However, the AddNew method works; it
does
not overwrite the entry returned by MoveLast
Click to expand...

Don't understand what you mean by this.How do you know where you are?
There is no positional attribute to a record.
It is where it is.

You can tell where it is using rs.absoluteposition

if you are expecting a particular sequence to your recordset you have to
sort it in a query based on your table and use that as the source.


Regards

Peter Russell
 
The problem is very simple:

I have a table and want to change a field in the last record I added. Now, I
cannot enter the value for the field I want to change when I add the record
because the value is not set jet.

Any ways, thanks for any help
 
Bill said:
The problem is very simple:

I have a table and want to change a field in the last record I added. Now, I
cannot enter the value for the field I want to change when I add the record
because the value is not set jet.
Click to expand...

You have to have a way to identify "the last record I
added", typically by having a date/time field with the date
and time that each record is added. You can then use a
query that sorts the records in the order they were added
anf MoveLast will do what you asked.
 
You have to have a way to identify "the last record I
added", typically by having a date/time field with the date
and time that each record is added.
Click to expand...


Can I just add that you don't then need a query that returns all the
records if you only want one of them? Your network manager, and your other
users will all love you if you use a 'SELECT TOP 1' or 'WHERE PKValue = '
query instead of the whole table.


B Wishes


Tim F
 
Back
Top