dgi001
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
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
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")
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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked exactly as requested.
Thanks Fyed.
Thanks Fyed.