Insert current record into another table

[epoh]

Hi all:

I need to in essence "move" a record from one table to another. I want the user to click 'move record' and perform the following actions:

• Mark the record in table1 as moved
• INSERT INTO table2 using current record in form

Thanks

 

[boblarson]

Easiest solution for you is use the QBE (query by example) grid and create an append query and run it. You can set the criteria to be a control on the form that holds the ID of the record you want to do it to.

 

[epoh]

I see, so I cant just mark the record as moved in the table1 and then perform a SQL INSERT INTO Statement?

That is what I really want to know. Is how to perform a sql insert into statement in access within a form.

 

[KeithG]

Add a button on the form.
create your append query

in the click event use

Docmd.openquery("[AppendQueryName]")


also you can use docmd.setwarnings FAlse to supress the warnings to the user.

 

[epoh]

Thanks Keith, I like that tip to suppress messages.

Ok, so I just cant do a DoCmd.Runsql "INSERT INTO (Field1,Field2) VALUES (Me!Field1.Value,Me!Field2.Value)

?

If I cant, how do I supply the Append Query data from the current record?

 

[KeithG]

Docmd.runsql should work for you but I prefer to create the query with the Query designer instead of hard coding the SQL

 

[GaryPanic]

If you check the samples - there was a real smart way of doing this without the qry option

 

[boblarson]

Sample using the QBE:

Then match up the fields you are appending to in the APPEND TO row.

 

[GaryPanic]

ooohhh!! pictures

 

[epoh]

Thank you all for the help. It worked as expected.