Copy current record on form to new record in underlying table

[Moss100]

Hello,

I have a single form which does not allow addition of records.

I would like a button on the form to copy the current record and duplicate it so that it is stored in the underlying table.

Please could someone give me some basic code for doing this.

Many thanks Mark

 

[dhookom]

Set the code behind the button to run an SQL statement like:

Dim strSQL as String
strSQL = "INSERT INTO tblYourTable ([Your field list here]) " & _
    "SELECT [Your field list here] FROM tblYourTable WHERE [YourPrimaryKey] = " & Me.txtYourPrimaryKey
CurrentDb.Execute strSQL, dbFailOnError

If you need more help, we need actual table and field names and data types.

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

Is there a way of selecting all fields or do I have to put each field in separately?

If I select * - would that work, or would it error as it would duplicate the primary key?

Many thanks for your help this far.

Mark

 

[Andrzejek]

You may try it, but it will error (just a guess here) because of PK
So list all the fields (without PK field) twice in your (dhookom's) INSERT statement.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

I typically use the Acces query designer interface to build most of the SQL statement and then copy it into my code.

Duane
Hook'D on Access
MS Access MVP