How to copy some fields in current record into new record?

[Chris]

I have access 2000 and I wish to be able to copy 5 out of
about 10 fields into a new record automatically using a
command button on a form. Similar to the way the duplicate
buttom works but only with these selected fields. None of
the fields I wish to duplicate are the primary key.

Thank muchly

Chris

 

[Larry Linson]

In the form's module, Dim a variable for each Control whose contents you
want carried forward, in the Click event of the Command Button copy the
Controls' values to the variable. Then, in the OnCurrent event of the new
Record, copy the content of the variables to the appropriate Controls on the
new Record.

Larry Linson
Microsoft Access MVP

 

[Armen Stein]

In the form's module, Dim a variable for each Control whose contents you
want carried forward, in the Click event of the Command Button copy the
Controls' values to the variable. Then, in the OnCurrent event of the new
Record, copy the content of the variables to the appropriate Controls on the
new Record.

Larry Linson
Microsoft Access MVP

Be careful here - the OnCurrent event will fire on existing records too,
thereby copying the saved data into them also.

You can check the Me.NewRecord property and only copy the fields if it
is True.

 

[Dirk Goldgar]

Be careful here - the OnCurrent event will fire on existing records
too, thereby copying the saved data into them also.

You can check the Me.NewRecord property and only copy the fields if it
is True.

I think if the idea is to click the command button and thereby start a
new record with certain fields already filled in from the current
record, I wouldn't use the Current event for anything. The command
button's Click event would do it all, something like this:

'----- start of example code -----
Private Sub cmdCopyRecord_Click()

Dim v1 As Variant
Dim v2 As Variant
Dim v3 As Variant
Dim v4 As Variant
Dim v5 As Variant

v1 = Me!Field1.Value
v2 = Me!Field2.Value
v3 = Me!Field3.Value
v4 = Me!Field4.Value
v5 = Me!Field5.Value

RunCommand acCmdRecordsGoToNew

Me!Field1 = v1
Me!Field2 = v2
Me!Field3 = v3
Me!Field4 = v4
Me!Field5 = v5

End Sub
'----- end of example code -----

 

[jbrumbau]

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.