• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 954
  • Last Modified:

Clone Record

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.
0
jodstrr2
Asked:
jodstrr2
1 Solution
 
shaydieCommented:
You should save the record before cloning it. Depending on your version 'If me.dirty = true then me.dirty = false' should work..
0
 
jodstrr2Author Commented:
How do I save the record without close the form?
0
 
shaydieCommented:
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


0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
jodstrr2Author Commented:
I tried both, it seems still not working.  I'm using access2003
0
 
shaydieCommented:
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
0
 
jodstrr2Author Commented:
it still not working.
0
 
Gustav BrockCIOCommented:
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

0
 
jodstrr2Author Commented:
Thanks
0
 
PatHartmanCommented:
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.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now