Link to home
Start Free TrialLog in
Avatar of dgi001
dgi001Flag for United Kingdom of Great Britain and Northern Ireland

asked on

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

Hi
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
DGI
Avatar of Darrell Porter
Darrell Porter
Flag of United States of America image

Why not copy the entire record and set the fields you don't want copied to null, 0 or blank?
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 = " * "
    Else
        For intLoop = LBound(varFields) To UBound(varFields)
            If varFields(intLoop) <> PKField Then
                strFields = strFields & ", [" & varFields(intLoop) & "] "
            End If
        Next
        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)
    
    rsDest.AddNew
    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
    Next
    rsDest.Update
    rsDest.Bookmark = rsDest.LastModified
    
    fnCopyRecord = rsDest.Fields(PKField)
    
ProcExit:
    If Not rsDest Is Nothing Then
        rsDest.Close
        Set rsDest = Nothing
    End If
    If Not rsSource Is Nothing Then
        rsSource.Close
        Set rsSource = Nothing
    End If
    If Not db Is Nothing Then Set db = Nothing
    
    Exit Function
    
ProcError:
    MsgBox Err.Number & vbCrLf & Err.Description, vbOKOnly
    Debug.Print "fnCopyRecord", Err.Number, Err.Description
    fnCopyRecord = 0
    Resume ProcExit
    
End Function

Open in new window


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

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

Or...

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
    Me.SomeField=""
    Me.SomeOtherField=""

JeffCoachman

BTW,

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.
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dgi001

ASKER

That worked exactly as requested.
Thanks Fyed.