Archiving records - Insert and Delete

[JMM]

I have 2 tables. All specified records should be inserted into one table and, at the same time, deleted from the other. The update query works fine, but I want a user to be able to select a date range from a form, say &lt;1/1/85, and have the sql running unseen in the background.<br><br>Any ideas? Thanks<br><br>JMM

 

[DougP]

I got this from Help and modified it to fit.<br>It does work I just tested it.<br>It Prompts for the user to key in a date then creates a new table.<br>It will not however add more records to it when it is already created.<br>---------------------------<br>Private Sub Command9_Click()<br>On Error GoTo Err_Command9_Click<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim dbs As Database<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim qdf As QueryDef<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Retval As Date<br>&nbsp;&nbsp;&nbsp;&nbsp;Set dbs = CurrentDb<br><br>&nbsp;&nbsp;&nbsp;&nbsp;' Select all records in the Contacts table<br>&nbsp;&nbsp;&nbsp;&nbsp;' and copy them into a new table, Emp Backup.<br>&nbsp;&nbsp;&nbsp;&nbsp;Retval = InputBox(&quot;Enter Date to Archive&quot;, &quot;Archive Records&quot;, Format(Now, &quot;mm/dd/yy&quot)<br>&nbsp;&nbsp;&nbsp;&nbsp;dbs.Execute &quot;SELECT Contacts.* INTO [Emp Backup] FROM Contacts Where Date &lt; #&quot; & Retval & &quot;#;&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;dbs.Close<br><br>Exit_Command9_Click:<br>&nbsp;&nbsp;&nbsp;&nbsp;Exit Sub<br><br>Err_Command9_Click:<br>&nbsp;&nbsp;&nbsp;&nbsp;MsgBox Err.Description<br>&nbsp;&nbsp;&nbsp;&nbsp;Resume Exit_Command9_Click<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Sub<br>-----------------<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[DougP]

PS it does not delete the records from the original table either. <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[gol4]

Simplest way is use a macro. Echo off, Set warnings no, run an update query to append then a delete query to delete. Just set the criteria of both queries to match the date field on your form. Or you could do the same thing using VBA