Checking data in previous record - using DLookkup

[Polarbilly]

In a form I want to read data from a previous record so I can compare it. At present, it only shows data from the current, not previous record
I'm obviously doing something wrong - but what. I'm using ACCESS 2007

Table: Delivery details
ID: ID
Field: ORDERNO
Form: DELIVERY DETAILS1

Formula entered in Control Source of Field "Previous Data"
=DLookUp("[ORDERNO]","DELIVERY DETAILS","[ID]=Forms![DELIVERY DETAILS1]![ID]-1")



Any, and all help appreciated

[CJ_London]

if this is the controlsource to a control on the form it would be

=DLookUp("[ORDERNO]","DELIVERY DETAILS","[ID]=" & [ID]-1)

however you cannot rely on the previous record having an ID of current record-1. To demonstrate try this little scenario

in a table lets say the largest ID is 100
start to enter a new record - the ID for the new record is immediately populated with 101 - all fine and dandy
but rather than finishing the new entry you hit the escape key (say by accident) and cancel the new entry
you 'realise your mistake' and start to re-enter the details - but this time the ID is 102 - 101 is now gone for all time.

Autonumbers should not be used for anything other that as a unique ID to a record and should not be assigned any other meaning.

Databases do not have the concept of first, last, next, previous without some sort of order, so to get your previous record you need to apply an order - it might be a timestamp, it might be the largest number in a column (i.e. effectively ordering by size). I don't recommend it but you could use ID field if you feel confident enough it will always increment (at that is not guaranteed) - in which case your lookup would be

=DLookUp("[ORDERNO]","DELIVERY DETAILS","[ID]=" & DMax("[ID]","DELIVERY DETAILS","[ID]<" & [ID]))

[Polarbilly]

Thank you very much for your prompt and extremely helpful reply - back to the drawing board