Duplicate Form, Subfrom

  • Thread starter Duplicate Form, Subform
  • Start date
D

Duplicate Form, Subform

I have a problem with this. Currently I am trying Allen's code and i am not
successful.

Current Design

Table1 (Main Form)
TravelID (PK)
ApprovedBY
EntreredBy
BudgetCode
ExpenseCode

Table2 (Subform)
TripID (PK)
TripTypeID
TravellerID
TripStartDate
TripEndDate
TripStatus

Can you help with this?


'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.


'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!EnteredBy = Me.EnteredBy
!ApprovedBy = Me.ApprovedBy
!BudgetCode = Me.BudgetCode
!ExpenseCode = Me.ExpenseCode
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = Me.TravelAuthorizationID


'Duplicate the related records: append query.
If Me.[TravelAuthorizations
Subform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [Trips] ( TravelID, TripTypeID,
TravellerID, TripStartDate, TripEndDate, TripStatus ) " & _
"SELECT " & lngID & " As NewID, TripID, TripTypeID,
TravellerID, TripStartDate, TripEndate, TripStatus " & _
"FROM [Trips] WHERE TravelID = " & Me.TravelID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

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

Dirk Goldgar

"Duplicate Form, Subform" <Duplicate Form,
(e-mail address removed)> wrote in message
I have a problem with this. Currently I am trying Allen's code and i am
not
successful.

Current Design

Table1 (Main Form)
TravelID (PK)
ApprovedBY
EntreredBy
BudgetCode
ExpenseCode

Table2 (Subform)
TripID (PK)
TripTypeID
TravellerID
TripStartDate
TripEndDate
TripStatus

Can you help with this?


'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.


'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!EnteredBy = Me.EnteredBy
!ApprovedBy = Me.ApprovedBy
!BudgetCode = Me.BudgetCode
!ExpenseCode = Me.ExpenseCode
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = Me.TravelAuthorizationID


'Duplicate the related records: append query.
If Me.[TravelAuthorizations
Subform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [Trips] ( TravelID, TripTypeID,
TravellerID, TripStartDate, TripEndDate, TripStatus ) " & _
"SELECT " & lngID & " As NewID, TripID, TripTypeID,
TravellerID, TripStartDate, TripEndate, TripStatus " & _
"FROM [Trips] WHERE TravelID = " & Me.TravelID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If
Click to expand...


Without my staring at the code too hard, it looks reasonable to me. What
sort of problem are you having?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Duplicate Subform and SubSubForm Records but not Main 1
Duplicating reocords from main form and subform receiving Runtime Error 3022 2
Duplicate record in Form and Subform 0
Duplicate Data in Form, its Subform and SubSubForm 5
Duplicate record code from Allen Browne 4
Allen Browne's Duplicate Record Code 7
Duplicate record with child fields 1
Duplicate Form & Subform for record 0

Top