Dlast returns incorrect Autonumber record

[Philip]

Dlast returns wrong record on autonumber field

I been "docmd.runsql insert into values ..
for over 18 months, and looking for the last recor
with Dlast("Id","tblStockIn") to record on the paper doc

Suddendly Dlast returns Auto number ID record no 615
6150 is not the end of the table

To correct this I have done the following:
deleted 6150, Dlast returns 614
cut and pasted tabl
Checked indexing, oops! there is an additional index on another field
removed this and run a make table query to make a new tabl
Dlast still returns 6150

I am not aware of any changes to the data or the program at the time of
this change of behaviour

Some time prior to the above access warned of a problem and gave th
Repair and Compact option, I choose yes for fear of being locked out of
a damaged database

Any suggestions on how to fix this pleas
Cheer
Phil

 

[Andrew Smith]

This is what the Access help system says about the DLast function:

"Use the DLast function to return a *RANDOM* record from a particular field
in a table or query, when you need *ANY* value from that field."

In other words you cannot rely on it returning any particular record. In
fact a table in Access is not sorted at all, the records do not have any
particular order, and you cannot assume that the last record to be entered
will be the last record in the table.

If you're looking for the highest ID then you could use DMax rather than
DLast.

 

[Duane Hookom]

Don't ever rely on DLast() to return the Last record of an un-ordered
recordset. Records in tables are like marbles in a bag. You can't reach in
and expect to find the last marble added to the bag. If you want the highest
Autonumber, then use
DMax("ID","tblStockIn")
DLast() may work if the domain is a query that is ordered by ID ascending.

 

[John Vinson]

Dlast returns wrong record on autonumber field.

Not really.

DLast() returns the last record IN DISK STORAGE ORDER. This order is
whatever Access finds convenient - new records will often be stored
last in the table, but if there is space elsewhere on the disk, new
records may be inserted anywhere within the table. Tables have NO
controllable order.

If you want the value of the largest autonumber use DMax() rather than
DLast().

 

[Philip]

Thanks all for the help
As usual I was looking at the problem from the wrong end
cheers Phil