T
Tom
I have come across a Allen Browne's function that allows one to duplicate a
record and create the proper references between parent record and child
record(s). It works great in the sample db; however, that one uses a
compound key in the "child table" vs. an autonumber. I have attempted to
modify the code to fit my 1:M relationship... it doesn't work properly
though right now.
Before I provide the current code, let me share a few details about my table
relationships:
ParentTable: "tbl01PrioritizationData"
- "TrackingNumber" (autonumber) is primary key
- "Division"; textfield
- plus other textfields (not necessary to describe)
ChildTable: "tbl01CISI"
- "CISIID" (autonumber) is primary key
- "TrackingNumber" (number) is foreign key
- "City"; textfield
- plus other textfields (not necessary to describe)
So, in [tbl01PrioritizationData], I might have 2 records like below:
1, West Coast
2, East Coast
And in [tbl01CISI], I might have 4 subordinate child records:
1, 1, "Los Angeles"
2, 1, "San Francisco"
3, 2, "New York"
4, 2, "Miami"
Now, once the "DuplicateRecord" function is executed, I want my output to
look like this:
tbl01PrioritizationData:
1, West Coast
2, East Coast
3, West Coast
tbl01CISI:
1, 1, "Los Angeles"
2, 1, "San Francisco"
3, 2, "New York"
4, 2, "Miami"
5, 3, "Los Angeles"
6, 3, "San Francisco"
Below is the function that worked fine in the sample db; however, since I'm
not using a compound key in my child table, something doesn't work out in my
application.
The error seems to lie somewhere in the "strSQL = "INSERT INTO" section as
(depending on what I'm changing) my output is the following:
1. either I get error "Too few parameters. Expected 1." -- the line
"DBEngine(0)(0).Execute strSQL, dbFailOnError" is highlighted
2. or, when removing the "TrackingNumber", I have been able to actually
duplicate the record, but the output of the TrackingNumber (foreign key) is
incorrect such as:
5, 1, "Los Angeles" (it is still linked to parent record "1" instead of "3")
6, 1, "San Francisco" (it is still linked to parent record "1" instead of
"3")
Here's the function (as I have it right now):
===========================
*************************************************************
Private Sub DuplicateRecord_Click()
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSQL As String
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
!Division = Me.Division
.Update
'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !TrackingNumber
If Me.[sfrm11CISI].Form.RecordsetClone.RecordCount > 0 Then
strSQL = "INSERT INTO [tbl01CISI] ( TrackingNumber, City) "
& _
"SELECT " & lngID & " As TrackingNumber, City " & _
"FROM [tbl01CISI] WHERE TrackingNumber = " &
Me.TrackingNumber & ";"
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
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"DuplicateRecord_Click"
Resume Exit_Handler
End Sub
*************************************************************
Any ideas how to change it so that [tbl01CISI].[TrackingNumber] obtains the
proper ID value (3 vs. 1)?
Tom
P.S. BTW, Allen... thanks for creating this wonderful sample db...
record and create the proper references between parent record and child
record(s). It works great in the sample db; however, that one uses a
compound key in the "child table" vs. an autonumber. I have attempted to
modify the code to fit my 1:M relationship... it doesn't work properly
though right now.
Before I provide the current code, let me share a few details about my table
relationships:
ParentTable: "tbl01PrioritizationData"
- "TrackingNumber" (autonumber) is primary key
- "Division"; textfield
- plus other textfields (not necessary to describe)
ChildTable: "tbl01CISI"
- "CISIID" (autonumber) is primary key
- "TrackingNumber" (number) is foreign key
- "City"; textfield
- plus other textfields (not necessary to describe)
So, in [tbl01PrioritizationData], I might have 2 records like below:
1, West Coast
2, East Coast
And in [tbl01CISI], I might have 4 subordinate child records:
1, 1, "Los Angeles"
2, 1, "San Francisco"
3, 2, "New York"
4, 2, "Miami"
Now, once the "DuplicateRecord" function is executed, I want my output to
look like this:
tbl01PrioritizationData:
1, West Coast
2, East Coast
3, West Coast
tbl01CISI:
1, 1, "Los Angeles"
2, 1, "San Francisco"
3, 2, "New York"
4, 2, "Miami"
5, 3, "Los Angeles"
6, 3, "San Francisco"
Below is the function that worked fine in the sample db; however, since I'm
not using a compound key in my child table, something doesn't work out in my
application.
The error seems to lie somewhere in the "strSQL = "INSERT INTO" section as
(depending on what I'm changing) my output is the following:
1. either I get error "Too few parameters. Expected 1." -- the line
"DBEngine(0)(0).Execute strSQL, dbFailOnError" is highlighted
2. or, when removing the "TrackingNumber", I have been able to actually
duplicate the record, but the output of the TrackingNumber (foreign key) is
incorrect such as:
5, 1, "Los Angeles" (it is still linked to parent record "1" instead of "3")
6, 1, "San Francisco" (it is still linked to parent record "1" instead of
"3")
Here's the function (as I have it right now):
===========================
*************************************************************
Private Sub DuplicateRecord_Click()
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSQL As String
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
!Division = Me.Division
.Update
'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !TrackingNumber
If Me.[sfrm11CISI].Form.RecordsetClone.RecordCount > 0 Then
strSQL = "INSERT INTO [tbl01CISI] ( TrackingNumber, City) "
& _
"SELECT " & lngID & " As TrackingNumber, City " & _
"FROM [tbl01CISI] WHERE TrackingNumber = " &
Me.TrackingNumber & ";"
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
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"DuplicateRecord_Click"
Resume Exit_Handler
End Sub
*************************************************************
Any ideas how to change it so that [tbl01CISI].[TrackingNumber] obtains the
proper ID value (3 vs. 1)?
Tom
P.S. BTW, Allen... thanks for creating this wonderful sample db...