Solved

Clone Record

Posted on 2010-11-17
9
918 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…

760 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

22 Experts available now in Live!

Get 1:1 Help Now