In general, this is not a good idea in Access. One of the basic rules of
data normalization is not to store dependent data.
For an alternative approach, see:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210504
If you want to do it anyway, use the BeforeInsert event of the form to read
the last value, and assign it to the appropriate field. You could use DMax()
to read the higest value assigned so far; if "last" is defined other than
highest, then use ELookup() from this link to specify the sort order to get
the record you want:
http://allenbrowne.com/ser-42.html
A serious problem with doing it this way is that the user may enter records
out of order, or may need to enter another record that fits between others
logically. In these cases, your interface will produce wrong results, and
since you are storing them in a table, your database gradually becomes more
and more wrong. That's the reason why real data developers would never
consider doing it that way.
