Forum Discussion
Microsoft access VBA Code
I want to move a record from one table to an archive table after I input a date on a form through an Event Procedure, what would be the proper code I should use?
plowell43 Well, there are two responses.
The specific method most commonly used to copy (not necessarily move) a record is an Append query. You could also use a recordset in a VBA Sub, if that's more your style. Most developers, though, would probably go the path of least effort and write the query.
The other response is more procedural and related to "best practices". Archiving records tends not to be a frequently done routine. The whole purpose of having a relational database application is to preserve historical data and make it easy to access and report on that data. Archiving defeats that purpose in many cases. Sometimes it is done once in a while, maybe annually, for example, when older data is no longer useful for reporting. I can't see any indication one way or the other in the question, but it does imply you want to archive records every time you modify one date field. And that seems like overkill.
That said, you know the business rules that apply, so you decide how often you want to remove data from your working environment.
Write an append query to select the designated record and append its values to your archive table, followed by a delete query to select the designated record in the original table and delete that one record.
