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?
Expand|Select|Wrap|Line Numbers
- '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