Duplicating reocords from main form and subform receiving Runtime Error 3022

[lroberson]

I am trying to develop a command button that will duplicate the main form and
the subform information and save it to another record within the same tables.
I used Allen Browne's program and modified it to fit my tables. At first it
would copy the main form and not the subform data so I made some changes and
now I am getting the Runtime error 3022. I have worked on this off and on
for about 2 weeks. Any help on getting this to run will be greatly
appreciated. Below is my code:

Private Sub Command78_Click()

'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.

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

'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 Me.RecordsetClone
.AddNew
![Group Name] = Me.[Group Name]
![effective date] = Me.[effective date]
![AEID] = Me.[AEID]
![TypeID] = Me.[TypeID]
![Market SegmentID] = Me.[Market SegmentID]
![UWID] = Me.[UWID]
![#subs] = Me.[#subs]
![Assigned] = Me.[Assigned]
'![SAID] = Me.SAID
![EZApps] = Me.EZApps
![expected due date] = Me.[expected due date]
![Comments] = Me.[Comments]
'![Completed] = Me.Completed
![Rush] = Me.Rush
![date created] = Me.[date created]
![created by] = Me.[created by]
![date updated] = Me.[date updated]
![updated by] = Me.[updated by]

.Update

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

'Duplicate the related records: append query.
If Me.[F_Entry Status Subform].Form.RecordsetClone.RecordCount >
0 Then
strSql = "INSERT INTO [T_Entry Status Subform] (
[EntryRecordID], [Created By], [Date], [Hold StatusId], [Sent to UW], [Date
Sent to UW], [Comments], [RecordID], [Date Updated], [Updated By] ) " &
"Select " & lngID & " As EntryRecordID, [Created By], [Date], [Hold StatusID],
[Sent to UW], [Date Sent to UW], [Comments], [RecordID], [Date Updated],
[Updated By]" & "FROM [T_Entry status subform] WHERE EntryrecordID = " & Me.
RecordID & ";"
Debug.Print strSql

DBEngine(0)(0).Execute strSql, dbFailOnError

Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified


End With
End If
Set db = Nothing

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler



End Sub

 

[lroberson]

I got it to work. Thanks

 

[Jeff Boyce]

You are describing a "how", as in how you want to accomplish something
(i.e., by apparently duplicating data in a table).

In a well-normalized relational database, you wouldn't need to duplicate
data in a table.

If you'll describe a bit more specifically "what" you would be able to do if
you had this duplicated data, folks here may be able to offer more specific
suggestions, including approaches that could give you better use of Access'
relationally-oriented features/functions.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I am trying to develop a command button that will duplicate the main form
and
the subform information and save it to another record within the same
tables.
I used Allen Browne's program and modified it to fit my tables. At first
it
would copy the main form and not the subform data so I made some changes
and
now I am getting the Runtime error 3022. I have worked on this off and on
for about 2 weeks. Any help on getting this to run will be greatly
appreciated. Below is my code:

Private Sub Command78_Click()

'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.

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

'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 Me.RecordsetClone
.AddNew
![Group Name] = Me.[Group Name]
![effective date] = Me.[effective date]
![AEID] = Me.[AEID]
![TypeID] = Me.[TypeID]
![Market SegmentID] = Me.[Market SegmentID]
![UWID] = Me.[UWID]
![#subs] = Me.[#subs]
![Assigned] = Me.[Assigned]
'![SAID] = Me.SAID
![EZApps] = Me.EZApps
![expected due date] = Me.[expected due date]
![Comments] = Me.[Comments]
'![Completed] = Me.Completed
![Rush] = Me.Rush
![date created] = Me.[date created]
![created by] = Me.[created by]
![date updated] = Me.[date updated]
![updated by] = Me.[updated by]

.Update

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

'Duplicate the related records: append query.
If Me.[F_Entry Status Subform].Form.RecordsetClone.RecordCount0 Then
strSql = "INSERT INTO [T_Entry Status Subform] (
[EntryRecordID], [Created By], [Date], [Hold StatusId], [Sent to UW],
[Date
Sent to UW], [Comments], [RecordID], [Date Updated], [Updated By] ) " &
"Select " & lngID & " As EntryRecordID, [Created By], [Date], [Hold
StatusID],
[Sent to UW], [Date Sent to UW], [Comments], [RecordID], [Date Updated],
[Updated By]" & "FROM [T_Entry status subform] WHERE EntryrecordID = " &
Me.
RecordID & ";"
Debug.Print strSql

DBEngine(0)(0).Execute strSql, dbFailOnError

Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified


End With
End If
Set db = Nothing

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdDupe_Click"
Resume Exit_Handler



End Sub