please help - think this code needs a slight tweak

[capdownlondon]

Im using the following code to duplicate a record varCnt(retrieved from a combo box on the form) many times, and it only duplicates the record with the fields present on the form for that record.
The duplicate fields are [childs name] and [target 1] [target 2] (upto target 5) and then it duplicates the [weekly index] but adds 1 to each duplicate for this field.
But if the targets are updated again it keeps the old duplicates for future weeks, causing there to be 2 sets of records for those future weeks.
So ive added a bit to the code when they are added to search for duplicates, but what it does is delete all but the last updated record.
But what i need it to do is not to delete the last updated record, but the last varCnt record, so in other words if varCnt is 3, then it deletes all but the last 3.
heres the code, i reckon this must be quite simple, but i cant work it out, ive tried playing around a bit, but im not a coder:

Private Sub Command16_Click()
On Error GoTo Err_Command16_Click
Dim i As Integer, cnt As Integer, varBk As String
Dim intCriteriaCount As Integer, MyDB As Database, MyRS As Recordset
Dim intCounter As Integer
 
varBk = Me.Bookmark
cnt = Me!Combo17
 
For i = 0 To cnt - 1
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
 
Me!Text22 = Me!Text22 + 1
 
Next
 
Exit_Command16_Click:
Me.Bookmark = varBk
 
intCriteriaCount = DCount("*", "FindDuplicatesWeeklyPoints")
 
'Are there Records meeting the Criteria?
If intCriteriaCount > 0 Then
  Set MyDB = CurrentDb()
  'Order by your Primary Key Ascending
  Set MyRS = MyDB.OpenRecordset("SELECT * FROM  FindDuplicatesWeeklyPoints ORDER BY [Weekly Index]")
  MyRS.MoveLast: MyRS.MoveFirst
    For intCounter = 1 To intCriteriaCount - 1      'All but the Last Record
      MyRS.Delete
      MyRS.MoveNext
    Next
Else
  Exit Sub
End If
 
MyRS.Close
 
Exit Sub
 
Err_Command16_Click:
MsgBox Err.Description
 
Resume Exit_Command16_Click
 
End Sub
 

thanks
Adam

[MMcCarthy]

Hi Adam,

Will this work ...

Private Sub Command16_Click()
On Error GoTo Err_Command16_Click
Dim i As Integer, cnt As Integer
Dim varBk As String
Dim intCriteriaCount As Integer
Dim MyDB As Database
Dim MyRS As Recordset
Dim intCounter As Integer
 
varBk = Me.Bookmark
cnt = Me!Combo17
 
For i = 0 To cnt - 1
   DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
   DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
   DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 
 
   'Paste Append
   Me!Text22 = Me!Text22 + 1
 
Next
 
Exit_Command16_Click:
Me.Bookmark = varBk
 
intCriteriaCount = DCount("*", "FindDuplicatesWeeklyPoints")
 
'Are there Records meeting the Criteria?
If intCriteriaCount > 0 Then
   Set MyDB = CurrentDb()
   'Order by your Primary Key Ascending
   Set MyRS = MyDB.OpenRecordset("SELECT * FROM FindDuplicatesWeeklyPoints ORDER BY [Weekly Index]")
   MyRS.MoveLast: MyRS.MoveFirst
   For intCounter = 1 To intCriteriaCount - cnt      'All but the Last Record
      MyRS.Delete
      MyRS.MoveNext
   Next
Else
   Exit Sub
End If
 
MyRS.Close
 
Exit Sub
 
Err_Command16_Click:
MsgBox Err.Description
 
Resume Exit_Command16_Click
 
End Sub