moveLast not returning last record

  • Thread starter Thread starter Peter Graber
  • Start date Start date
P

Peter Graber

I am using the MoveLast method to get to the end of a
table. But unfortunately, it is not returning the last
record.

Is there a better way to find the last record in a
table?

Thanks.
 
Sandy -
I'm getting it through code. Its very basic:
Set db = CurrentDb
Set table = db.OpenRecordset("T_Visits")
table.MoveLast

Anything I'm doing wrong? thanks for all your help.

-PTG
 
Sandy Skaar said:
You can use the Max statement in the query.

Sandy S.
DBA
Click to expand...

First and Last are meaningless unless an explicit SortOrder is applied to
the form. If your table includes a field for Date/Time entered or an
incrementing number field that can be used to sort ascending on then you
can assume that GoToLast will do what you expect. There is nothing built
in to Access that can determine the "last record entered" if you do not
have one of these kinds of fields (at least not reliably).
 
Sandy -
I'm getting it through code. Its very basic:
Set db = CurrentDb
Set table = db.OpenRecordset("T_Visits")
table.MoveLast

Anything I'm doing wrong? thanks for all your help.
Click to expand...

Inasmuch as Access (Jet) tables are unordered, you can't rely on the last record
returned being the "last" record entered. If you have an incremental autonumber
or timestamp fields, you could sort your recordset on this field's value, and
*then* use MoveLast to retrieve the last record.

'********EXAMPLE START
Set db = CurrentDb
Set rs_table = db.OpenRecordset("SELECT * FROM T_Visits ORDER BY VisitID")
If rs_table.Recordcount > 0 Then
rs_table.MoveLast
End If
....
'Don't forget to clean up your object references
rs_table.Close
Set rs_table = Nothing
Set db = Nothing
'********EXAMPLE END

You would need to replace "VisitID" with the name of your incremental unique
record identifier field (e.g. autonumber or timestamp field). You could also set
the ORDER BY to "DESC" to make the "last" record the current record as soon as
the recordset is opened.
 
I am using the MoveLast method to get to the end of a
table. But unfortunately, it is not returning the last
record.

Is there a better way to find the last record in a
table?
Click to expand...

No, because a Table HAS NO ORDER. The "last record in the table" is
altogether arbitrary; Access will add new records in whatever order it
finds convenient. The most recently added record might be at the end
of the table, or it might go into a gap opened by a deletion.

If you care about the order of records, you must - no choice, no
option! - use a Query sorting the data in the table. MoveLast will
work correctly on such a Query.
 
Peter,

I would put an autonumber incremental on the table as the
first field of the recordset. You can sort by this field
and then use your movelast function. You could also sort
the records through a query and use the query dynaset to
get your info.

Hope this helps!

Sandy
 
Back
Top