Duplicate selected record within a subform

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Crossh - 12 Mar 2007 22:04 GMT

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?

Reply to this Message

strive4peace - 13 Mar 2007 00:51 GMT

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
*

> 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
[quoted text clipped - 3 lines]
> 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?

Reply to this Message