We help IT Professionals succeed at work.

Clone Record

jodstrr2
jodstrr2 asked
on
Medium Priority
1,057 Views
Last Modified: 2016-08-18
Hi Experts,
I have a question. I have a clone button on a form for users to clone a record.  below is my coding on the On Click of the Clone Button
Private Sub Command101_Click()
Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim Criteria As String
Dim Criteria2 As String
Dim HoldID As Double
Dim HoldBookmark As Double
'    rs2 = me.RecordsetClone
    Set db = CurrentDb
    Criteria = "Select * from Event_Service where 1=2"
    Set rs = db.OpenRecordset(Criteria, dbOpenDynaset, dbSeeChanges)
    rs.AddNew
        rs!SETYPE = "Service"
        rs!SECASENUM = Me.SECASENUM
        rs!SERIN = Me.SERIN
        rs!SEDOCKETNO = Me.SEDOCKETNO
        rs!SEDTRCVD = Me.SEDTRCVD
        rs!SENOTSERV = Me.SENOTSERV
        rs!SEIBNO = Me.SEIBNO
        rs!SEDTREFTO = Me.SEDTREFTO
        rs!SEWHATSERV = Me.SEWHATSERV
        rs!SETYPESER = Me.SETYPESER
        rs!SESERVER = Me.SESERVER
        rs!SEASSIGNEDTO = Me.SEASSIGNEDTO
        rs!SEUSER = Environ("user")
        rs!SEDATE = DATE
        rs!SEUPDUSR = Environ("user")
        rs!SEUPDT = DATE
      rs.Update
    rs.Bookmark = rs.LastModified
    HoldID = rs!SEID
    rs.Close
    Me.Filter = "SEID = " & HoldID
    Me.Requery
   
Exit_Command101_Click:
    Exit Sub

Err_Command101_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_Command101_Click

End Sub


This coding works fine if a user enter a new record then close the record, then reopen the same record and click the Clone Button, it does what is suppose.  However, if a user just create a new record and click the Clone Button before the user save new record, it clear every thing on the record that the user want to clone and also create an extra empty record which means it creates two new record instead one new record.  Any help would be very appreciate.
Comment
Watch Question

Commented:
You should save the record before cloning it. Depending on your version 'If me.dirty = true then me.dirty = false' should work..

Author

Commented:
How do I save the record without close the form?

Commented:
What is your version of Access?

Depending on your version...

Place this line right after your Dim statements

If me.dirty = true then me.dirty = false

That will save the record.

In older versions I think the command was something like

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70


Author

Commented:
I tried both, it seems still not working.  I'm using access2003

Commented:
Is this basically what you have? Try the code below.. does it work? Any errors? Same problem?

Dim db As Database
Dim rs As Recordset
Dim HoldID As Double

    If Me.Dirty = True Then Me.Dirty = False
   
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Event_Service", dbOpenDynaset, dbSeeChanges)
    rs.AddNew
        rs!SETYPE = "Service"
        rs!SECASENUM = Me.SECASENUM
        rs!SERIN = Me.SERIN
        rs!SEDOCKETNO = Me.SEDOCKETNO
        rs!SEDTRCVD = Me.SEDTRCVD
        rs!SENOTSERV = Me.SENOTSERV
        rs!SEIBNO = Me.SEIBNO
        rs!SEDTREFTO = Me.SEDTREFTO
        rs!SEWHATSERV = Me.SEWHATSERV
        rs!SETYPESER = Me.SETYPESER
        rs!SESERVER = Me.SESERVER
        rs!SEASSIGNEDTO = Me.SEASSIGNEDTO
        rs!SEUSER = Environ("user")
        rs!SEDATE = Date
        rs!SEUPDUSR = Environ("user")
        rs!SEUPDT = Date
      rs.Update
      rs.Bookmark = rs.LastModified

    HoldID = rs!SEID
    Me.Requery
    rs.Close
    Me.Filter = "SEID = " & HoldID
    Me.FilterOn = True

Author

Commented:
it still not working.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Here is a method that works. You should be able to modify it to your exact needs.

/gustav
Private Sub btnCopy_Click()

  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field
  
  If Me.NewRecord = True Then Exit Sub
    
  Set rstInsert = Me.RecordsetClone
  Set rstSource = rstInsert.Clone
  With rstSource
    If .RecordCount > 0 Then
      ' Go to the current record.
      .Bookmark = Me.Bookmark
      With rstInsert
        .AddNew
          For Each fld In rstSource.Fields
            With fld
              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
              Else
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If
            End With
          Next
        .Update
        ' Go to the new record and sync form.
        .MoveLast
        Me.Bookmark = .Bookmark
        .Close
      End With
    End If
    .Close
  End With
  
  Set rstInsert = Nothing
  Set rstSource = Nothing
  
End Sub

Open in new window

Author

Commented:
Thanks
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
DO NOT USE
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

It is from Access 95.

The correct method in all versions of Access is -
DoCmd.RunCommand acCmdSaveRecord

In some cases you might need to use the suggested hack -
If Me.Dirty Then
    Me.Dirty = False
End If

Some people recommend using the hack in all cases but I have run into issues with the hack as well as the explicit save command.  Therefore, I use the explicit save command because it is the intended solution and its intention is clear.  I only use the hack if I have a problem which has only happened once in 20 years.