[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

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
1 Solution
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

Jeffrey CoachmanCommented:

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


Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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.
Dale FyeCommented:
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
dgi001Author Commented:
That worked exactly as requested.
Thanks Fyed.

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now