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

Posted on 2011-10-28
Last Modified: 2012-05-12
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
Question by:dgi001
    LVL 15

    Expert Comment

    Why not copy the entire record and set the fields you don't want copied to null, 0 or blank?
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    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

    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    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


    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)

    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.
    LVL 47

    Accepted Solution

    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

    Author Closing Comment

    That worked exactly as requested.
    Thanks Fyed.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now