Code that will copy a record to another table

[Guest]

I am trying to create a macro or vba code that will allow me to select a
record in a form bound to a particular table and copy it to another table
for temporary use. (Once the record is copied to the table, I want to be
able to manipulate the data in the record without affecting the original
record in the first table)

Can someone help me?

 

[Jeff Boyce]

If by "manipulate" you mean perform some calculations on, consider using a
query and adding fields in which you perform those calculations.

 

[Douglas J. Steele]

You should be able to use an INSERT INTO query.

INSERT INTO Table1 (Field1, Field2, ...)
SELECT Field1, Field2, ... FROM Table2
WHERE Id = 123

 

[Wayne Morgan]

You could use an Append Query to copy the data. Use the Click event of a
button to activate the code.

Example:
Dim strSQL As String
strSQL = "INSERT INTO tbl1 (Field1, Field2, Field3, Field4)" & _
" SELECT Table1.Field1, Table1.Field2, Table1.Field3, Table1.Field4" & _
" FROM Table1 WHERE Table1.Field1=" & Me.txtRecordID & ";"
CurrentDb.Execute strSQL, dbFailOnError

This assumes the ID is a number, add quotes as needed for a text value.
Next, you could open a pop-up form whose Record Source is this temporary
table and manipulate the data. If you don't delete the temporary data each
time you close the pop-up form, you'll need to move to the record you just
added when you open the pop-up.