By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,454 Members | 2,629 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,454 IT Pros & Developers. It's quick & easy.

Duplicating RecordsetClone

P: n/a
I have found a wealth of information here on how to duplicate records
in forms with subforms.

I have adapted code found here to work with my forms. It works
beautifully the first time I hit the "duplicate" button. It copies the
main form data, the subform data and moves to the new record. Great!

Here's the problem...if I move to another record (or stay on the newly
created record, for that matter), and hit the Duplicate button again, I
get the 3021 (no current record) error. To duplicate again, I need to
close out of the main form and reopen it.

I've tried a number of things, but to no avail...

Anyone have any ideas?

Here is the code I am using that works on the first duplicate. (Please
don't yell at me because I don't conform to the standard naming
conventions; I know, I know!)

======
Private Sub cmdDupe_Click()

'MsgBox "This is a future enhancement.", , DefTitle
'Exit Sub

DoCmd.SetWarnings False

On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in
the subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.
Dim fld As Field
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Contract Information")

'Save edits first
If Me.Dirty Then
Me.Dirty = False
End If

Set rst = Me.RecordsetClone
OldID = Me.ID

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With rs
.AddNew
For Each fld In rs.Fields
FldName = fld.Name
FldValue = fld.Value

Select Case FldName
Case "ID"
'Do Nothing
Case "Date Modified"
rs.Fields(FldName).Value = Now()
Case Else
rs.Fields(FldName).Value =
rst.Fields(FldName).Value

End Select
Next
.Update

'Save the primary key value, to use as the foreign key for
the related records.
.Bookmark = .LastModified

lngID = rs!ID
DupeID = lngID
End With
End If

Me.Requery
DoCmd.OpenQuery "Append Contract Equipment"
DoCmd.OpenQuery "Append to Invoice Details"

DoCmd.GoToControl "ID"
DoCmd.FindRecord lngID

Me.Dirty = False

rs.Close

Set db = Nothing
Set rs = Nothing

DoCmd.SetWarnings True

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & err.Number & " - " & err.Description, , DefTitle
Resume Exit_Handler

End Sub

May 3 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
I have no problem with this (ms access 2000).
I use the:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

to save the newly added record and then I can go on.

Hope this helps :-)

Me.Name
Josetta skrev:
I have found a wealth of information here on how to duplicate records
in forms with subforms.

I have adapted code found here to work with my forms. It works
beautifully the first time I hit the "duplicate" button. It copies the
main form data, the subform data and moves to the new record. Great!

Here's the problem...if I move to another record (or stay on the newly
created record, for that matter), and hit the Duplicate button again, I
get the 3021 (no current record) error. To duplicate again, I need to
close out of the main form and reopen it.

I've tried a number of things, but to no avail...

Anyone have any ideas?

Here is the code I am using that works on the first duplicate. (Please
don't yell at me because I don't conform to the standard naming
conventions; I know, I know!)

======
Private Sub cmdDupe_Click()

'MsgBox "This is a future enhancement.", , DefTitle
'Exit Sub

DoCmd.SetWarnings False

On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in
the subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.
Dim fld As Field
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Contract Information")

'Save edits first
If Me.Dirty Then
Me.Dirty = False
End If

Set rst = Me.RecordsetClone
OldID = Me.ID

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With rs
.AddNew
For Each fld In rs.Fields
FldName = fld.Name
FldValue = fld.Value

Select Case FldName
Case "ID"
'Do Nothing
Case "Date Modified"
rs.Fields(FldName).Value = Now()
Case Else
rs.Fields(FldName).Value =
rst.Fields(FldName).Value

End Select
Next
.Update

'Save the primary key value, to use as the foreign key for
the related records.
.Bookmark = .LastModified

lngID = rs!ID
DupeID = lngID
End With
End If

Me.Requery
DoCmd.OpenQuery "Append Contract Equipment"
DoCmd.OpenQuery "Append to Invoice Details"

DoCmd.GoToControl "ID"
DoCmd.FindRecord lngID

Me.Dirty = False

rs.Close

Set db = Nothing
Set rs = Nothing

DoCmd.SetWarnings True

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & err.Number & " - " & err.Description, , DefTitle
Resume Exit_Handler

End Sub


May 4 '06 #2

P: n/a
"Josetta" <Jo*****@smartdot.net> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.com...

I've tried a number of things, but to no avail...


Does that include Me.Refresh and Me.Requery? Also, couldn't you use append
queries with the bookmark property to simplify the code?

Regards,
Keith.
www.keithwilby.com
May 4 '06 #3

P: n/a
Thanks for your suggestions (and sorry I'm replying today; I was out
yesterday).

Remember, when I press the duplicate button the first time, it works
perfectly. It's on the second time I press it without closing the form
that I get the 3021 error.

I am using Access 2003.

Yes, I have done the requery and the refresh...still doesn't work the
second time around.

Keith, can you expand on using append queries with the bookmark
property? I don't think I've every used the bookmark property in a
query. How do I tap into that property in a query?

May 5 '06 #4

P: n/a
Josetta wrote:
Thanks for your suggestions (and sorry I'm replying today; I was out
yesterday).

Remember, when I press the duplicate button the first time, it works
perfectly. It's on the second time I press it without closing the form
that I get the 3021 error.

I am using Access 2003.

Yes, I have done the requery and the refresh...still doesn't work the
second time around.

Keith, can you expand on using append queries with the bookmark
property? I don't think I've every used the bookmark property in a
query. How do I tap into that property in a query?


You'd use the bookmark property in your form's code, not the query.
It's a way of getting access to remember which record the form is
looking at when you need to navigate away from it (eg create a new
record). It's all there in the help :-)

Regards,
Keith.
www.keithwilby.com
May 7 '06 #5

P: n/a
Josetta wrote:
Anyone have any ideas?


My idea would be to use this in the main form:

Private Sub DuplicateRecord()
With DoCmd
.RunCommand acCmdSelectRecord
.RunCommand acCmdCopy
.RunCommand acCmdPasteAppend
End With
' make some changes to the new record
txtAmount = -(txtAmount)
txtDate = Date
' call same of SubForm
Form_SubFormName.DuplicateRecord
End Sub

and in the SubForm

Public Sub DuplicateRecord()
With DoCmd
.RunCommand acCmdSelectRecord
.RunCommand acCmdCopy
.RunCommand acCmdPasteAppend
End With
End Sub

I do this regularly with main forms but I have not extended the idea to
SubForms. I can't think of a reason it wouldn't work, but there may be
such a reason. We might have to add code giving the SubForm focus
before duplicating its record.

May 7 '06 #6

P: n/a
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@j73g2000cwa.googlegr oups.com...

Public Sub DuplicateRecord()
With DoCmd
.RunCommand acCmdSelectRecord
.RunCommand acCmdCopy
.RunCommand acCmdPasteAppend
End With
End Sub

I do this regularly with main forms but I have not extended the idea to
SubForms. I can't think of a reason it wouldn't work, but there may be
such a reason.


Hi Lyle, might such a reason be that there may be more than one record in
the subform's recordset?

Regards,
Keith.
May 8 '06 #7

P: n/a
Sounds likely to me, yes.

May 8 '06 #8

P: n/a
Thank you Keith and Lyle for your comments and help. Yes, I do
understand how to use the bookmark property in code; for some reason, I
read Keith's previous post as using the bookmark property in a query
and I have never done that. This whole thing is perplexing, in that
the FIRST time I hit the duplicate button, it works just perfectly. If
I try to press it again, I get the error. I can't figure out why that
is happening.

I'm going to look at Lyle's suggestion...there was a reason why I
didn't just use a query, although I can't remember what that was at
this moment...it's Monday morning B.C. (that's Before Coffee), so who
knows what I was thinking.

Thank you again for your help.

Josetta

May 8 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.