Copy a record and open in form

[klnlsu]

I have a record open in a form. I would like to copy that record into the same linked Access table and then open that copied record in another form. What is the most efficient way to accomplish this in VBA code? I know I am being kind of vague, but I'm not sure what details I might need to add. Let me know.



Thanks.

[jwhite]

Run an Update query using Currentdb.Execute that will fetch a record from the table that matches the primary key of the record you are viewing (WHERE YourPrimaryKeyField = YourPrimaryKeyBeingViewed) and add the new record, then open a recordset using @@IDENTITY to get the PK of the record just added, and finally filter/refresh the form to view the record for PK just added. See: http://stackoverflow.com/questions/8...msaccess-query

[ranman256]

You'd use the keys on the 2 forms to run an append query...

sub btnCopy_click()
Docmd.openQuery  "qaCopyData"
end sub

The query would use the key on the form as the source ,but user must enter the key from the target.
there could be any # of forms open so the app would not know which to use,so the user should enter the target key by hand.
the query will ask for the target id as a param.....

insert into table (ID, field2) select [Enter Target ID] , field2 from table where [id] = forms!myForm!txtID

[klnlsu]

I have tried using the INSERT INTO command using the following code:
Dim db As Database
Set db = CurrentDb
db.Execute "INSERT INTO tblOEOrderHeader SELECT * FROM tblOEOrderHeader WHERE OrderHeaderID = OrderHeaderIDTxt", dbFailOnError

OrderHeaderID is the autonumber field of the record and OrderHeaderIDTxt is the textbox on the form that holds the autonumber field of the record I am trying to duplicate. I have tried the command with and without the "dbFailOnError" parameter.

I get this error message:
"Too few parameters. Expected 1."

[jwhite]

I presume your OrderHeaderID is numeric, therefore:
db.Execute "INSERT INTO tblOEOrderHeader SELECT * FROM tblOEOrderHeader WHERE OrderHeaderID = " & Me.OrderHeaderIDTxt, dbFailOnError

But I wouldn't use * - I would list the fields.

[klnlsu]

Thank you for correcting my syntax. That worked.