copy record and related records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everybody.

I have a form with a subform. I would like to assign a
command button to copy the current record on the form and
the records on the subform and create a new record with
this information.

Suggestions?
 
This example duplicates the invoice in the main form, and the line items in
the subform. It uses the RecordsetClone of the form to duplicate the main
record, so you can choose the fields selectively. For example, the new
invoice gets today's date.

It then executes an Append query statement to duplicate the related records,
and shows the new dupliate in the form.

Private Sub cmdDupe_Click()
Dim sSQL As String
Dim db As DAO.Database
Dim lngInvID As Long

Set db = DBEngine(0)(0)

If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else

'Duplicate the main record
With Me.RecordsetClone
.AddNew
!InvoiceDate = Date
!ClientID = Me.ClientID
!Ref = Me.Ref
'etc for other fields
.Update
.Bookmark = .LastModified
lngInvID = !InvoiceID

'Duplicate the related records.
If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 0 Then
sSQL = "INSERT INTO tInvoiceDetail ( InvoiceID, Item,
Amount) " & _
"SELECT " & lngInvID & " As NewInvoiceID,
tInvoiceDetail.Item, tInvoiceDetail.Amount FROM tInvoiceDetail " & _
"WHERE (tInvoiceDetail.InvoiceID = " & Me.InvoiceID &
");"
db.Execute sSQL, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records.", vbInformation, "Information"
End If

'Display the duplicate.
Me.Bookmark = .LastModified
End With
End If

Set db = Nothing
End Sub
 
hi, i think i've got a similar situation to yours happening....

i used the 'wizard' to create a 'Duplicate Record' cmdbtn which propagated
the following code:

Private Sub Duplicate_Click()
On Error GoTo Err_Duplicate_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_Duplicate_Click:
Exit Sub

Err_Duplicate_Click:
MsgBox Err.description
Resume Exit_Duplicate_Click

End Sub

one thing i've noticed is that ms a2k returns the following message claiming
"The value you entered isn't appropriate for the input mask '00/00/00'
specified for this field" which is apparently addressing one or more of the
pair of date fields i have in the table underlying the sub-form (displaying
date as medium date format).

(vba newbie that i am) what i'm interested in this form's being able to do
is as follows: i've put this button into the header field of the sub-form's.
whenever a subform listbox field i called "Continuing" is checked "Yes" this
cmdbtn is 'enabled' for the active record. clicking on the now enabled
"Duplicate" cmdbtn is supposed to duplicate the values for the active record
with the proviso that the "Continuing" listbox on the duplicate record is
supposed to have the value "No".

in the interest of getting as much details out of the way for the purposes
of this posting, there are 5 fields that comprise the composite PK of the
underlying table's:
patient_number (#), cycle (#), ae_description (txt), subtype (txt), onset
(date/time).
 
Allen, I need to do the same thing described here but my form has two
subforms. I am having difficulty with the SQL statement that duplicates the
related records. I have the portion of the code working that duplicates the
record in the parent table but have been unable to get the rest to work.
SerialNum and ConfigDate are keys.

Form names: ConfigHistory, ConfigHistorySubform1, and ConfigHistorySubform2
Table names: ConfigHistory, Ports, Instances
Field names:

ConfigHistory
SerialNum
ConfigDate
Name
Building
Room

Ports
SerialNum
ConfigDate
Port
Sysplex
AliasName
EntryPerson

Instances
SerailNum
ConfigDate
InstanceName
EntryPerson

Thanks for the great example.
 
Back
Top