Code that will copy a record to another table

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

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?
 
If by "manipulate" you mean perform some calculations on, consider using a
query and adding fields in which you perform those calculations.
 
You should be able to use an INSERT INTO query.

INSERT INTO Table1 (Field1, Field2, ...)
SELECT Field1, Field2, ... FROM Table2
WHERE Id = 123
 
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.
 
Back
Top