I am using Access 2016. I am writing a music collection database with two primary tables, tblTitles and tblSongs. The main form frmTitles is used to enter the information pertaining to the title and that form has a continuous subform subfrmSongs that all of the song information is contained in. Every now and again I run across a duplicate title that I need to enter into the database. Rather than rekeying all of the information again I thought I would create a command button on the form btnCopyRecord that would;
1) copy all of the data from the frmTitles (AutoNum Key field TitleID) and subfrmSongs (AutoNum Key field SongID, create a new record and copy the information into the new record with a new unique key for TitleID and SongID. 2) Save the duplicate record with the new unique keys and open that record so that the one or two fields that need to be edited can be edited without having to rekey the whole record.
While searching on the internet for a solution I came across the code listed below. The problem is when it gets to the line .Update I get an error message Runtime error 3022. The changes in the table were not successful as they would create duplicate values in the index, primary key or the index. Change the data in the in the fields that contain the duplicate data, remove the index, or redefine the index to permit duplicated entries and try again.
What am I doing wrong or is there another way to skin this cat?
'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
'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
'Duplicate the main record: add to form's clone.
!TitleID = Me.TitleID
!Status = Me.Status
!MediaType = Me.MediaType
!RollType = Me.RollType
!RollOrgin = Me.RollOrigin
!RecutSource = Me.RecutSource
!Title = Me.Title
!TitleNumber = Me.TitleNumber
!RecordingCompany = Me.RecordingCompany
!GroupPerformer = Me.GroupPerformer
!Genre = Me.Genre
!PurchasedFrom = Me.PurchasedFrom
!PurchaseDate = Me.PurchaseDate
!PurchasePrice = Me.PurchasePrice
!CurrentMarketValue = Me.CurrentMarketValue
!Condition = Me.Condition
!RollRating = Me.RollRating
!DateDigitalFileRecorded = Me.DateDigitalFileRecorded
!TitleNotes = Me.TitleNotes
!SoldDate = Me.SoldDate
!SoldPrice = Me.SoldPrice
!SoldTo = Me.SoldTo
!GainLoss = Me.GainLoss
'etc for other fields.
'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
lngID = !TitleID
'Duplicate the related records: append query.
If Me.[subfrmSongs].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [subfrmSongs] ( SongID, MediaTitle, SideDiscNumber, SongPosition, SongTitle, SongPlayedBy1, SongPlayedBy2, SongPlayedBy3, SongType, SongLength ) " & _
"SELECT " & lngID & " As NewID, SongID, MediaTitle, SideDiscNumber, SongPosition, SongTitle, SongPlayedBy1, SongPlayedBy2, SongPlayedBy3, SongType, SongLength " & _
"FROM [subfrmSongs] WHERE TitleID = " & Me.TitleID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
MsgBox "Main record duplicated, but there were no related records."
'Display the new duplicate.
Me.Bookmark = .LastModified
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"