Copy a single record from one table to another

[GraeagleBill]

I've recently had an occasion to copy a record to another table:

        CurrentDb.Execute "INSERT INTO tblAutoTrans SELECT * FROM tblRegister WHERE TRecID =" & Me.TRecID & ";"

The code works fine but I'm surprised that the newly created record in table "tblAutoTrans" was not assigned a new primary autonumber value in its record ID field. Is that normal with such cases as with this copy operation?

Would it be a more preferred approach to open a DOA Recordset of the target table and use AddNew to copy the desired fields?



Bill

[Welshgasman]

So did you get the AutonumberID from the tblRegister instead?

[GraeagleBill]

Yes, the copy operation copied the record ID to the newly created copy in table "tblAutoTrans".

[Gicu]

Instead of using the SELECT.* use SELECT [Field2],[Field3].... leaving the autonumber field out and it will work as expected.

Cheers,

[Welshgasman]

Yes, the copy operation copied the record ID to the newly created copy in table "tblAutoTrans".

I must admit I was not aware it would even do that, but rather complain?

As mentioned omit that field then.

However wouldn't you then lose any relationships? How would you tie that new record back to it's original source?

[GraeagleBill]

Thanks Vlad, that's all that was needed.
Bill

[GraeagleBill]

However wouldn't you then lose any relationships?

Normally one would have to consider any relationships pertaining to the record ID. In the current app, the user is simply in the midst of capturing selected records for purposes of creating register transaction templates devoid of any relationship concerns.

Thanks,
Bill