make a copy of record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In XP I want to make a copy of a record, in DAO, with a new Autonumber key.
I know how to do this on a field by field basis, but wonder if there is any
way to just copy the entire record without doing field by field. This is a
lot of fields, and I also need to do it for a bunch of related tables.
Thanks!
Ron
 
If you are trying to copy a record and also its related records, a starting
point might be:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html

The article does suggest naming each field, as that is the safest approach.
However, you could try looping through the Fields collection instead of
supplying their names. For example, if you have 2 recordsets (say rsSource
and rsTarget):
For i = 0 to rs.Fields.Count - 1
rsTarget(i) = rsSource(i)
Next

Similarly, you will probably use an Append query statement to duplicate the
records in the related table. You can use the wildcard instead of listing
fields. The string will be something like this:
INSERT INTO MyTargetTable
SELECT MySourceTable.*
FROM MySourceTable
WHERE ...
 
If you have a table with "a lot of fields" and you want multiple records
that differ only in the value of one field, there's probably something
wrong with your data structure. Almost certainly you can normalise this
"wide" table into two or more taller, narrower ones.

To do what you ask, you have to work one field at a time. Either build
and execute an append query that lists all the fields *except* the
autonumber, e.g. (air code)

strSQL = "INSERT INTO MyTable (F2, F3, F4) " _
& "SELECT F2, F3, F4 FROM MyTable WHERE KeyField=" _
& Me.KeyField.Value & ";"
CurrentDB.Execute strSQL, dbFailOnError

or (more air code)
Dim rsFrom As DAO.Recordset, rsTo As DAO.Recordset
Dim dbD as DAO.Database
Dim j As Long

Set dbD = CurrentDB()
Set rsFrom = dbD.OpenRecordset("SELECT * FROM MyTable " _
& " WHERE KeyField=" & Me.KeyField.Value & ";", dbOpenSnapshot
Set rsTo = dbD.OpenRecordset("SELECT * FROM MyTable " _
& " WHERE FALSE"; 'recordset with no records
rsTo.AddNew
For j = 0 To rsFrom.Fields.Count - 1
If rsFrom.Fields(j).Name <> "KeyField" Then
rsTo.Fields(j).Value = rsFrom.Fields(j).Value
End If
Next
rsTo.Update
rsFrom.Close
rsTo.Close
 
Back
Top