how do I move a record from one table to another?

[montyb]

I am creating a database and in some instances I will need to move records from one table to another. It would be similar to a cut and paste of the record. What I want to do is have a button on a form and when necessary, push the button to have that particular record deleted in its original table and moved to another table (called "Destroyed&quot.

One last point, I probably won't need all the data from a particular record. What do I need to do to cherry pick the fields I want and have the others deleted?

Thanks in advance.. [sig][/sig]

 

[jimmythegeek]

On the on click of the button while you are on your current record:

==========
Dim db As Database
Dim rs As Recordset

Set db = Currentdb()
Set rs = db.OpenRecordset("YourSecondTable&quot ' Change name appropriately

With rs
.AddNew
' Here is where you would specify what fields you want to add to your second table.
You can populate whatever fields you want in your table, with fields from your form.
!TableFieldName = Me.FormFieldName For Example:
!LastName = Me.LastName
!BirthDate = Me.txtBirthDate
etc.
.Update
.Close
End With

db.Close

' Delete the current record
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
[sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]