About the background...
My table has 3 keys to make a recordset unique...
KPIProjID, KPIPMID and ReprotDtID (all numeric)
My form has a Sub to duplicate the record so the used does not have to retype their monthly inputs. They can just duplicate the record and change the date to the current month and make any changes needed. A bit of a time saver for them since their data is repetative.
However the code below, gives the error 3022 at the .update command.
Private Sub btnDuplicate_Click()
Dim dbs As DAO.Database, Rst As DAO.Recordset
Dim F As Form
' Return Database variable pointing to current database.
Set dbs = CurrentDb
Set Rst = Me.RecordsetClone
On Error GoTo Err_btnDuplicate_Click
DoCmd.SetWarnings False
' Tag property to be used later by the append query.
Me.Tag = Me![CustImpactID]
' Add new record to end of Recordset object.
With Rst
.AddNew
!KPIProjID = Me.KPIProjID
!KPIPMID = Me.KPIPMID
!ReportDtID = Me.ReportDtID
!Scope = Me.Scope
!Invoicing = Me.Invoicing
!SWDeliv = Me.SWDeliv
!HWDeliv = Me.HWDeliv
!Payments = Me.Payments
!MoMtgHeld = Me.MoMtgHeld
!Outage = Me.Outage
!OutagePlan = Me.OutagePlan
!ExWorks = Me.ExWorks
.Update ' Save changes.
.Move 0, .LastModified
End With
Me.Bookmark = Rst.Bookmark
' Run the Duplicate Details append query which selects all
' detail records that have the CustImpactID stored in the form's
' Tag property and appends them back to the detail table with
' the CustImpactID of the duplicated main form record.
DoCmd.SetWarnings False
DoCmd.OpenQuery "q2CustImpactsDupe"
DoCmd.SetWarnings True
Me.AllowEdits = True
Forms!f2CustImpactsEdit.Form!f2CustImpactsEditDetails.Form.AllowEdits = True
retvalue = MsgBox("Reminder!! Change the Report Date and other data before clicking the Save button.", vbOKOnly)
'Requery the subform to display the newly appended records.
Me![f2CustImpactsEditDetails].Requery
Exit_btnduplicate_Click:
Exit Sub
Err_btnDuplicate_Click:
MsgBox Error$
Resume Exit_btnduplicate_Click:
End Sub
I get a Runtime 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.
If I OK ther error and change the date. It just overwrites the current record instead of duplicating and changing the ReportDtID.
How can I make this work? Thanks!!
My table has 3 keys to make a recordset unique...
KPIProjID, KPIPMID and ReprotDtID (all numeric)
My form has a Sub to duplicate the record so the used does not have to retype their monthly inputs. They can just duplicate the record and change the date to the current month and make any changes needed. A bit of a time saver for them since their data is repetative.
However the code below, gives the error 3022 at the .update command.
Private Sub btnDuplicate_Click()
Dim dbs As DAO.Database, Rst As DAO.Recordset
Dim F As Form
' Return Database variable pointing to current database.
Set dbs = CurrentDb
Set Rst = Me.RecordsetClone
On Error GoTo Err_btnDuplicate_Click
DoCmd.SetWarnings False
' Tag property to be used later by the append query.
Me.Tag = Me![CustImpactID]
' Add new record to end of Recordset object.
With Rst
.AddNew
!KPIProjID = Me.KPIProjID
!KPIPMID = Me.KPIPMID
!ReportDtID = Me.ReportDtID
!Scope = Me.Scope
!Invoicing = Me.Invoicing
!SWDeliv = Me.SWDeliv
!HWDeliv = Me.HWDeliv
!Payments = Me.Payments
!MoMtgHeld = Me.MoMtgHeld
!Outage = Me.Outage
!OutagePlan = Me.OutagePlan
!ExWorks = Me.ExWorks
.Update ' Save changes.
.Move 0, .LastModified
End With
Me.Bookmark = Rst.Bookmark
' Run the Duplicate Details append query which selects all
' detail records that have the CustImpactID stored in the form's
' Tag property and appends them back to the detail table with
' the CustImpactID of the duplicated main form record.
DoCmd.SetWarnings False
DoCmd.OpenQuery "q2CustImpactsDupe"
DoCmd.SetWarnings True
Me.AllowEdits = True
Forms!f2CustImpactsEdit.Form!f2CustImpactsEditDetails.Form.AllowEdits = True
retvalue = MsgBox("Reminder!! Change the Report Date and other data before clicking the Save button.", vbOKOnly)
'Requery the subform to display the newly appended records.
Me![f2CustImpactsEditDetails].Requery
Exit_btnduplicate_Click:
Exit Sub
Err_btnDuplicate_Click:
MsgBox Error$
Resume Exit_btnduplicate_Click:
End Sub
I get a Runtime 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.
If I OK ther error and change the date. It just overwrites the current record instead of duplicating and changing the ReportDtID.
How can I make this work? Thanks!!
