jodstrr2
asked on
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.
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,
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.
You should save the record before cloning it. Depending on your version 'If me.dirty = true then me.dirty = false' should work..
ASKER
How do I save the record without close the form?
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
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
ASKER
I tried both, it seems still not working. I'm using access2003
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_Se rvice", 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
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_Se
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
ASKER
it still not working.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
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.
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.