The database I have tracks work orders that have been complete. Once we
verify with the customer the issue is resolved, using a form we change the
value of the field from “No†to “Yesâ€. A simple append query is in place to
move those records from the active DB to an archive of sorts.
Is there a way to append the data to the archive database and delete it from
the main database all in one shot?
But why move and delete? Having those records in a different table
does nothing for you except to make searching for data more complex.
Simply add a check box field named [Resolved] to the original table.
A check means yes, it's resolved, no check means it's still not
resolved.
You can show only the not resolved records in a form by making that
form's record source a query, using the table as the query record
source, and setting the criteria on the [Resolved] column to No.
OR ...
still using the table as the Form's record source, you can set the
form's Filter property to
[Resolved] = No
Then set the Form's filter on or off by right clicking on the form and
selecting Remove Filter/Sort or Apply Filter/Sort as needed to show or
not show all the records.
As all records are always available in the one original table,
searching for records, new and old, is simpler.
If you still wish to move and delete records in one operation you can,
using code, but you still need 2 queries.
Create an Append query, using
[Resolved] = -1
as criteria.
Create a Delete query, using
[Resolved] = -1
as criteria.
Then code the click event of a command button:
CurrentDb.Execute "AppendQueryName", dbFailOnError
CurrentDb.Execute "DeleteQueryName", dbFailOnError
But don't do it! Leaving all the records in the one table is the
better option.