|
P: 30
|
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
| |
Share this Question
| Expert 5K+
P: 8,627
|
I too seem to have trouble implementing Allen Browne's code, so I drastically changed the logic involved - sorry Allen! (LOL)! - Instead of using the Recordset exposed by the Form.RecordsetClone Property, I created an External Recordset based on the Orders Table (Ref#1).
- I used a smaller Data set for the code testing, but it can easily be expanded.
- I allowed the Primary Key in the Main Table (Orders) to Auto-Generate when adding a New Record (Ref #2), then retrieved its value by setting a Bookmark to the LastModified Property of the Recordset (Ref #3).
- Retrieved the Value of the Foreign Key for the Records to be added to the Sub-Form (Order Details), which is the same as the Primary Key in the Main Form (Ref #4).
- Populated the Sub-Form (Ref #5), Requeried the Main Form (Ref #6), then Navigated to the newly created Main/Sub-Form Record(s) (Ref #7).
- Any questions, feel free to ask.
- Dim MyDB As DAO.Database
-
Dim rstDup As DAO.Recordset
-
Dim lngNewPK As Long
-
Dim lngOldPK As Long
-
Dim strSQL As String
-
-
Set MyDB = CurrentDb
-
Set rstDup = MyDB.OpenRecordset("Orders", dbOpenDynaset) 'Ref #1
-
-
With rstDup
-
.AddNew
-
'The Primary Key [Order ID] will Auto Generate 'Ref #2
-
![Order Name] = Me![Order Name]
-
.Update
-
.Bookmark = .LastModified 'Ref #3
-
lngNewPK = ![Order ID]
-
End With
-
-
'Retrieve the Value of the Old, Original Primary Key in order
-
'to populate the New Main Record's Sub-Form
-
lngOldPK = Me![Order ID] 'Ref #4
-
-
If Me.[Order Details].Form.RecordsetClone.RecordCount > 0 Then
-
strSQL = "INSERT INTO [Order Details] ([Order ID], [Section]) " & _
-
"SELECT " & lngNewPK & " As NewID,[Section] " & _
-
"FROM [Order Details] WHERE [Order ID] = " & lngOldPK & ";"
-
CurrentDb.Execute strSQL, dbFailOnError 'Ref #5
-
Else
-
MsgBox "Main record duplicated, but there were no related records."
-
End If
-
-
rstDup.Close
-
Set rstDup = Nothing
-
-
Me.Requery 'Ref #6
-
DoCmd.GoToRecord , , acLast 'Ref #7
| | | |  Question stats - viewed: 5541
- replies: 1
- date asked: Jun 13 '09
| |
