Easiest way to copy/add identical record.

[ThomasAJ]

I want to store a record with several hundred fields to another identical
table before I delete that record.

Can I enumerate each field like:
for each field in table1

Can someone help me with the code please.

Or is there an easier way?

 

[Boyd Trimmell aka HiTechCoach via AccessMonster.co]

If the table is not changing in the design (fields), then I would just use
the query designer to build an append query.

In a a query select and drag all the field into the grid. Change the query to
an append query. After you select the table. Access will automatically match
up the fields. It should take lesss than a minute to build.

 

[Allen Browne]

You can do it, but before you do, please consider whether this table is
normalized.

If you have 'several hundred fields', there is no way you have a correct
database design. IMHO, you could make better use of your time by reading up
on normalization rather than by writing code to try to work around the real
problem.

If you want to do it anyway, OpenRecordset on a SQL string that selects just
the one record, and AddNew to the RecordsetClone. You can then loop through
the Fields of the Recordset.

Here's an OpenRecordset() example:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample
To loop through the fields of the Recordset, add:
Dim fld As DAO.Field
For Each fld In rs.Fields
Debug.Print fld.Name
Next