Solved

Clone Record

Posted on 2010-11-17
9
931 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:jodstrr2
ID: 34157080
I tried both, it seems still not working.  I'm using access2003
0
 
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 35

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

776 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