How to Append a duplicate record and just change the key?

[Guest]

I am trying to duplicate an entire record with a new key without listing
every field (too many). I tried this, but it didn't work. There must be a way!

strSql=Select statement to select record to copy
Set rstSaved = mydb.OpenRecordset(strSql, dbOpenSnapshot)
With rstSaved
Set rstNew = mydb.OpenRecordset("Proposal", dbOpenDynaset)
rstNew.AddNew
rstNew!Key1 = Key1
rstNew!Key2 = Key2
For Each fld In .Fields
If fld.Name <> Key1 And fld.Name <> Key2 Then
rstNew.Fields(fld.Name) = (fld.Value)
End If
Next
rstNew.Update
rstNew.Close
.Close
End With

 

[Douglas J Steele]

Your code is a little confusing.

You've got fields named Key1 and Key2, and you're assigning them the values
contained in variables Key1 and Key2. However, your loop is comparing the
field names to Key1 and Key2, which means the variables.

I think you want

If fld.Name <> "Key1" And fld.Name <> "Key2" Then

 

[Guest]

Sorry for the confusion, I just replaced the Key names for example purposes.
That part of the code is working, it's the code:

rstNew.Fields(fld.Name) = (fld.Value)

that I'm having a problem with. The record gets updated with only the keys
filled in. All other fields are blank.

 

[Douglas J. Steele]

It sounds as though that If statement isn't working for you. What do you
have in reality there?

If your code is getting into that loop, try removing the parentheses around
fld.Value.

In reality, though, you'd be far better off using an INSERT INTO statement.
Yes, you'd need to list all of the fields, but you only have to do that
once.

 

[Sirocco]

When I want to duplicate a record, I use "The 3 query method". It's my own
invention. I query the current record, modify whatever fields need to be
changed in a 2nd query (such as key fields), then use a 3rd query to append
this record to the table. Works! I only use VB when there isn't a SQL
solution.

 

[Guest]

There are too many fields, and more importantly the database is still
evolving, so here's what I did and it worked. I did this before I tried your
parenthesis suggestion, so I'm not sure if it would work.

For intTemp = 2 To rstSaved.Fields.Count - 1
rstNew(intTemp) = rstSaved(intTemp)
Next

(the first two fields are the keys)

 

[Douglas J. Steele]

Don't be so lazy! <g>

It's almost always more efficient to use SQL than to use a recordset.

INSERT INTO MyTable (Key1, Key2, Field3, Field4, ..., Fieldn)
SELECT NewValue1, NewValue2, Field3, Field4, ..., Fieldn
FROM MyTable
WHERE Key1 = OldValue1
AND Key2 = OldValue2

It's also fairly trivial to generate the list of fields and write them to
the Debug window, where you can then copy it into your code.