How to duplicate subform records ? Help!! NEwbie

[CynthiaFL717]

Hello,
I am a first timer in this forum and wanted to get help on how to duplicate records in SubForm, i have created a form (transmittal) with a subform that contains all items listed:

Master Field : Transmittal No
Child Fields: IDTransmittalNumber

there is a button that allows me to duplicate form , however when i change the Transmittal no in the main form it automatically deletes all data in subform since they are both linked by that field.. Please help?

 

[pbaldy]

Is this what you're trying to do?

http://allenbrowne.com/ser-57.html

 

[CynthiaFL717]

I did try using Allen's code, but I get error 3022 - the changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

 

[CynthiaFL717]

This is the code i'm using:

'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 any 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
!TransGCID = Me.TransGCID (<---ID in main form - Link Master field - autonumber)
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
lngID = !IDTransmittalNumber << --- Link Child Field in subform

'Duplicate the related records: append query.
If Me.[Subform Transmittal items].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [Transmittal to GC] ( IDTransmittalNumber, Item No/ Mix Code, Description, S/D Date, Proposed Placement ) " & _
"SELECT " & lngID & " As NewID, ProductID, Quantity, UnitPrice, Discount " & _
"FROM [Order Details] WHERE Trasmittal No = " & Me.Transmittal_No & ";"
Else
MsgBox "Main record duplicated, but there were no related records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler
End Sub

 

[pbaldy]

I'd expect an error due to the space in the field name in the WHERE clause. Can you post the db here?

 

[CynthiaFL717]

it is over 3.5mb it won't let me upload it, can I send it by email?

 

[pbaldy]

Does this work?

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[CynthiaFL717]

Here it is

 

[pbaldy]

This SQL:

                strSql = "INSERT INTO [Transmittal to GC] ( IDTransmittalNumber, Item No/ Mix Code, Description, S/D Date, Proposed Placement ) " & _
                    "SELECT " & lngID & " As NewID, ProductID, Quantity, UnitPrice, Discount " & _
                    "FROM [Order Details] WHERE Trasmittal No = " & Me.Transmittal_No & ";"

Does not appear to be inserting into the correct table, and the FROM table doesn't even exist.

 

[andy1968]

I modified Allen Browns code to allow the user to select an old parent record from a combo box, then click a button to enter the data into a child form.

Private Sub cmdCopyCriteria_Click()
'On Error GoTo Err_Handler
    'Purpose:   Duplicate the criteria from a past submittal (in combobox ExistingCriteria) into the form for the current submittal
Dim strSql As String    'SQL statement.
Dim lngID As Long       'Primary key value of the new record.
lngID = Me.SerialNumber
            If Len(Me.ExistingCriteria) > 0 Then
                strSql = "INSERT INTO [tblSubmittalCriteria] ( Job, SerialNumber, Spec, Criteria ) " & _
                    "SELECT Job," & lngID & " As SerialNumber, Spec, Criteria " & _
                    "FROM [tblSubmittalCriteria] WHERE SerialNumber = " & Me.ExistingCriteria & ";"
                DBEngine(0)(0).Execute strSql, dbFailOnError
            Else
                MsgBox "No Existing Criteria Selected."
            End If
Me.frmSubmittalCriteriaNew.Requery
Exit_Handler:
    Exit Sub

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