Archive Record Macro

[Tom]

I need to create a macro/function that allows me
to "archive" records.

Basically, I need to...:
- copy a record from Table1
- paste this record into Table2
- deleted this record from Table1

Naturally, each records contains multiple fields.

Does anyone have an idea how to "move" an entire record
within Access across table?

Any ideas as to how I should solve this problem are
welcomed!!!

Thanks,
Tom

 

[Ashby]

The easiest way would be to create two queries. One that
appends the records in table1 to table2 and then create a
delete query to the delete the record from table1.

 

[Tom]

Ashby:

Thanks for the feedback. Initially, this sounded good,
but I now realize that this process "prompts" for input
too many times.

First, I am prompted to type in the parameter to (primary
key) to identify the record before appending it. Then
I'm prompted to click "ok" for appending it.

The same 2 prompts occur for deleting it. So,
essentially, I need to acknowledge the archiving process 4
times in order to move a record across tables.

Would you happen to know of any other way (e.g. functions)
that allows me to execute the "move" on once.

Thanks,
Tom

 

[Ally]

You could do this through a macro, either by opening the
queries in the macro, or if the SQL isn't too long,
copying the SQL of the queries and pasting them into
RunSQL commands. When you run this through a macro your
first row should be the SetWarnings command. If you have
this set to "No", the warning prompts will not appear.

Hope this helps,
Ally

 

[Stan Schaefer]

I need to create a macro/function that allows me
to "archive" records.

Basically, I need to...:
- copy a record from Table1
- paste this record into Table2
- deleted this record from Table1

Naturally, each records contains multiple fields.

Does anyone have an idea how to "move" an entire record
within Access across table?

Any ideas as to how I should solve this problem are
welcomed!!!

Thanks,
Tom

You could avoid shuffling records from table to table and just provide
an additional Boolean field named "Archive". True for Archive
condition, False for Active condition. Adjust your queries to show
only "Archived" or "Active" records, as desired. Makes it easy to
undo "oopsies".

Stan

 

[Steve Schapel]

Tom,

Ashby's advice is the best way to handle this. If you use a macro to
run the process, with OpenQuery actions to activate the Append and
Delete queries, just preced them with a SetWarnings, No action to
suppress the display of the action query confirmation messages. As
for the parameter prompt to identify the primary key of the record, it
is impossible to advise specifically without knowing more about what
you are really trying to achieve, but I guess the record has to be
identified somehow, either by the user at the time, or by a criteria
in the queries.

- Steve Schapel, Microsoft Access MVP