Solved

Clone Record

Posted on 2010-11-17
9
927 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.
0
Comment
Question by:jodstrr2
9 Comments
 
LVL 7

Expert Comment

by:shaydie
ID: 34156249
You should save the record before cloning it. Depending on your version 'If me.dirty = true then me.dirty = false' should work..
0
 

Author Comment

by:jodstrr2
ID: 34156412
How do I save the record without close the form?
0
 
LVL 7

Expert Comment

by:shaydie
ID: 34156498
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
 

Author Comment

by:jodstrr2
ID: 34157080
I tried both, it seems still not working.  I'm using access2003
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 7

Expert Comment

by:shaydie
ID: 34157612
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
 

Author Comment

by:jodstrr2
ID: 34158478
it still not working.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 34158936
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
 

Author Closing Comment

by:jodstrr2
ID: 34159100
Thanks
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41761154
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now