Unable to create duplicate records on a subform (1 Viewer)

[nika.duncan]

Hi All,
I am working with a sub-form where once a staff member enters there sub measure I would want to create a duplicate of that record. The problem I am having is that once you enter the sub-form and click the duplicate button it creates a duplicate of the record selected but overwrites the first record in the table. I want it to create a new SubMeasure Number which is the primary key and assigns the record the next available number.

Also if I try to add another record after one has been added I get runtime error "3021" - No current record. I would have to close the form and reopen for it to be able to add again.

Please if anyone can assist because I am not sure what must be done to eliminate the problem.
I have attached the code below

Private Sub cmdDuplicate_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


If Me.Dirty Then
Me.Dirty = False
End If
'On Error GoTo Err_cmdDuplicate_Click


'Tag property to be used later by the append query.
Me.Tag = Me![MeasureID]    'Links Main form to sub-form


'Add new record to the end of Recordset object


With Rst
.Edit
!SubMeasureName = Me!SubMeasureName
!SubMeasureDesp = Me!SubMeasureDesp
!SubMeasureScore = Me!SubMeasureScore
!SubMeasureWeight = Me!SubMeasureWeight
!SubMeasureTotal = Me!SubMeasureTotal






.Update


.Move 0, .LastModified


End With


Me.Bookmark = Rst.Bookmark
'Run the Duplicate Measure Detail append query which will
'selects all detail records that have the MUserLoginID stored
'in the form's Tag property and appends the back to the detail table
'with the MUserLoginID of the duplicated main form record


DoCmd.SetWarnings False
DoCmd.OpenQuery "Duplicate Measure Detail"
DoCmd.SetWarnings True


'Requery the subform to display the newly appended records.
Me.Requery






Exit_cmdDuplicate_Click:
Exit Sub


Err_cmdDuplicateClick:
MsgBox Error$
Resume Exit_cmdDuplicate_Click:


Me.SubMeasure = Nz(DMax("SubMeasure", "tblSubMeasure"), 0) + 1  'Suppose to find next available number and assign to record


End Sub

 

[pbaldy]

As answered elsewhere, you likely want .AddNew instead of .Edit.

 

[TJPoorman]

You could also use a simple "INSERT INTO SELECT" SQL statement

 

[nika.duncan]

Thanks the .AddNew works

 

[pbaldy]

No problem.