I am trying to copy a Master Record and its Subform Record into a new Master
and Subfrom Record.
But instead of the new copy of Master record PK being generated
automatically, I want to choose which other record to link it to via a combo
box. And then hit the Do Copy button which will run the code and copy the
master/sub to my chosen record (I can handle the combo box code, but can't
work out how to make the inital copy code work with example code below).
Main Form fields are:
GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)
Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)
Below is how far I got with some adapted code (from Allen Browne's website)
http://allenbrowne.com/ser-57.html
But when I run it I get the following error message:
Run-time 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...
Below is Allen's adapted code:
Private Sub btn_Do_Copy_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
!GetRound_ID = Me.GetRound_ID
!GetRoundPoint_ID = Me.GetRoundPoint_ID
!FromStreetNameID = Me.FromStreetNameID
!ToStreetNameID = ToStreetNameID
!From_PostCode = From_PostCode
!To_PostCode = To_PostCode
!Direction_From = Direction_From
!Direction_To = Direction_To
'etc for other fields.
.Update
'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !GetRound_ID
'Duplicate the related records: append query.
If Me.[frm_Getround_Detail].Form.RecordsetClone.RecordCount > 0
Then
strSql = "INSERT INTO [tbl_Getrounds] (GetRound_ID, GetRound_Detail_ID,
Run_Direction, Run_waypoint, Postcode) " & "SELECT " & lngID & " As NewID,
GetRound_Detail_ID, Run_Direction, Run_waypoint, Postcode " & _
"FROM [tbl_Getround_Detail] WHERE Me.GetRound_ID = " &
Me.GetRound_ID & ";"
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
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler
End Sub
Similar Threads
