Delete and Append Query at the same time

[Haz]

I don't know if this is the right place for my post. Since is in regard to queries...

I have data that I want to archive from one table to another table within a press of a button in a single form. The append work fine for me, but i tried adding the code to run the delete query to the same button as the append's one is, but giving me error that MS can't run the delete query because i have the form open exclusively. If I run the delete query with the form closed it work. How can I make the button to excute both append and delete queries at the same time.

Thanks in advance for the help

 

[KeithG]

Can you post your code?

 

[Moniker]

You can't delete the record that the form is currently using as its focus. That's what the error message is telling you. It sounds like you have an input form, you want to take whatever was input and store it in a separate table, and then clear out the form for another entry. The easiest way to do that is to perform your deletion after everything is appended and allowing the temporary holding table to grow and go to a new (blank) record. In other words, let the user enter as many records as they want, appending each one as necessary. Then, once the form closes (or as a macro when the DB closes or reopens), delete the contents of what is ultimately the temporary table.

 

[Haz]

Thanks Moniker for the tip.

Just wondering what is the different between the delete query i created and the the built in delete command using the wizard I use in the form to delete current record. Why would this allow deletion when form still open?

I will try to run the delete query once user exit the form, this is the best way i guess.

 

[Niniel]

I haven't had any problems deleting active records, so that comment doesn't make sense to me.
In fact, I *need* to click on the record on my [sub]form [continuous view] so that Access knows which one to delete.

As Keith said, it would be best if you posted your code.

 

[The_Doc_Man]

The "right" way to do this - not that it is uniquely right - is to add a field to mark records to be archived, if you don't have such a thing already.

Add something that updates the marker - maybe an update query or maybe a form with a checkbox or some other way of figuring out candidates for archiving. Now use your method to mark the records you want.

Then write two queries.

First, copy the marked records to the archiving destintion in an Append query.

Second, delete the marked records from the source table with a Delete query.

Next step, write a macro that runs the queries in the right order. This includes the record-marking (update) query if the marking process can be automated this way.

Final step, run the whole shootin' match in a Macro and key your form's button to run the macro.

 

[Haz]

Sorry I was away all last week on vacation.

Thanks guys for the reply, I already did half way what Doc Man says, I have appended query to updated marked records into history table. At the moment i filtered selected records and delete one by one.

I created a macro to open both queries in the order Append first and than Delete. If I run the macro with the form open is giving me the error "The table 'table_name' is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmatically". Is it possible to run the delete macro with table/form open? And where is the right place to put the macro code to run automatically?

 

[The_Doc_Man]

OK, if the form is bound to the table you are archiving, you can't run this macro from the form. Ever.

You could create an unbound form with that function on it.

Or you can look at the command line options that tell you how to run a named macro from the MS-DOS prompt line. (Command prompt.)

Search this form for "Scheduling a Macro" - or just look for scheduling and weed out those threads that are asking about a database for personal schedules. You want to make something happen on a regular basis. This is possible through macros and macros can be placed in the Windows Task Scheduler if you build the appropriate Icon.

The macro has to include a DoCmd.Quit or something like that if you are going to run it automatically. In which case it has to run in a way that does not open any of the critical tables. Otherwise you would get that "table locked" message again.