delete record and save it to another table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a macro for a command button on my form. I think a
macro is the best way to go. I want to be able to find any record in my form
and delete it. But at the same time, I want to save it to a "deleted records"
table. Please help me put this together.
 
Harry,

You can make an Append Query to write the selected record to the other
table, and a Delete Query to delete it from the existing table. Then,
to automate this using a macro, you can use two OpenQuery actions inthe
macro, to run the Append and the Delete.

Having said that, unless you are dealing with very large numbers of
records, this is probably more complicated than it needs to be.
Probably it would serve your purpose to forget the "deleted records"
table, and simply add another field to your existing table, Yes/No data
type, call it Archived or some such. So you can simply remove a record
from circulation via this field, and then base your forms and reports
accordingly based on a query that selects the records according to
[Archived]=No or whatever. Just a thought - that's how I usually do it.
 
Do I really need the delete query?

Steve Schapel said:
Harry,

You can make an Append Query to write the selected record to the other
table, and a Delete Query to delete it from the existing table. Then,
to automate this using a macro, you can use two OpenQuery actions inthe
macro, to run the Append and the Delete.

Having said that, unless you are dealing with very large numbers of
records, this is probably more complicated than it needs to be.
Probably it would serve your purpose to forget the "deleted records"
table, and simply add another field to your existing table, Yes/No data
type, call it Archived or some such. So you can simply remove a record
from circulation via this field, and then base your forms and reports
accordingly based on a query that selects the records according to
[Archived]=No or whatever. Just a thought - that's how I usually do it.

--
Steve Schapel, Microsoft Access MVP
I am trying to create a macro for a command button on my form. I think a
macro is the best way to go. I want to be able to find any record in my form
and delete it. But at the same time, I want to save it to a "deleted records"
table. Please help me put this together.
Click to expand...
Click to expand...
 
I have been trying to save the current record displayed in my form to another
table. I created an append query but it saves the whole table not just the
single record. What am I doing wrong?

Harry said:
Do I really need the delete query?

Steve Schapel said:
Harry,

You can make an Append Query to write the selected record to the other
table, and a Delete Query to delete it from the existing table. Then,
to automate this using a macro, you can use two OpenQuery actions inthe
macro, to run the Append and the Delete.

Having said that, unless you are dealing with very large numbers of
records, this is probably more complicated than it needs to be.
Probably it would serve your purpose to forget the "deleted records"
table, and simply add another field to your existing table, Yes/No data
type, call it Archived or some such. So you can simply remove a record
from circulation via this field, and then base your forms and reports
accordingly based on a query that selects the records according to
[Archived]=No or whatever. Just a thought - that's how I usually do it.

--
Steve Schapel, Microsoft Access MVP
I am trying to create a macro for a command button on my form. I think a
macro is the best way to go. I want to be able to find any record in my form
and delete it. But at the same time, I want to save it to a "deleted records"
table. Please help me put this together.
Click to expand...
Click to expand...
Click to expand...
 
Harry,

You will need to put a criteria in the query, to identify the form's
current record. Most likely you will use the table's Primary Key field
for this, and in the criteria in the query something like this...
[Forms]![NameOfYourForm]![YourIDField]
 
Back
Top