Copying data from one field in existing record to the same field in a new record

I want to copy certain fields from one record to a new record using a copy button. We input data where e.g. the model and make can be the same if we bulk buy. Therefore, i want to click a copy button that will open a new record and copy cetain fields from the previous record to save retyping. I know how to copy the whole record using the DoCmd.RunCommand but cant see how to select certain fields.

Kind regards
Who is Participating?
Dale FyeConnect With a Mentor Commented:
I wrote fnCopyRecord to be a generic, that I could use whenever I needed it.  But a version that you could use in a button click would look something like the following.  It would copy the values from field1, field3, and field5 of the record displayed on the current form

Private sub cmd_Copy_Click

    Dim rs as DAO.Recordset
    dim lngID as long

    me.dirty = false 'makes sure the current record is saved before copying
    set rs = me.recordsetclone

    with rs
        !Field1 = me.txt_Field1
        !Field3 = me.txt_Field3
        !Field5 = me.cbo_Field5
        .bookmark = .lastmodified
        lngID = !ID
    end with

    set rs = nothing
    with me.recordsetclone
        .findfirst "[ID] = " & lngID
        me.bookmark = .bookmark

End Sub
Darrell PorterEnterprise Business Process ArchitectCommented:
Why not copy the entire record and set the fields you don't want copied to null, 0 or blank?
Dale FyeCommented:
I have a function I use for this purpose (see below).

I returns the PKID of the record that was added to the recordset (or 0 if an error occurred).

As an example, if you wanted to copy the country_CD and Country_Name fields from tbl_Countries to a new record, it would look like:

NewID = fncopyrecord("ID", 1, "tbl_Countries", "Country_CD", "Country_Name")

If you wanted to copy all of the fields from that table, you just leave off the fields

NewID = fncopyrecord("ID", 1, "tbl_Countries")
Public Function fnCopyRecord(PKField As String, PKID As Long, TableName As String, ParamArray varFields() As Variant) As Long

    Dim strFields As String, strSQL As String
    Dim db As DAO.Database
    Dim rsSource As DAO.Recordset, rsDest As DAO.Recordset
    Dim intLoop As Integer
    On Error GoTo ProcError
    If UBound(varFields()) = -1 Then
        strFields = " * "
        For intLoop = LBound(varFields) To UBound(varFields)
            If varFields(intLoop) <> PKField Then
                strFields = strFields & ", [" & varFields(intLoop) & "] "
            End If
        strFields = Mid(strFields, 2)
    End If
    strSQL = "SELECT " & strFields & "FROM [" & TableName & "] WHERE [" & PKField & "] = " & PKID
    'remove duplicate brackets
    strSQL = Replace(Replace(strSQL, "[[", "["), "]]", "]")
    Set db = CurrentDb
    Set rsSource = db.OpenRecordset(strSQL, , dbFailOnError)
    strSQL = "SELECT [" & PKField & "], " & strFields & "FROM [" & TableName & "] WHERE False"
    strSQL = Replace(Replace(strSQL, "[[", "["), "]]", "]")
    Set rsDest = db.OpenRecordset(strSQL, , dbFailOnError)
    For intLoop = 0 To rsSource.Fields.Count - 1
        If rsSource.Fields(intLoop).Name <> PKField Then
            rsDest.Fields(rsSource.Fields(intLoop).Name) = rsSource.Fields(intLoop)
        End If
    rsDest.Bookmark = rsDest.LastModified
    fnCopyRecord = rsDest.Fields(PKField)
    If Not rsDest Is Nothing Then
        Set rsDest = Nothing
    End If
    If Not rsSource Is Nothing Then
        Set rsSource = Nothing
    End If
    If Not db Is Nothing Then Set db = Nothing
    Exit Function
    MsgBox Err.Number & vbCrLf & Err.Description, vbOKOnly
    Debug.Print "fnCopyRecord", Err.Number, Err.Description
    fnCopyRecord = 0
    Resume ProcExit
End Function

Open in new window

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Jeffrey CoachmanMIS LiasonCommented:

Depending on the ratio of Fields to Copy/Total Fields...

...If this is a continuous or datasheet form you can use the hotkey combination:
(Hold down the Control key and tap the  "  key)
This will cop the value above into the current record


Simply copy the entire record and clear out the fields you don't want copied:
(From the "Duplicate Record" button wizard code)
    'Copy the current record
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdCopy
    DoCmd.RunCommand acCmdRecordsGoToNew
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdPaste
    'Clear any other fields


Dale FyeCommented:

That code was an early version of fnCopyRecord, so it does not contain error checking to ensure that the field names provided actually exist in the table.

I would recommend that you add that if you choose to use the function.
dgi001Author Commented:
That worked exactly as requested.
Thanks Fyed.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.