Best way to denote most recent record?

[Bill Mitchell]

Hi,

I am looking for the best way to denote programmatically
the most recent record the current user has added to the
database in a multi-user environment.

The reason I need this is that I want the user to be able
to run an append query copying some data from a current
record into a new record (such as adding a new staff
member), then requery the active form and do a findrecord
to go to the new record.

The best way, it seems to me, would be to have access
create the new record with the append query, somehow
record that new record's "Contact_ID" number in a Public
Variable, then do the FindRecord for the Contact_ID that
matched that variable.

I just don't know how to run the append query, have
access identify the new Contact_ID and set the Public
Variable (say "Contact_ID_#") to that new Contact_ID.

Any ideas?

Thanks.

 

[Scott McDaniel]

I'm not sure Access exposes a "most recently added" property (like SQL
Server's @@IDENTITY). You could use the DMax function to return the largest
value in the UniqueID field (if you set this to Autonumber and are not using
the Random option), then do a Find function using this (after your user has
finished whatever steps are necessary). Additionally, you could add a
TimeStamp field to your tables by:

1)Adding a field named dteTimeStamp, with a Datatype of DateTime
2)Setting the DefaultValue of that field to =Now()

Then, you could either filter a recordset, or sort Descending on the
dteTimeStamp column ...