Duplicate selected record within a subform

[Guest]

What is the best way to duplicate a selected record in a subform which lists
multiple records? I created a command button and wrote code to save each
control, add a new record, then move all the saved fields into the new ones.
But this seems very cumbersome, also I will have to edit this every time I
change the subform. Is there a better way to do this? I tried using the
RecordsetClone of the subform, but it copies the first records instead of the
record selected on the subform. Any ideas from you Access guru's?

 

[strive4peace]

Delete subform record
---

'~~~~~~~~~~~~~~~~~~~~~

If Me.subform_controlname.Form.Recordset.RecordCount = 0 Then
MsgBox "You are not on a record in the subform" _
, , "Cannot delete"
Exit Sub
End If

Dim mIDfieldAs Long _
, s As String

'save record if changes have been made
If Me.subform_controlname.Form.Dirty Then
Me.subform_controlname.Form.Dirty = False
end if

If Me.subform_controlname.Form.NewRecord Then
MsgBox "Cannot delete record" _
, , "Not on a current record in subform"
Exit Sub
End If

if msgbox("Do you want to delete current subform record" _
,vbYesNo+vbDefaultButtons, "Delete record?") = vbNo then
exit sub
end if

mIDfield= Me.subform_controlname.Form.IDfield
s = "DELETE * FROM Tablename WHERE IDfield=" & mIDfield
rSql s

Me.subform_controlname.Requery
'~~~~~~~~~~~~~~~~~~~~~
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*