Copy Paste Form Data

[Dave]

I would like to copy data I enter in a form's field(s) and paste it into
another form's field(s). The purpose is to copy and paste time and miles for
a transport log. The (cntrl ') will not work since the records are not in
sequential order. Is there a way to make a command button one "Copy" and
another "Paste" for this purpose on the forms? Thanks.

 

[Allen Browne]

Here's an example of how to AddNew to the RecordsetClone of the other form.
The code lets you specify which fields you want to copy, making no
assumptions that the fields have the same name or are in the same order.

Dim frm As Form
Dim rs As DAO.Recordset

'Save any edits.
If Me.Dirty Then Me.Dirty = False
'Check there is a record to copy.
If Me.NewRecord Then
MsgBox "Select a record to copy"
Else
'Open the other form if needed, and save any edits.
If Not CurrentProject.AllForms("Form2").IsLoaded Then
DoCmd.OpenForm "Form2"
End If
Set frm = Forms("Form2")
If frm.Dirty Then frm.Dirty = False

'Add the new record to the other form's clone set.
Set rs = frm.RecordsetClone
rs.AddNew
'Substitute your field names on the following lines.
rs!TruckID = Me.TruckID
rs!TripDate = Me.TripDate
rs!Odometer = Me.Odometer
'etc for other fields
rs.Update

'Show this record in the other form.
frm.Bookmark = rs.LastModified
frm.SetFocus
End If

Set rs = Nothing
Set frm = Nothing

 

[Dave]

I found a simpler way to do what I am looking for. The only problem is that
it does not paste date fields or time fields. If I remove the date and time
fields from the form, it works. Here is the code:

'On the copy button (Command2) this code in the Click Event:

Private Sub Command2_Click()
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
End Sub

'On the paste button (Command3) this code in the Click Event:
Private Sub Command3_Click()
DoCmd.RunCommand acCmdPasteAppend
End Sub

If you know how to fix it so it will copy/paste date and time fields too,
you will be the man. Thank you.

 

[Allen Browne]

That code should work for date/time fields, but it does have several
problems.

It messes up with unbound fields, fields bound to expressions, calculated
fields, and fields from other tables (where your form is based on a query.)

It also lacks the flexibility to choose which fields you want to duplicate.

So, it works for limited scenarios, where as the more detailed approach
gives you the flexibility and power to do exactly what you want.