Duplicating Records

[koolwaters]

I added a thread previously called "Duplicate Sub Form Data on New Record" but I did not receive any feedback with that query. I searched the Internet and found a fix on Microsoft's Help and Support Site. I tried the code displayed with the Northwind database and it worked.

I modified it to suit my database and when I click my Duplicate button, I get an error saying "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship."

To explain my scenario:
I have a database with several tables tracking training. The training information is entered from an attendance sheet after the training is completed. I have a table for the tblFacilitators, tblStaff, tblCourses, tblCourseDetails and tblEmpResults, among others.

The course information is entered on the Main Form and then there is a subform where the attendance and results for each employee is entered. The control source for the Main form is tblCourseDetails and tblEmpResults for the subform. The primary key in tblCourseDetails table is the RecordID. The PK in tblEmpResults is the RecordID and the EmployeeID.

Below is the sample code used:

Private Sub btnDuplicate_Click()
Dim dbs As DAO.Database, Rst As DAO.Recordset
Dim F As Form

Set dbs = CurrentDb
Set Rst = Me.RecordsetClone

On Error GoTo Err_btnDuplicate_Click

Me.Tag = Me![RecordID]

With Rst
.AddNew
!RecordID = Me!RecordID
!Territory = Me!Territory
!CourseSegment = Me!CourseSegment
!CourseID = Me!CourseID
!FacilitatorID = Me!FacilitatorID
!StartDate = Me!StartDate
!EndDate = Me!EndDate
!StartTime = Me!StartTime
!EndTime = Me!EndTime
!Duration = Me!Duration
!NoLongerWithBank = Me!NoLongerWithBank
!TypeManager = Me!TypeManager
!Comments = Me!Comments
.Update
.Move 0, .LastModified
End With
Me.Bookmark = Rst.Bookmark

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDuplicateCourseDetails"
DoCmd.SetWarnings True

Me![frmEmpNames].Requery

Exit_btnduplicate_Click:
Exit Sub

Err_btnDuplicate_Click:
MsgBox Error$
Resume Exit_btnduplicate_Click:
End Sub

I appreciate any help as this has been plaguing me for quite sometime and I cannot seem to find a fix.

 

[koolwaters]

I have FINALLY gotten it solved using the code below. I am on top of the world.

For the "Duplicate Values Error", that I was getting, I used the formula suggested by Bob Larson in a thread called "How do I make a text box increase to the next number in line?"

Private Sub btnDuplicate_Click()
Dim dbs As DAO.Database, Rst As DAO.Recordset
Dim F As Form

Set dbs = CurrentDb
Set Rst = Me.RecordsetClone

On Error GoTo Err_btnDuplicate_Click

Me.Tag = Me![RecordID]

With Rst
.AddNew
!RecordID = Nz(DMax("[RecordID]", "tblCourseDetails"), 0) + 1
!Territory = Me!Territory
!CourseSegment = Me!CourseSegment
!CourseID = Me!CourseID
!FacilitatorID = Me!FacilitatorID
!StartDate = Me!StartDate
!EndDate = Me!EndDate
!StartTime = Me!StartTime
!EndTime = Me!EndTime
!Duration = Me!Duration
!NoLongerWithBank = Me!NoLongerWithBank
!TypeManager = Me!TypeManager
!Comments = Me!Comments
.Update
.Move 0, .LastModified
End With
Me.Bookmark = Rst.Bookmark

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDuplicateCourseDetails"
DoCmd.SetWarnings True

Me![Record Attendance].Requery

Exit_btnduplicate_Click:
Exit Sub

Err_btnDuplicate_Click:
MsgBox Error$
Resume Exit_btnduplicate_Click:
End Sub