Run an Update Query based on the most recent record (1 Viewer)

[LHolden]

I'm currently having some difficulty with an "undo" button I was asked to include in a database. The 'undo' function of the button would simply be updating a table with records from an archive table, thus whatever information had been archived/edited would be restored to the original table. The problem I'm having is that the Archive table can house many records for the same subject which is unique in the original table. The only ways I can think of getting around this is either writing an update query to update the Archive table to keep each record there unique as well, or updating the original table based on the most recent record added to the Archive table.

Any help is greatly appreciated. Thanks!

 

[G37Sam]

How do you choose which records go from Archive to Original table? Do you have a timestamp on your records?

 

[LHolden]

I do have a time stamp on the records, it's currently set to =Date()

With regards to which records get updated in the original table, they all do. This update query is meant as an undo to any editing which is done (the data is automatically archived before it is edited). However if Client A is edited multiple times, there are multiple records for it in the Archive table, and I'd like the MOST RECENT record for said client to be the one that updates the original table, and that is where I am stuck at the moment.

 

[G37Sam]

One way of doing it would be to use DAO to lookup the most recent record for ClientA in your archive. I made up names for your tables & fields for demonstration, change them to your own.

Dim rs1 as DAO.recordset
set rs1 = currentdb.openrecordset("Select * from ClientsArchive where client='" & "Client A" & "' ORDER BY timeStamp DESC")

The above code starts from the most recent record in Client A's archive. You can take it on from there

 

[LHolden]

Thanks a lot Sam!