Duplicate Form, Subfrom

  • Thread starter Thread starter Duplicate Form, Subform
  • Start date 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
 
"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?
 
Back
Top