This didn't answer my question as I wanted to duplicate a record with multiple fields but change the unique ones simultaneously. I made a nifty subroutine to automate copying one recordset to another with relative ease:
Sub copyRecord(ByRef rs As Recordset, ByRef keyName As String, Optional ByRef keyVal As Long = 0, _
Optional ByRef tagName As String = "", Optional ByRef tagVal As Variant = "")
Dim rsNew As Recordset
Dim i As Integer
Set rsNew = rs.Clone
rsNew.AddNew
For i = 0 To rsNew.Fields.Count - 1
If StrComp(rsNew.Fields(i).Name, keyName) = 0 Then
If keyVal <> 0 Then
rsNew.Fields(i).Value = keyVal
End If
ElseIf LenB(keyName) > 0 Then
If StrComp(rs.Fields(i).Name, tagName) = 0 Then
If keyVal <> 0 Then
rsNew.Fields(i).Value = tagVal
End If
ElseIf Not IsNull(rs.Fields(i).Value) Then
rsNew.Fields(i).Value = rs.Fields(i).Value
End If
Else
If Not IsNull(rs.Fields(i).Value) Then
rsNew.Fields(i).Value = rs.Fields(i).Value
End If
End If
Next i
rsNew.Update
rsNew.Close
End Sub
This code assumes that recordset rs is already on the record to be copied, and also that the rs.EOF and rs.NoMatch checks have already been done. KeyName/Value would be the unique key for the table, and tagName/Value would be for some other unique field in the table. You'll have to modify this code to pass in more unique fields.