Append and Delete in one step

[OverMyHead]

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?

 

[KARL DEWEY]

Yes, by using a macro to run the append and then the delete queries.

But that is the wrong way to do it. You need to just rely on that field
value you change from “No†to “Yesâ€. Just add criteria in all of your
queries that pull current/open data. That way if you need to pull data for
past year it will all be in the one table.

 

[fredg]

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.

 

[OverMyHead]

Thank you Karl and Fred. I had not even considered basing the information on
a query to show the information I needed. This will make it significantly
easier to run reports and retain data.

Again, thank you for your help!

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.