Allen Browne's function: "Duplicate record (and related child record)" -- my modified version doesn'


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...
 
Ad

Advertisements

S

SteveS

Tom,

I made a few changes to the code. I think this should work. Watch for line wrap

'**************************************************
Private Sub DuplicateRecord_Click()

'Purpose: Duplicate the main form record and related records in the subform.
Dim strSQL As String
Dim strDivision As String
Dim lngOldID As Long '*** Primary key value of the orginial record.
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
'*** Save division
strDivision = Me.Division

'*** Save the orginial primary key value, to use in select query
lngOldID = Me.TrackingNumber

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
'!Division = Me.Division
'*** changed
!Division = strDivision

.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 NewID, City " & _
"FROM [tbl01CISI] WHERE TrackingNumber = " & lngOldID & ";"

' for debugging
'MsgBox strSQL

DBEngine(0)(0).Execute strSQL, dbFailOnError
'*** added/ comment out if not wanted
MsgBox "Subform record(s) added."

'*** requery the subform
Me.[sfrm11CISI].Requery

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
'**************************************************


HTH
--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)


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...
 
T

Tom

Thanks, Steve... this works great.

Tom


SteveS said:
Tom,

I made a few changes to the code. I think this should work. Watch for line
wrap

'**************************************************
Private Sub DuplicateRecord_Click()

'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSQL As String
Dim strDivision As String
Dim lngOldID As Long '*** Primary key value of the orginial record.
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
'*** Save division
strDivision = Me.Division

'*** Save the orginial primary key value, to use in select query
lngOldID = Me.TrackingNumber

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
'!Division = Me.Division
'*** changed
!Division = strDivision

.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 NewID, City " & _
"FROM [tbl01CISI] WHERE TrackingNumber = " & lngOldID
& ";"

' for debugging
'MsgBox strSQL

DBEngine(0)(0).Execute strSQL, dbFailOnError
'*** added/ comment out if not wanted
MsgBox "Subform record(s) added."

'*** requery the subform
Me.[sfrm11CISI].Requery

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
'**************************************************


HTH
--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)


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...
 
Ad

Advertisements

S

SteveS

Great.

--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Thanks, Steve... this works great.

Tom


Tom,

I made a few changes to the code. I think this should work. Watch for line
wrap

'**************************************************
Private Sub DuplicateRecord_Click()

'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSQL As String
Dim strDivision As String
Dim lngOldID As Long '*** Primary key value of the orginial record.
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
'*** Save division
strDivision = Me.Division

'*** Save the orginial primary key value, to use in select query
lngOldID = Me.TrackingNumber

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
'!Division = Me.Division
'*** changed
!Division = strDivision

.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 NewID, City " & _
"FROM [tbl01CISI] WHERE TrackingNumber = " & lngOldID
& ";"

' for debugging
'MsgBox strSQL

DBEngine(0)(0).Execute strSQL, dbFailOnError
'*** added/ comment out if not wanted
MsgBox "Subform record(s) added."

'*** requery the subform
Me.[sfrm11CISI].Requery

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
'**************************************************


HTH
--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)


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...
 

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

Top