Copy Master and Sub record to an exisiting record with specified P

  • Thread starter Thread starter efandango
  • Start date Start date
E

efandango

I am trying to copy a Master Record and its Subform Record into a new Master
and Subfrom Record.

But instead of the new copy of Master record PK being generated
automatically, I want to choose which other record to link it to via a combo
box. And then hit the Do Copy button which will run the code and copy the
master/sub to my chosen record (I can handle the combo box code, but can't
work out how to make the inital copy code work with example code below).


Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)




Below is how far I got with some adapted code (from Allen Browne's website)
http://allenbrowne.com/ser-57.html
But when I run it I get the following error message:

Run-time error '3022'
The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key or relationship...


Below is Allen's adapted code:

Private Sub btn_Do_Copy_Click()
'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
!GetRound_ID = Me.GetRound_ID
!GetRoundPoint_ID = Me.GetRoundPoint_ID
!FromStreetNameID = Me.FromStreetNameID
!ToStreetNameID = ToStreetNameID
!From_PostCode = From_PostCode
!To_PostCode = To_PostCode
!Direction_From = Direction_From
!Direction_To = Direction_To

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !GetRound_ID

'Duplicate the related records: append query.
If Me.[frm_Getround_Detail].Form.RecordsetClone.RecordCount > 0
Then
strSql = "INSERT INTO [tbl_Getrounds] (GetRound_ID, GetRound_Detail_ID,
Run_Direction, Run_waypoint, Postcode) " & "SELECT " & lngID & " As NewID,
GetRound_Detail_ID, Run_Direction, Run_waypoint, Postcode " & _
"FROM [tbl_Getround_Detail] WHERE Me.GetRound_ID = " &
Me.GetRound_ID & ";"
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, , "cmdDupe_Click"
Resume Exit_Handler
End Sub
 
Hi Eric

I'd just like to confirm that I understand you correctly: You want to copy
all the existing many-side records related to one-side record A and relate
the copies to an already-existing one-side record B. Is that correct?

This can be done with a single SQL statement, similar to the one you already
have in your code:

strSql = "INSERT INTO [tbl_Getrounds] " _
& (GetRound_ID, GetRound_Detail_ID, " _
& "Run_Direction, Run_waypoint, Postcode) SELECT " _
& lngNewID & " As NewID, GetRound_Detail_ID, Run_Direction, " _
& "Run_waypoint, Postcode FROM [tbl_Getround_Detail] " _
& "WHERE Me.GetRound_ID = " & lngOldID & ";"
CurrentDb.Execute strSql, dbFailOnError

Here, lngNewID is the ID of record B, which you wish to select from a combo
box, and lngOldID is the ID of record A, which is presumably the current
record on your form.

By the way, the reason you are getting error 3022 when trying to create a
*new* record is this line of code:
!GetRound_ID = Me.GetRound_ID

You are setting the primary key of the new record you are adding to be the
same as the record you are copying. If GetRound_ID is an autonumber, then
just delete that line. Otherwise, generate a new, unique value for
GetRound_ID and assign that to !GetRound_ID.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


efandango said:
I am trying to copy a Master Record and its Subform Record into a new
Master
and Subfrom Record.

But instead of the new copy of Master record PK being generated
automatically, I want to choose which other record to link it to via a
combo
box. And then hit the Do Copy button which will run the code and copy the
master/sub to my chosen record (I can handle the combo box code, but can't
work out how to make the inital copy code work with example code below).


Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)




Below is how far I got with some adapted code (from Allen Browne's
website)
http://allenbrowne.com/ser-57.html
But when I run it I get the following error message:

Run-time error '3022'
The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key or relationship...


Below is Allen's adapted code:

Private Sub btn_Do_Copy_Click()
'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
!GetRound_ID = Me.GetRound_ID
!GetRoundPoint_ID = Me.GetRoundPoint_ID
!FromStreetNameID = Me.FromStreetNameID
!ToStreetNameID = ToStreetNameID
!From_PostCode = From_PostCode
!To_PostCode = To_PostCode
!Direction_From = Direction_From
!Direction_To = Direction_To

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !GetRound_ID

'Duplicate the related records: append query.
If Me.[frm_Getround_Detail].Form.RecordsetClone.RecordCount > 0
Then
strSql = "INSERT INTO [tbl_Getrounds] (GetRound_ID, GetRound_Detail_ID,
Run_Direction, Run_waypoint, Postcode) " & "SELECT " & lngID & " As NewID,
GetRound_Detail_ID, Run_Direction, Run_waypoint, Postcode " & _
"FROM [tbl_Getround_Detail] WHERE Me.GetRound_ID = " &
Me.GetRound_ID & ";"
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, ,
"cmdDupe_Click"
Resume Exit_Handler
End Sub
Click to expand...
 
Hi Eric

I'd just like to confirm that I understand you correctly: You want to copy
all the existing many-side records related to one-side record A and relate
the copies to an already-existing one-side record B. Is that correct?

This can be done with a single SQL statement, similar to the one you already
have in your code:

strSql = "INSERT INTO [tbl_Getrounds] " _
& (GetRound_ID, GetRound_Detail_ID, " _
& "Run_Direction, Run_waypoint, Postcode) SELECT " _
& lngNewID & " As NewID, GetRound_Detail_ID, Run_Direction, " _
& "Run_waypoint, Postcode FROM [tbl_Getround_Detail] " _
& "WHERE Me.GetRound_ID = " & lngOldID & ";"
CurrentDb.Execute strSql, dbFailOnError

Here, lngNewID is the ID of record B, which you wish to select from a combo
box, and lngOldID is the ID of record A, which is presumably the current
record on your form.

By the way, the reason you are getting error 3022 when trying to create a
*new* record is this line of code:
!GetRound_ID = Me.GetRound_ID

You are setting the primary key of the new record you are adding to be the
same as the record you are copying. If GetRound_ID is an autonumber, then
just delete that line. Otherwise, generate a new, unique value for
GetRound_ID and assign that to !GetRound_ID.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


efandango said:
I am trying to copy a Master Record and its Subform Record into a new
Master
and Subfrom Record.

But instead of the new copy of Master record PK being generated
automatically, I want to choose which other record to link it to via a
combo
box. And then hit the Do Copy button which will run the code and copy the
master/sub to my chosen record (I can handle the combo box code, but can't
work out how to make the inital copy code work with example code below).


Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)




Below is how far I got with some adapted code (from Allen Browne's
website)
http://allenbrowne.com/ser-57.html
But when I run it I get the following error message:

Run-time error '3022'
The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key or relationship...


Below is Allen's adapted code:

Private Sub btn_Do_Copy_Click()
'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
!GetRound_ID = Me.GetRound_ID
!GetRoundPoint_ID = Me.GetRoundPoint_ID
!FromStreetNameID = Me.FromStreetNameID
!ToStreetNameID = ToStreetNameID
!From_PostCode = From_PostCode
!To_PostCode = To_PostCode
!Direction_From = Direction_From
!Direction_To = Direction_To

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !GetRound_ID

'Duplicate the related records: append query.
If Me.[frm_Getround_Detail].Form.RecordsetClone.RecordCount > 0
Then
strSql = "INSERT INTO [tbl_Getrounds] (GetRound_ID, GetRound_Detail_ID,
Run_Direction, Run_waypoint, Postcode) " & "SELECT " & lngID & " As NewID,
GetRound_Detail_ID, Run_Direction, Run_waypoint, Postcode " & _
"FROM [tbl_Getround_Detail] WHERE Me.GetRound_ID = " &
Me.GetRound_ID & ";"
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, ,
"cmdDupe_Click"
Resume Exit_Handler
End Sub
Click to expand...
 
Hello Graham,

Thanks for replying.

Yes, I want to copy all the existing many-side records related to one-side
record A and relate the copies to an already-existing one-side record B.

You also correctly say: Here, lngNewID is the ID of record B, which you wish
to select from a combo box, and lngOldID is the ID of record A, which is
presumably the current record on your form.

how do I get the references for lngNewID and lngOldID? Do i use something
like this:


Record B

your version: SELECT " & lngNewID

my version: SELECT " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID]




Record A

Your version: WHERE Me.GetRound_ID = " & lngOldID

My version: WHERE Me.GetRound_ID = " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![GetRound_ID]


It's late here (blighty), and I have been on this for literally hours... so
I will give this a try and come back to you with a response, will you get an
auto-reply to this?, or what is the best time to catch you?

regards


Eric



Graham Mandeno said:
Hi Eric

I'd just like to confirm that I understand you correctly: You want to copy
all the existing many-side records related to one-side record A and relate
the copies to an already-existing one-side record B. Is that correct?

This can be done with a single SQL statement, similar to the one you already
have in your code:

strSql = "INSERT INTO [tbl_Getrounds] " _
& (GetRound_ID, GetRound_Detail_ID, " _
& "Run_Direction, Run_waypoint, Postcode) SELECT " _
& lngNewID & " As NewID, GetRound_Detail_ID, Run_Direction, " _
& "Run_waypoint, Postcode FROM [tbl_Getround_Detail] " _
& "WHERE Me.GetRound_ID = " & lngOldID & ";"
CurrentDb.Execute strSql, dbFailOnError

Here, lngNewID is the ID of record B, which you wish to select from a combo
box, and lngOldID is the ID of record A, which is presumably the current
record on your form.

By the way, the reason you are getting error 3022 when trying to create a
*new* record is this line of code:
!GetRound_ID = Me.GetRound_ID

You are setting the primary key of the new record you are adding to be the
same as the record you are copying. If GetRound_ID is an autonumber, then
just delete that line. Otherwise, generate a new, unique value for
GetRound_ID and assign that to !GetRound_ID.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


efandango said:
I am trying to copy a Master Record and its Subform Record into a new
Master
and Subfrom Record.

But instead of the new copy of Master record PK being generated
automatically, I want to choose which other record to link it to via a
combo
box. And then hit the Do Copy button which will run the code and copy the
master/sub to my chosen record (I can handle the combo box code, but can't
work out how to make the inital copy code work with example code below).


Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)




Below is how far I got with some adapted code (from Allen Browne's
website)
http://allenbrowne.com/ser-57.html
But when I run it I get the following error message:

Run-time error '3022'
The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key or relationship...


Below is Allen's adapted code:

Private Sub btn_Do_Copy_Click()
'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
!GetRound_ID = Me.GetRound_ID
!GetRoundPoint_ID = Me.GetRoundPoint_ID
!FromStreetNameID = Me.FromStreetNameID
!ToStreetNameID = ToStreetNameID
!From_PostCode = From_PostCode
!To_PostCode = To_PostCode
!Direction_From = Direction_From
!Direction_To = Direction_To

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !GetRound_ID

'Duplicate the related records: append query.
If Me.[frm_Getround_Detail].Form.RecordsetClone.RecordCount > 0
Then
strSql = "INSERT INTO [tbl_Getrounds] (GetRound_ID, GetRound_Detail_ID,
Run_Direction, Run_waypoint, Postcode) " & "SELECT " & lngID & " As NewID,
GetRound_Detail_ID, Run_Direction, Run_waypoint, Postcode " & _
"FROM [tbl_Getround_Detail] WHERE Me.GetRound_ID = " &
Me.GetRound_ID & ";"
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, ,
"cmdDupe_Click"
Resume Exit_Handler
End Sub
Click to expand...
Click to expand...
 
Hello Graham,

Thanks for replying.

Yes, I want to copy all the existing many-side records related to one-side
record A and relate the copies to an already-existing one-side record B.

You also correctly say: Here, lngNewID is the ID of record B, which you wish
to select from a combo box, and lngOldID is the ID of record A, which is
presumably the current record on your form.

how do I get the references for lngNewID and lngOldID? Do i use something
like this:


Record B

your version: SELECT " & lngNewID

my version: SELECT " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID]




Record A

Your version: WHERE Me.GetRound_ID = " & lngOldID

My version: WHERE Me.GetRound_ID = " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![GetRound_ID]


It's late here (blighty), and I have been on this for literally hours... so
I will give this a try and come back to you with a response, will you get an
auto-reply to this?, or what is the best time to catch you?

regards


Eric



Graham Mandeno said:
Hi Eric

I'd just like to confirm that I understand you correctly: You want to copy
all the existing many-side records related to one-side record A and relate
the copies to an already-existing one-side record B. Is that correct?

This can be done with a single SQL statement, similar to the one you already
have in your code:

strSql = "INSERT INTO [tbl_Getrounds] " _
& (GetRound_ID, GetRound_Detail_ID, " _
& "Run_Direction, Run_waypoint, Postcode) SELECT " _
& lngNewID & " As NewID, GetRound_Detail_ID, Run_Direction, " _
& "Run_waypoint, Postcode FROM [tbl_Getround_Detail] " _
& "WHERE Me.GetRound_ID = " & lngOldID & ";"
CurrentDb.Execute strSql, dbFailOnError

Here, lngNewID is the ID of record B, which you wish to select from a combo
box, and lngOldID is the ID of record A, which is presumably the current
record on your form.

By the way, the reason you are getting error 3022 when trying to create a
*new* record is this line of code:
!GetRound_ID = Me.GetRound_ID

You are setting the primary key of the new record you are adding to be the
same as the record you are copying. If GetRound_ID is an autonumber, then
just delete that line. Otherwise, generate a new, unique value for
GetRound_ID and assign that to !GetRound_ID.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


efandango said:
I am trying to copy a Master Record and its Subform Record into a new
Master
and Subfrom Record.

But instead of the new copy of Master record PK being generated
automatically, I want to choose which other record to link it to via a
combo
box. And then hit the Do Copy button which will run the code and copy the
master/sub to my chosen record (I can handle the combo box code, but can't
work out how to make the inital copy code work with example code below).


Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)




Below is how far I got with some adapted code (from Allen Browne's
website)
http://allenbrowne.com/ser-57.html
But when I run it I get the following error message:

Run-time error '3022'
The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key or relationship...


Below is Allen's adapted code:

Private Sub btn_Do_Copy_Click()
'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
!GetRound_ID = Me.GetRound_ID
!GetRoundPoint_ID = Me.GetRoundPoint_ID
!FromStreetNameID = Me.FromStreetNameID
!ToStreetNameID = ToStreetNameID
!From_PostCode = From_PostCode
!To_PostCode = To_PostCode
!Direction_From = Direction_From
!Direction_To = Direction_To

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !GetRound_ID

'Duplicate the related records: append query.
If Me.[frm_Getround_Detail].Form.RecordsetClone.RecordCount > 0
Then
strSql = "INSERT INTO [tbl_Getrounds] (GetRound_ID, GetRound_Detail_ID,
Run_Direction, Run_waypoint, Postcode) " & "SELECT " & lngID & " As NewID,
GetRound_Detail_ID, Run_Direction, Run_waypoint, Postcode " & _
"FROM [tbl_Getround_Detail] WHERE Me.GetRound_ID = " &
Me.GetRound_ID & ";"
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, ,
"cmdDupe_Click"
Resume Exit_Handler
End Sub
Click to expand...
Click to expand...
 
Hi Eric

I guess you are in daylight saving time now, so it must be nearly 1am!

Assuming your code is in the class module attached to the form in question
(frm_GetRounds), all you need is the following substitutions:

lngOldID -> Me.GetRound_ID
lngNewID -> Me.cbo_copy_to_new_point_ID

And I didn't notice another error in your code: SQL doesn't understand "Me"
so you must remove the "Me" after the "WHERE":

& "WHERE GetRound_ID = " & Me.GetRound_ID & ";"

--
Good Luck and Good Night :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


efandango said:
Hello Graham,

Thanks for replying.

Yes, I want to copy all the existing many-side records related to one-side
record A and relate the copies to an already-existing one-side record B.

You also correctly say: Here, lngNewID is the ID of record B, which you
wish
to select from a combo box, and lngOldID is the ID of record A, which is
presumably the current record on your form.

how do I get the references for lngNewID and lngOldID? Do i use something
like this:


Record B

your version: SELECT " & lngNewID

my version: SELECT " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID]




Record A

Your version: WHERE Me.GetRound_ID = " & lngOldID

My version: WHERE Me.GetRound_ID = " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![GetRound_ID]


It's late here (blighty), and I have been on this for literally hours...
so
I will give this a try and come back to you with a response, will you get
an
auto-reply to this?, or what is the best time to catch you?

regards


Eric



Graham Mandeno said:
Hi Eric

I'd just like to confirm that I understand you correctly: You want to
copy
all the existing many-side records related to one-side record A and
relate
the copies to an already-existing one-side record B. Is that correct?

This can be done with a single SQL statement, similar to the one you
already
have in your code:

strSql = "INSERT INTO [tbl_Getrounds] " _
& (GetRound_ID, GetRound_Detail_ID, " _
& "Run_Direction, Run_waypoint, Postcode) SELECT " _
& lngNewID & " As NewID, GetRound_Detail_ID, Run_Direction, " _
& "Run_waypoint, Postcode FROM [tbl_Getround_Detail] " _
& "WHERE Me.GetRound_ID = " & lngOldID & ";"
CurrentDb.Execute strSql, dbFailOnError

Here, lngNewID is the ID of record B, which you wish to select from a
combo
box, and lngOldID is the ID of record A, which is presumably the current
record on your form.

By the way, the reason you are getting error 3022 when trying to create a
*new* record is this line of code:
!GetRound_ID = Me.GetRound_ID

You are setting the primary key of the new record you are adding to be
the
same as the record you are copying. If GetRound_ID is an autonumber,
then
just delete that line. Otherwise, generate a new, unique value for
GetRound_ID and assign that to !GetRound_ID.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


efandango said:
I am trying to copy a Master Record and its Subform Record into a new
Master
and Subfrom Record.

But instead of the new copy of Master record PK being generated
automatically, I want to choose which other record to link it to via a
combo
box. And then hit the Do Copy button which will run the code and copy
the
master/sub to my chosen record (I can handle the combo box code, but
can't
work out how to make the inital copy code work with example code
below).


Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)




Below is how far I got with some adapted code (from Allen Browne's
website)
http://allenbrowne.com/ser-57.html
But when I run it I get the following error message:

Run-time error '3022'
The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key or
relationship...


Below is Allen's adapted code:

Private Sub btn_Do_Copy_Click()
'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
!GetRound_ID = Me.GetRound_ID
!GetRoundPoint_ID = Me.GetRoundPoint_ID
!FromStreetNameID = Me.FromStreetNameID
!ToStreetNameID = ToStreetNameID
!From_PostCode = From_PostCode
!To_PostCode = To_PostCode
!Direction_From = Direction_From
!Direction_To = Direction_To

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for
the
related records.
.Bookmark = .LastModified
lngID = !GetRound_ID

'Duplicate the related records: append query.
If Me.[frm_Getround_Detail].Form.RecordsetClone.RecordCount
0
Then
strSql = "INSERT INTO [tbl_Getrounds] (GetRound_ID, GetRound_Detail_ID,
Run_Direction, Run_waypoint, Postcode) " & "SELECT " & lngID & " As
NewID,
GetRound_Detail_ID, Run_Direction, Run_waypoint, Postcode " & _
"FROM [tbl_Getround_Detail] WHERE Me.GetRound_ID = "
&
Me.GetRound_ID & ";"
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, ,
"cmdDupe_Click"
Resume Exit_Handler
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi Eric

I guess you are in daylight saving time now, so it must be nearly 1am!

Assuming your code is in the class module attached to the form in question
(frm_GetRounds), all you need is the following substitutions:

lngOldID -> Me.GetRound_ID
lngNewID -> Me.cbo_copy_to_new_point_ID

And I didn't notice another error in your code: SQL doesn't understand "Me"
so you must remove the "Me" after the "WHERE":

& "WHERE GetRound_ID = " & Me.GetRound_ID & ";"

--
Good Luck and Good Night :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


efandango said:
Hello Graham,

Thanks for replying.

Yes, I want to copy all the existing many-side records related to one-side
record A and relate the copies to an already-existing one-side record B.

You also correctly say: Here, lngNewID is the ID of record B, which you
wish
to select from a combo box, and lngOldID is the ID of record A, which is
presumably the current record on your form.

how do I get the references for lngNewID and lngOldID? Do i use something
like this:


Record B

your version: SELECT " & lngNewID

my version: SELECT " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID]




Record A

Your version: WHERE Me.GetRound_ID = " & lngOldID

My version: WHERE Me.GetRound_ID = " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![GetRound_ID]


It's late here (blighty), and I have been on this for literally hours...
so
I will give this a try and come back to you with a response, will you get
an
auto-reply to this?, or what is the best time to catch you?

regards


Eric



Graham Mandeno said:
Hi Eric

I'd just like to confirm that I understand you correctly: You want to
copy
all the existing many-side records related to one-side record A and
relate
the copies to an already-existing one-side record B. Is that correct?

This can be done with a single SQL statement, similar to the one you
already
have in your code:

strSql = "INSERT INTO [tbl_Getrounds] " _
& (GetRound_ID, GetRound_Detail_ID, " _
& "Run_Direction, Run_waypoint, Postcode) SELECT " _
& lngNewID & " As NewID, GetRound_Detail_ID, Run_Direction, " _
& "Run_waypoint, Postcode FROM [tbl_Getround_Detail] " _
& "WHERE Me.GetRound_ID = " & lngOldID & ";"
CurrentDb.Execute strSql, dbFailOnError

Here, lngNewID is the ID of record B, which you wish to select from a
combo
box, and lngOldID is the ID of record A, which is presumably the current
record on your form.

By the way, the reason you are getting error 3022 when trying to create a
*new* record is this line of code:
!GetRound_ID = Me.GetRound_ID

You are setting the primary key of the new record you are adding to be
the
same as the record you are copying. If GetRound_ID is an autonumber,
then
just delete that line. Otherwise, generate a new, unique value for
GetRound_ID and assign that to !GetRound_ID.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


efandango said:
I am trying to copy a Master Record and its Subform Record into a new
Master
and Subfrom Record.

But instead of the new copy of Master record PK being generated
automatically, I want to choose which other record to link it to via a
combo
box. And then hit the Do Copy button which will run the code and copy
the
master/sub to my chosen record (I can handle the combo box code, but
can't
work out how to make the inital copy code work with example code
below).


Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)




Below is how far I got with some adapted code (from Allen Browne's
website)
http://allenbrowne.com/ser-57.html
But when I run it I get the following error message:

Run-time error '3022'
The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key or
relationship...


Below is Allen's adapted code:

Private Sub btn_Do_Copy_Click()
'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
!GetRound_ID = Me.GetRound_ID
!GetRoundPoint_ID = Me.GetRoundPoint_ID
!FromStreetNameID = Me.FromStreetNameID
!ToStreetNameID = ToStreetNameID
!From_PostCode = From_PostCode
!To_PostCode = To_PostCode
!Direction_From = Direction_From
!Direction_To = Direction_To

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for
the
related records.
.Bookmark = .LastModified
lngID = !GetRound_ID

'Duplicate the related records: append query.
If Me.[frm_Getround_Detail].Form.RecordsetClone.RecordCount
0
Then
strSql = "INSERT INTO [tbl_Getrounds] (GetRound_ID, GetRound_Detail_ID,
Run_Direction, Run_waypoint, Postcode) " & "SELECT " & lngID & " As
NewID,
GetRound_Detail_ID, Run_Direction, Run_waypoint, Postcode " & _
"FROM [tbl_Getround_Detail] WHERE Me.GetRound_ID = "
&
Me.GetRound_ID & ";"
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, ,
"cmdDupe_Click"
Resume Exit_Handler
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Graham,

I tried the code below: But still get Error 3022. I have spent a lot of time
trying to figure out why this is so because as you suggested I have remmed
out the line: !GetRound_ID = Me.GetRound_ID, but the code stops on this
yellow line: .Update

another thing is; the SQL line; is it for inserting the subrecords or the
main records? the reason I ask is because I seem to have a an instruction
saying
INSERT into the master table (tbl_Getrounds) a field that belongs in the
subtable (tbl_Getround_Detail), the field is [GetRound_Detail_ID] and is the
PK for that subtable; I dunno, maybe i'm wrong, but it doesen't look correct
in there.

The code I adapated from Allen Brownes code was done by replacing what I
thought was the correct fields in the correct order; would you have a look at
my list below and tell me if the code I have now reflects the list?

Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)


have I got my fields in the SQL correctly?



This is my very latest code:
**********
'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
' !GetRound_ID = Me.GetRound_ID
'!GetRoundPoint_ID = Me.GetRoundPoint_ID
'!FromStreetNameID = Me.FromStreetNameID
' !ToStreetNameID = ToStreetNameID
From_PostCode = From_PostCode
To_PostCode = To_PostCode
'!Direction_From = Direction_From
'!Direction_To = Direction_To

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !GetRound_ID

'Duplicate the related records: append query.
If Me.[frm_Getround_Detail].Form.RecordsetClone.RecordCount > 0
Then

strSql = "INSERT INTO [tbl_Getrounds] " & "(GetRound_ID,
GetRound_Detail_ID, " & "Run_Direction, Run_waypoint, Postcode) SELECT " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID] & " As
NewID, GetRound_Detail_ID, Run_Direction, " & "Run_waypoint, Postcode FROM
[tbl_Getround_Detail] " & "WHERE GetRound_ID = " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![GetRound_ID] & ";"
CurrentDb.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, , "cmdDupe_Click"
Resume Exit_Handler

**********


Graham Mandeno said:
Hi Eric

I guess you are in daylight saving time now, so it must be nearly 1am!

Assuming your code is in the class module attached to the form in question
(frm_GetRounds), all you need is the following substitutions:

lngOldID -> Me.GetRound_ID
lngNewID -> Me.cbo_copy_to_new_point_ID

And I didn't notice another error in your code: SQL doesn't understand "Me"
so you must remove the "Me" after the "WHERE":

& "WHERE GetRound_ID = " & Me.GetRound_ID & ";"

--
Good Luck and Good Night :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


efandango said:
Hello Graham,

Thanks for replying.

Yes, I want to copy all the existing many-side records related to one-side
record A and relate the copies to an already-existing one-side record B.

You also correctly say: Here, lngNewID is the ID of record B, which you
wish
to select from a combo box, and lngOldID is the ID of record A, which is
presumably the current record on your form.

how do I get the references for lngNewID and lngOldID? Do i use something
like this:


Record B

your version: SELECT " & lngNewID

my version: SELECT " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID]




Record A

Your version: WHERE Me.GetRound_ID = " & lngOldID

My version: WHERE Me.GetRound_ID = " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![GetRound_ID]


It's late here (blighty), and I have been on this for literally hours...
so
I will give this a try and come back to you with a response, will you get
an
auto-reply to this?, or what is the best time to catch you?

regards


Eric



Graham Mandeno said:
Hi Eric

I'd just like to confirm that I understand you correctly: You want to
copy
all the existing many-side records related to one-side record A and
relate
the copies to an already-existing one-side record B. Is that correct?

This can be done with a single SQL statement, similar to the one you
already
have in your code:

strSql = "INSERT INTO [tbl_Getrounds] " _
& (GetRound_ID, GetRound_Detail_ID, " _
& "Run_Direction, Run_waypoint, Postcode) SELECT " _
& lngNewID & " As NewID, GetRound_Detail_ID, Run_Direction, " _
& "Run_waypoint, Postcode FROM [tbl_Getround_Detail] " _
& "WHERE Me.GetRound_ID = " & lngOldID & ";"
CurrentDb.Execute strSql, dbFailOnError

Here, lngNewID is the ID of record B, which you wish to select from a
combo
box, and lngOldID is the ID of record A, which is presumably the current
record on your form.

By the way, the reason you are getting error 3022 when trying to create a
*new* record is this line of code:
!GetRound_ID = Me.GetRound_ID

You are setting the primary key of the new record you are adding to be
the
same as the record you are copying. If GetRound_ID is an autonumber,
then
just delete that line. Otherwise, generate a new, unique value for
GetRound_ID and assign that to !GetRound_ID.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


I am trying to copy a Master Record and its Subform Record into a new
Master
and Subfrom Record.

But instead of the new copy of Master record PK being generated
automatically, I want to choose which other record to link it to via a
combo
box. And then hit the Do Copy button which will run the code and copy
the
master/sub to my chosen record (I can handle the combo box code, but
can't
work out how to make the inital copy code work with example code
below).


Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)




Below is how far I got with some adapted code (from Allen Browne's
website)
http://allenbrowne.com/ser-57.html
But when I run it I get the following error message:

Run-time error '3022'
The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key or
relationship...


Below is Allen's adapted code:

Private Sub btn_Do_Copy_Click()
'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
!GetRound_ID = Me.GetRound_ID
!GetRoundPoint_ID = Me.GetRoundPoint_ID
!FromStreetNameID = Me.FromStreetNameID
!ToStreetNameID = ToStreetNameID
!From_PostCode = From_PostCode
!To_PostCode = To_PostCode
!Direction_From = Direction_From
!Direction_To = Direction_To

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for
the
related records.
.Bookmark = .LastModified
lngID = !GetRound_ID

'Duplicate the related records: append query.
If Me.[frm_Getround_Detail].Form.RecordsetClone.RecordCount
0
Then
strSql = "INSERT INTO [tbl_Getrounds] (GetRound_ID, GetRound_Detail_ID,
Run_Direction, Run_waypoint, Postcode) " & "SELECT " & lngID & " As
NewID,
GetRound_Detail_ID, Run_Direction, Run_waypoint, Postcode " & _
"FROM [tbl_Getround_Detail] WHERE Me.GetRound_ID = "
&
Me.GetRound_ID & ";"
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, ,
"cmdDupe_Click"
Resume Exit_Handler
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Graham,

I tried the code below: But still get Error 3022. I have spent a lot of time
trying to figure out why this is so because as you suggested I have remmed
out the line: !GetRound_ID = Me.GetRound_ID, but the code stops on this
yellow line: .Update

another thing is; the SQL line; is it for inserting the subrecords or the
main records? the reason I ask is because I seem to have a an instruction
saying
INSERT into the master table (tbl_Getrounds) a field that belongs in the
subtable (tbl_Getround_Detail), the field is [GetRound_Detail_ID] and is the
PK for that subtable; I dunno, maybe i'm wrong, but it doesen't look correct
in there.

The code I adapated from Allen Brownes code was done by replacing what I
thought was the correct fields in the correct order; would you have a look at
my list below and tell me if the code I have now reflects the list?

Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)


have I got my fields in the SQL correctly?



This is my very latest code:
**********
'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
' !GetRound_ID = Me.GetRound_ID
'!GetRoundPoint_ID = Me.GetRoundPoint_ID
'!FromStreetNameID = Me.FromStreetNameID
' !ToStreetNameID = ToStreetNameID
From_PostCode = From_PostCode
To_PostCode = To_PostCode
'!Direction_From = Direction_From
'!Direction_To = Direction_To

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !GetRound_ID

'Duplicate the related records: append query.
If Me.[frm_Getround_Detail].Form.RecordsetClone.RecordCount > 0
Then

strSql = "INSERT INTO [tbl_Getrounds] " & "(GetRound_ID,
GetRound_Detail_ID, " & "Run_Direction, Run_waypoint, Postcode) SELECT " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID] & " As
NewID, GetRound_Detail_ID, Run_Direction, " & "Run_waypoint, Postcode FROM
[tbl_Getround_Detail] " & "WHERE GetRound_ID = " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![GetRound_ID] & ";"
CurrentDb.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, , "cmdDupe_Click"
Resume Exit_Handler

**********


Graham Mandeno said:
Hi Eric

I guess you are in daylight saving time now, so it must be nearly 1am!

Assuming your code is in the class module attached to the form in question
(frm_GetRounds), all you need is the following substitutions:

lngOldID -> Me.GetRound_ID
lngNewID -> Me.cbo_copy_to_new_point_ID

And I didn't notice another error in your code: SQL doesn't understand "Me"
so you must remove the "Me" after the "WHERE":

& "WHERE GetRound_ID = " & Me.GetRound_ID & ";"

--
Good Luck and Good Night :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


efandango said:
Hello Graham,

Thanks for replying.

Yes, I want to copy all the existing many-side records related to one-side
record A and relate the copies to an already-existing one-side record B.

You also correctly say: Here, lngNewID is the ID of record B, which you
wish
to select from a combo box, and lngOldID is the ID of record A, which is
presumably the current record on your form.

how do I get the references for lngNewID and lngOldID? Do i use something
like this:


Record B

your version: SELECT " & lngNewID

my version: SELECT " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID]




Record A

Your version: WHERE Me.GetRound_ID = " & lngOldID

My version: WHERE Me.GetRound_ID = " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![GetRound_ID]


It's late here (blighty), and I have been on this for literally hours...
so
I will give this a try and come back to you with a response, will you get
an
auto-reply to this?, or what is the best time to catch you?

regards


Eric



Graham Mandeno said:
Hi Eric

I'd just like to confirm that I understand you correctly: You want to
copy
all the existing many-side records related to one-side record A and
relate
the copies to an already-existing one-side record B. Is that correct?

This can be done with a single SQL statement, similar to the one you
already
have in your code:

strSql = "INSERT INTO [tbl_Getrounds] " _
& (GetRound_ID, GetRound_Detail_ID, " _
& "Run_Direction, Run_waypoint, Postcode) SELECT " _
& lngNewID & " As NewID, GetRound_Detail_ID, Run_Direction, " _
& "Run_waypoint, Postcode FROM [tbl_Getround_Detail] " _
& "WHERE Me.GetRound_ID = " & lngOldID & ";"
CurrentDb.Execute strSql, dbFailOnError

Here, lngNewID is the ID of record B, which you wish to select from a
combo
box, and lngOldID is the ID of record A, which is presumably the current
record on your form.

By the way, the reason you are getting error 3022 when trying to create a
*new* record is this line of code:
!GetRound_ID = Me.GetRound_ID

You are setting the primary key of the new record you are adding to be
the
same as the record you are copying. If GetRound_ID is an autonumber,
then
just delete that line. Otherwise, generate a new, unique value for
GetRound_ID and assign that to !GetRound_ID.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


I am trying to copy a Master Record and its Subform Record into a new
Master
and Subfrom Record.

But instead of the new copy of Master record PK being generated
automatically, I want to choose which other record to link it to via a
combo
box. And then hit the Do Copy button which will run the code and copy
the
master/sub to my chosen record (I can handle the combo box code, but
can't
work out how to make the inital copy code work with example code
below).


Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)




Below is how far I got with some adapted code (from Allen Browne's
website)
http://allenbrowne.com/ser-57.html
But when I run it I get the following error message:

Run-time error '3022'
The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key or
relationship...


Below is Allen's adapted code:

Private Sub btn_Do_Copy_Click()
'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
!GetRound_ID = Me.GetRound_ID
!GetRoundPoint_ID = Me.GetRoundPoint_ID
!FromStreetNameID = Me.FromStreetNameID
!ToStreetNameID = ToStreetNameID
!From_PostCode = From_PostCode
!To_PostCode = To_PostCode
!Direction_From = Direction_From
!Direction_To = Direction_To

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for
the
related records.
.Bookmark = .LastModified
lngID = !GetRound_ID

'Duplicate the related records: append query.
If Me.[frm_Getround_Detail].Form.RecordsetClone.RecordCount
0
Then
strSql = "INSERT INTO [tbl_Getrounds] (GetRound_ID, GetRound_Detail_ID,
Run_Direction, Run_waypoint, Postcode) " & "SELECT " & lngID & " As
NewID,
GetRound_Detail_ID, Run_Direction, Run_waypoint, Postcode " & _
"FROM [tbl_Getround_Detail] WHERE Me.GetRound_ID = "
&
Me.GetRound_ID & ";"
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, ,
"cmdDupe_Click"
Resume Exit_Handler
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi Eric

If I've understood you correctly, you don't want to insert ANY records into
the parent table, only the child table.

I wasn't aware of your table names until now, and you're right, the SQL line
*was* inserting records into the parent table (tbl_Getrounds) not the child
table (tbl_Getround_Detail).

Also, since GetRound_Detail_ID is the primary key of that table, and an
autonumber, you should not include it in the INSERT statement, otherwise you
will be attempting to create duplicates and nothing will happen at all!

Here is an emended version:

strSql = "INSERT INTO tbl_Getround_Detail " _
& (GetRound_ID, Run_Direction, Run_waypoint, Postcode) " _
& "SELECT " & Me.Getround_ID & " As NewID, Run_Direction, " _
& "Run_waypoint, Postcode FROM tbl_Getround_Detail " _
& "WHERE GetRound_ID = " & Me.cbo_copy_to_new_point_ID & ";"
CurrentDb.Execute strSql, dbFailOnError

This will duplicate all the child records associated with the current
GetRound_ID on the mail form and associate those copies with the new
GetRound_ID that has been selected in your combo box.

Only three fields will be copied - Run_Direction, Run_waypoint, and
Postcode. The other field (StreetNameID) will be Null, or will get its
default value if it has one. I don't know whether or not this is what you
intend.

The code below that is dying with error 3022 is attempting to duplicate the
parent record, which is NOT what I understand you are trying to do. If this
IS what you require, then post back with the details of the fields in
tbl_Getrounds, including all the indexes.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

efandango said:
Graham,

I tried the code below: But still get Error 3022. I have spent a lot of
time
trying to figure out why this is so because as you suggested I have remmed
out the line: !GetRound_ID = Me.GetRound_ID, but the code stops on this
yellow line: .Update

another thing is; the SQL line; is it for inserting the subrecords or the
main records? the reason I ask is because I seem to have a an instruction
saying
INSERT into the master table (tbl_Getrounds) a field that belongs in the
subtable (tbl_Getround_Detail), the field is [GetRound_Detail_ID] and is
the
PK for that subtable; I dunno, maybe i'm wrong, but it doesen't look
correct
in there.

The code I adapated from Allen Brownes code was done by replacing what I
thought was the correct fields in the correct order; would you have a look
at
my list below and tell me if the code I have now reflects the list?

Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)


have I got my fields in the SQL correctly?



This is my very latest code:
**********
'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
' !GetRound_ID = Me.GetRound_ID
'!GetRoundPoint_ID = Me.GetRoundPoint_ID
'!FromStreetNameID = Me.FromStreetNameID
' !ToStreetNameID = ToStreetNameID
From_PostCode = From_PostCode
To_PostCode = To_PostCode
'!Direction_From = Direction_From
'!Direction_To = Direction_To

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !GetRound_ID

'Duplicate the related records: append query.
If Me.[frm_Getround_Detail].Form.RecordsetClone.RecordCount > 0
Then

strSql = "INSERT INTO [tbl_Getrounds] " & "(GetRound_ID,
GetRound_Detail_ID, " & "Run_Direction, Run_waypoint, Postcode) SELECT " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID] & "
As
NewID, GetRound_Detail_ID, Run_Direction, " & "Run_waypoint, Postcode FROM
[tbl_Getround_Detail] " & "WHERE GetRound_ID = " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![GetRound_ID] & ";"
CurrentDb.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, ,
"cmdDupe_Click"
Resume Exit_Handler

**********


Graham Mandeno said:
Hi Eric

I guess you are in daylight saving time now, so it must be nearly 1am!

Assuming your code is in the class module attached to the form in
question
(frm_GetRounds), all you need is the following substitutions:

lngOldID -> Me.GetRound_ID
lngNewID -> Me.cbo_copy_to_new_point_ID

And I didn't notice another error in your code: SQL doesn't understand
"Me"
so you must remove the "Me" after the "WHERE":

& "WHERE GetRound_ID = " & Me.GetRound_ID & ";"

--
Good Luck and Good Night :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


efandango said:
Hello Graham,

Thanks for replying.

Yes, I want to copy all the existing many-side records related to
one-side
record A and relate the copies to an already-existing one-side record
B.

You also correctly say: Here, lngNewID is the ID of record B, which you
wish
to select from a combo box, and lngOldID is the ID of record A, which
is
presumably the current record on your form.

how do I get the references for lngNewID and lngOldID? Do i use
something
like this:


Record B

your version: SELECT " & lngNewID

my version: SELECT " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID]




Record A

Your version: WHERE Me.GetRound_ID = " & lngOldID

My version: WHERE Me.GetRound_ID = " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![GetRound_ID]


It's late here (blighty), and I have been on this for literally
hours...
so
I will give this a try and come back to you with a response, will you
get
an
auto-reply to this?, or what is the best time to catch you?

regards


Eric



:

Hi Eric

I'd just like to confirm that I understand you correctly: You want to
copy
all the existing many-side records related to one-side record A and
relate
the copies to an already-existing one-side record B. Is that correct?

This can be done with a single SQL statement, similar to the one you
already
have in your code:

strSql = "INSERT INTO [tbl_Getrounds] " _
& (GetRound_ID, GetRound_Detail_ID, " _
& "Run_Direction, Run_waypoint, Postcode) SELECT " _
& lngNewID & " As NewID, GetRound_Detail_ID, Run_Direction, " _
& "Run_waypoint, Postcode FROM [tbl_Getround_Detail] " _
& "WHERE Me.GetRound_ID = " & lngOldID & ";"
CurrentDb.Execute strSql, dbFailOnError

Here, lngNewID is the ID of record B, which you wish to select from a
combo
box, and lngOldID is the ID of record A, which is presumably the
current
record on your form.

By the way, the reason you are getting error 3022 when trying to
create a
*new* record is this line of code:
!GetRound_ID = Me.GetRound_ID

You are setting the primary key of the new record you are adding to be
the
same as the record you are copying. If GetRound_ID is an autonumber,
then
just delete that line. Otherwise, generate a new, unique value for
GetRound_ID and assign that to !GetRound_ID.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


I am trying to copy a Master Record and its Subform Record into a new
Master
and Subfrom Record.

But instead of the new copy of Master record PK being generated
automatically, I want to choose which other record to link it to via
a
combo
box. And then hit the Do Copy button which will run the code and
copy
the
master/sub to my chosen record (I can handle the combo box code, but
can't
work out how to make the inital copy code work with example code
below).


Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)




Below is how far I got with some adapted code (from Allen Browne's
website)
http://allenbrowne.com/ser-57.html
But when I run it I get the following error message:

Run-time error '3022'
The changes you requested to the table were not successful because
they
would create duplicate values in the index, primary key or
relationship...


Below is Allen's adapted code:

Private Sub btn_Do_Copy_Click()
'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
!GetRound_ID = Me.GetRound_ID
!GetRoundPoint_ID = Me.GetRoundPoint_ID
!FromStreetNameID = Me.FromStreetNameID
!ToStreetNameID = ToStreetNameID
!From_PostCode = From_PostCode
!To_PostCode = To_PostCode
!Direction_From = Direction_From
!Direction_To = Direction_To

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key
for
the
related records.
.Bookmark = .LastModified
lngID = !GetRound_ID

'Duplicate the related records: append query.
If
Me.[frm_Getround_Detail].Form.RecordsetClone.RecordCount
0
Then
strSql = "INSERT INTO [tbl_Getrounds] (GetRound_ID,
GetRound_Detail_ID,
Run_Direction, Run_waypoint, Postcode) " & "SELECT " & lngID & " As
NewID,
GetRound_Detail_ID, Run_Direction, Run_waypoint, Postcode " & _
"FROM [tbl_Getround_Detail] WHERE Me.GetRound_ID
= "
&
Me.GetRound_ID & ";"
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, ,
"cmdDupe_Click"
Resume Exit_Handler
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi Eric

If I've understood you correctly, you don't want to insert ANY records into
the parent table, only the child table.

I wasn't aware of your table names until now, and you're right, the SQL line
*was* inserting records into the parent table (tbl_Getrounds) not the child
table (tbl_Getround_Detail).

Also, since GetRound_Detail_ID is the primary key of that table, and an
autonumber, you should not include it in the INSERT statement, otherwise you
will be attempting to create duplicates and nothing will happen at all!

Here is an emended version:

strSql = "INSERT INTO tbl_Getround_Detail " _
& (GetRound_ID, Run_Direction, Run_waypoint, Postcode) " _
& "SELECT " & Me.Getround_ID & " As NewID, Run_Direction, " _
& "Run_waypoint, Postcode FROM tbl_Getround_Detail " _
& "WHERE GetRound_ID = " & Me.cbo_copy_to_new_point_ID & ";"
CurrentDb.Execute strSql, dbFailOnError

This will duplicate all the child records associated with the current
GetRound_ID on the mail form and associate those copies with the new
GetRound_ID that has been selected in your combo box.

Only three fields will be copied - Run_Direction, Run_waypoint, and
Postcode. The other field (StreetNameID) will be Null, or will get its
default value if it has one. I don't know whether or not this is what you
intend.

The code below that is dying with error 3022 is attempting to duplicate the
parent record, which is NOT what I understand you are trying to do. If this
IS what you require, then post back with the details of the fields in
tbl_Getrounds, including all the indexes.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

efandango said:
Graham,

I tried the code below: But still get Error 3022. I have spent a lot of
time
trying to figure out why this is so because as you suggested I have remmed
out the line: !GetRound_ID = Me.GetRound_ID, but the code stops on this
yellow line: .Update

another thing is; the SQL line; is it for inserting the subrecords or the
main records? the reason I ask is because I seem to have a an instruction
saying
INSERT into the master table (tbl_Getrounds) a field that belongs in the
subtable (tbl_Getround_Detail), the field is [GetRound_Detail_ID] and is
the
PK for that subtable; I dunno, maybe i'm wrong, but it doesen't look
correct
in there.

The code I adapated from Allen Brownes code was done by replacing what I
thought was the correct fields in the correct order; would you have a look
at
my list below and tell me if the code I have now reflects the list?

Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)


have I got my fields in the SQL correctly?



This is my very latest code:
**********
'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
' !GetRound_ID = Me.GetRound_ID
'!GetRoundPoint_ID = Me.GetRoundPoint_ID
'!FromStreetNameID = Me.FromStreetNameID
' !ToStreetNameID = ToStreetNameID
From_PostCode = From_PostCode
To_PostCode = To_PostCode
'!Direction_From = Direction_From
'!Direction_To = Direction_To

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !GetRound_ID

'Duplicate the related records: append query.
If Me.[frm_Getround_Detail].Form.RecordsetClone.RecordCount > 0
Then

strSql = "INSERT INTO [tbl_Getrounds] " & "(GetRound_ID,
GetRound_Detail_ID, " & "Run_Direction, Run_waypoint, Postcode) SELECT " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID] & "
As
NewID, GetRound_Detail_ID, Run_Direction, " & "Run_waypoint, Postcode FROM
[tbl_Getround_Detail] " & "WHERE GetRound_ID = " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![GetRound_ID] & ";"
CurrentDb.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, ,
"cmdDupe_Click"
Resume Exit_Handler

**********


Graham Mandeno said:
Hi Eric

I guess you are in daylight saving time now, so it must be nearly 1am!

Assuming your code is in the class module attached to the form in
question
(frm_GetRounds), all you need is the following substitutions:

lngOldID -> Me.GetRound_ID
lngNewID -> Me.cbo_copy_to_new_point_ID

And I didn't notice another error in your code: SQL doesn't understand
"Me"
so you must remove the "Me" after the "WHERE":

& "WHERE GetRound_ID = " & Me.GetRound_ID & ";"

--
Good Luck and Good Night :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


efandango said:
Hello Graham,

Thanks for replying.

Yes, I want to copy all the existing many-side records related to
one-side
record A and relate the copies to an already-existing one-side record
B.

You also correctly say: Here, lngNewID is the ID of record B, which you
wish
to select from a combo box, and lngOldID is the ID of record A, which
is
presumably the current record on your form.

how do I get the references for lngNewID and lngOldID? Do i use
something
like this:


Record B

your version: SELECT " & lngNewID

my version: SELECT " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID]




Record A

Your version: WHERE Me.GetRound_ID = " & lngOldID

My version: WHERE Me.GetRound_ID = " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![GetRound_ID]


It's late here (blighty), and I have been on this for literally
hours...
so
I will give this a try and come back to you with a response, will you
get
an
auto-reply to this?, or what is the best time to catch you?

regards


Eric



:

Hi Eric

I'd just like to confirm that I understand you correctly: You want to
copy
all the existing many-side records related to one-side record A and
relate
the copies to an already-existing one-side record B. Is that correct?

This can be done with a single SQL statement, similar to the one you
already
have in your code:

strSql = "INSERT INTO [tbl_Getrounds] " _
& (GetRound_ID, GetRound_Detail_ID, " _
& "Run_Direction, Run_waypoint, Postcode) SELECT " _
& lngNewID & " As NewID, GetRound_Detail_ID, Run_Direction, " _
& "Run_waypoint, Postcode FROM [tbl_Getround_Detail] " _
& "WHERE Me.GetRound_ID = " & lngOldID & ";"
CurrentDb.Execute strSql, dbFailOnError

Here, lngNewID is the ID of record B, which you wish to select from a
combo
box, and lngOldID is the ID of record A, which is presumably the
current
record on your form.

By the way, the reason you are getting error 3022 when trying to
create a
*new* record is this line of code:
!GetRound_ID = Me.GetRound_ID

You are setting the primary key of the new record you are adding to be
the
same as the record you are copying. If GetRound_ID is an autonumber,
then
just delete that line. Otherwise, generate a new, unique value for
GetRound_ID and assign that to !GetRound_ID.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


I am trying to copy a Master Record and its Subform Record into a new
Master
and Subfrom Record.

But instead of the new copy of Master record PK being generated
automatically, I want to choose which other record to link it to via
a
combo
box. And then hit the Do Copy button which will run the code and
copy
the
master/sub to my chosen record (I can handle the combo box code, but
can't
work out how to make the inital copy code work with example code
below).


Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)




Below is how far I got with some adapted code (from Allen Browne's
website)
http://allenbrowne.com/ser-57.html
But when I run it I get the following error message:

Run-time error '3022'
The changes you requested to the table were not successful because
they
would create duplicate values in the index, primary key or
relationship...


Below is Allen's adapted code:

Private Sub btn_Do_Copy_Click()
'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
!GetRound_ID = Me.GetRound_ID
!GetRoundPoint_ID = Me.GetRoundPoint_ID
!FromStreetNameID = Me.FromStreetNameID
!ToStreetNameID = ToStreetNameID
!From_PostCode = From_PostCode
!To_PostCode = To_PostCode
!Direction_From = Direction_From
!Direction_To = Direction_To

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key
for
the
related records.
.Bookmark = .LastModified
lngID = !GetRound_ID

'Duplicate the related records: append query.
If
Me.[frm_Getround_Detail].Form.RecordsetClone.RecordCount
0
Then
strSql = "INSERT INTO [tbl_Getrounds] (GetRound_ID,
GetRound_Detail_ID,
Run_Direction, Run_waypoint, Postcode) " & "SELECT " & lngID & " As
NewID,
GetRound_Detail_ID, Run_Direction, Run_waypoint, Postcode " & _
"FROM [tbl_Getround_Detail] WHERE Me.GetRound_ID
= "
&
Me.GetRound_ID & ";"
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, ,
"cmdDupe_Click"
Resume Exit_Handler
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi Graham, (PS I wrote this in word, so it should cut n paste nicely for
easier reading if you need to)

OK, let’s see if I can make myself completely clear (bear with me here…)
I want to insert into both tables: tbl_Getrounds and its subtable
‘tbl_Getround_Detail’.

The table below (tbl_Getrounds) is what holds any given single record that I
want to selectively copy by saying, “when I copy this record, I want Access
to generate a new Autonumber ‘GetRound_ID’ for the new record. But I want to
specfiy via a combo box which (already existing) ‘GetRoundPoint_ID’ to assign
it to. Once it has done that, I then want the donor records in the subtable
to also be copied to a new subtable matching the new/chosen
‘GetRoundPoint_ID’ record.
So that I end up with a mirror copy of the master/child records, but
assigned to another record (via GetRoundPoint).

The name of the box that I will select the ‘destination’ record for is:
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID]

I hope that makes sense for you, (If only Microsoft would allow some kind of
image transfer, then I could just paste a screenshot which once you saw it
would make perfect sense, 1st time around) meanwhile…

Table Specs for both tables below: (For now I have put just the table
fields, if you still need the indexes let me know, I didn’t want to swamp you
with 5 pages of data…)

Table: tbl_Getrounds (Master containing subtable: tbl_Getround_Detail)

Link Master Fields: txtCurrentPoint
Link Child Fields: GetRoundPoint_ID
Columns
Name Type Size
GetRound_ID (Autonumber) Long Integer 4
GetRoundPoint_ID Long Integer 4
Run_No Long Integer 4
FromStreetNameID Long Integer 4
ToStreetNameID Long Integer 4
FromGetRound Text 100
ToGetRound Text 100
From_PostCode Text 8
To_PostCode Text 8
Reason Text 150
GetRoundPoint Text 100
GetRound_Note Text 255
GetRound_SetDown Text 10
Copied Yes/No 1

Table: tbl_Getround_Detail (Linked to Master table : tbl_Getrounds)


Link Master Fields: GetRound_ID
Link Child Fields: GetRound_ID
Columns
Name Type Size
GetRound_Detail_ID (Autonumber) Long Integer 4
GetRound_ID Long Integer 4
Run_Direction Text 255
Run_waypoint Text 255
Postcode Text 8
Lat Text 30
Notmapped Yes/No 1
Run_No Long Integer 4
StreetNameID Long Integer 4



Graham Mandeno said:
Hi Eric

If I've understood you correctly, you don't want to insert ANY records into
the parent table, only the child table.

I wasn't aware of your table names until now, and you're right, the SQL line
*was* inserting records into the parent table (tbl_Getrounds) not the child
table (tbl_Getround_Detail).

Also, since GetRound_Detail_ID is the primary key of that table, and an
autonumber, you should not include it in the INSERT statement, otherwise you
will be attempting to create duplicates and nothing will happen at all!

Here is an emended version:

strSql = "INSERT INTO tbl_Getround_Detail " _
& (GetRound_ID, Run_Direction, Run_waypoint, Postcode) " _
& "SELECT " & Me.Getround_ID & " As NewID, Run_Direction, " _
& "Run_waypoint, Postcode FROM tbl_Getround_Detail " _
& "WHERE GetRound_ID = " & Me.cbo_copy_to_new_point_ID & ";"
CurrentDb.Execute strSql, dbFailOnError

This will duplicate all the child records associated with the current
GetRound_ID on the mail form and associate those copies with the new
GetRound_ID that has been selected in your combo box.

Only three fields will be copied - Run_Direction, Run_waypoint, and
Postcode. The other field (StreetNameID) will be Null, or will get its
default value if it has one. I don't know whether or not this is what you
intend.

The code below that is dying with error 3022 is attempting to duplicate the
parent record, which is NOT what I understand you are trying to do. If this
IS what you require, then post back with the details of the fields in
tbl_Getrounds, including all the indexes.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

efandango said:
Graham,

I tried the code below: But still get Error 3022. I have spent a lot of
time
trying to figure out why this is so because as you suggested I have remmed
out the line: !GetRound_ID = Me.GetRound_ID, but the code stops on this
yellow line: .Update

another thing is; the SQL line; is it for inserting the subrecords or the
main records? the reason I ask is because I seem to have a an instruction
saying
INSERT into the master table (tbl_Getrounds) a field that belongs in the
subtable (tbl_Getround_Detail), the field is [GetRound_Detail_ID] and is
the
PK for that subtable; I dunno, maybe i'm wrong, but it doesen't look
correct
in there.

The code I adapated from Allen Brownes code was done by replacing what I
thought was the correct fields in the correct order; would you have a look
at
my list below and tell me if the code I have now reflects the list?

Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)


have I got my fields in the SQL correctly?



This is my very latest code:
**********
'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
' !GetRound_ID = Me.GetRound_ID
'!GetRoundPoint_ID = Me.GetRoundPoint_ID
'!FromStreetNameID = Me.FromStreetNameID
' !ToStreetNameID = ToStreetNameID
From_PostCode = From_PostCode
To_PostCode = To_PostCode
'!Direction_From = Direction_From
'!Direction_To = Direction_To

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !GetRound_ID

'Duplicate the related records: append query.
If Me.[frm_Getround_Detail].Form.RecordsetClone.RecordCount > 0
Then

strSql = "INSERT INTO [tbl_Getrounds] " & "(GetRound_ID,
GetRound_Detail_ID, " & "Run_Direction, Run_waypoint, Postcode) SELECT " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID] & "
As
NewID, GetRound_Detail_ID, Run_Direction, " & "Run_waypoint, Postcode FROM
[tbl_Getround_Detail] " & "WHERE GetRound_ID = " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![GetRound_ID] & ";"
CurrentDb.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, ,
"cmdDupe_Click"
Resume Exit_Handler

**********


Graham Mandeno said:
Hi Eric

I guess you are in daylight saving time now, so it must be nearly 1am!

Assuming your code is in the class module attached to the form in
question
(frm_GetRounds), all you need is the following substitutions:

lngOldID -> Me.GetRound_ID
lngNewID -> Me.cbo_copy_to_new_point_ID

And I didn't notice another error in your code: SQL doesn't understand
"Me"
so you must remove the "Me" after the "WHERE":

& "WHERE GetRound_ID = " & Me.GetRound_ID & ";"

--
Good Luck and Good Night :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Hello Graham,

Thanks for replying.

Yes, I want to copy all the existing many-side records related to
one-side
record A and relate the copies to an already-existing one-side record
B.

You also correctly say: Here, lngNewID is the ID of record B, which you
wish
to select from a combo box, and lngOldID is the ID of record A, which
is
presumably the current record on your form.

how do I get the references for lngNewID and lngOldID? Do i use
something
like this:


Record B

your version: SELECT " & lngNewID

my version: SELECT " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID]




Record A

Your version: WHERE Me.GetRound_ID = " & lngOldID

My version: WHERE Me.GetRound_ID = " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![GetRound_ID]


It's late here (blighty), and I have been on this for literally
hours...
so
I will give this a try and come back to you with a response, will you
get
an
auto-reply to this?, or what is the best time to catch you?

regards


Eric



:

Hi Eric

I'd just like to confirm that I understand you correctly: You want to
copy
all the existing many-side records related to one-side record A and
relate
the copies to an already-existing one-side record B. Is that correct?

This can be done with a single SQL statement, similar to the one you
already
have in your code:

strSql = "INSERT INTO [tbl_Getrounds] " _
& (GetRound_ID, GetRound_Detail_ID, " _
& "Run_Direction, Run_waypoint, Postcode) SELECT " _
& lngNewID & " As NewID, GetRound_Detail_ID, Run_Direction, " _
& "Run_waypoint, Postcode FROM [tbl_Getround_Detail] " _
& "WHERE Me.GetRound_ID = " & lngOldID & ";"
CurrentDb.Execute strSql, dbFailOnError

Here, lngNewID is the ID of record B, which you wish to select from a
combo
box, and lngOldID is the ID of record A, which is presumably the
current
record on your form.

By the way, the reason you are getting error 3022 when trying to
create a
*new* record is this line of code:
!GetRound_ID = Me.GetRound_ID

You are setting the primary key of the new record you are adding to be
the
same as the record you are copying. If GetRound_ID is an autonumber,
then
just delete that line. Otherwise, generate a new, unique value for
GetRound_ID and assign that to !GetRound_ID.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


I am trying to copy a Master Record and its Subform Record into a new
Master
and Subfrom Record.
Click to expand...
Click to expand...
Click to expand...
 
Hi Graham, (PS I wrote this in word, so it should cut n paste nicely for
easier reading if you need to)

OK, let’s see if I can make myself completely clear (bear with me here…)
I want to insert into both tables: tbl_Getrounds and its subtable
‘tbl_Getround_Detail’.

The table below (tbl_Getrounds) is what holds any given single record that I
want to selectively copy by saying, “when I copy this record, I want Access
to generate a new Autonumber ‘GetRound_ID’ for the new record. But I want to
specfiy via a combo box which (already existing) ‘GetRoundPoint_ID’ to assign
it to. Once it has done that, I then want the donor records in the subtable
to also be copied to a new subtable matching the new/chosen
‘GetRoundPoint_ID’ record.
So that I end up with a mirror copy of the master/child records, but
assigned to another record (via GetRoundPoint).

The name of the box that I will select the ‘destination’ record for is:
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID]

I hope that makes sense for you, (If only Microsoft would allow some kind of
image transfer, then I could just paste a screenshot which once you saw it
would make perfect sense, 1st time around) meanwhile…

Table Specs for both tables below: (For now I have put just the table
fields, if you still need the indexes let me know, I didn’t want to swamp you
with 5 pages of data…)

Table: tbl_Getrounds (Master containing subtable: tbl_Getround_Detail)

Link Master Fields: txtCurrentPoint
Link Child Fields: GetRoundPoint_ID
Columns
Name Type Size
GetRound_ID (Autonumber) Long Integer 4
GetRoundPoint_ID Long Integer 4
Run_No Long Integer 4
FromStreetNameID Long Integer 4
ToStreetNameID Long Integer 4
FromGetRound Text 100
ToGetRound Text 100
From_PostCode Text 8
To_PostCode Text 8
Reason Text 150
GetRoundPoint Text 100
GetRound_Note Text 255
GetRound_SetDown Text 10
Copied Yes/No 1

Table: tbl_Getround_Detail (Linked to Master table : tbl_Getrounds)


Link Master Fields: GetRound_ID
Link Child Fields: GetRound_ID
Columns
Name Type Size
GetRound_Detail_ID (Autonumber) Long Integer 4
GetRound_ID Long Integer 4
Run_Direction Text 255
Run_waypoint Text 255
Postcode Text 8
Lat Text 30
Notmapped Yes/No 1
Run_No Long Integer 4
StreetNameID Long Integer 4



Graham Mandeno said:
Hi Eric

If I've understood you correctly, you don't want to insert ANY records into
the parent table, only the child table.

I wasn't aware of your table names until now, and you're right, the SQL line
*was* inserting records into the parent table (tbl_Getrounds) not the child
table (tbl_Getround_Detail).

Also, since GetRound_Detail_ID is the primary key of that table, and an
autonumber, you should not include it in the INSERT statement, otherwise you
will be attempting to create duplicates and nothing will happen at all!

Here is an emended version:

strSql = "INSERT INTO tbl_Getround_Detail " _
& (GetRound_ID, Run_Direction, Run_waypoint, Postcode) " _
& "SELECT " & Me.Getround_ID & " As NewID, Run_Direction, " _
& "Run_waypoint, Postcode FROM tbl_Getround_Detail " _
& "WHERE GetRound_ID = " & Me.cbo_copy_to_new_point_ID & ";"
CurrentDb.Execute strSql, dbFailOnError

This will duplicate all the child records associated with the current
GetRound_ID on the mail form and associate those copies with the new
GetRound_ID that has been selected in your combo box.

Only three fields will be copied - Run_Direction, Run_waypoint, and
Postcode. The other field (StreetNameID) will be Null, or will get its
default value if it has one. I don't know whether or not this is what you
intend.

The code below that is dying with error 3022 is attempting to duplicate the
parent record, which is NOT what I understand you are trying to do. If this
IS what you require, then post back with the details of the fields in
tbl_Getrounds, including all the indexes.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

efandango said:
Graham,

I tried the code below: But still get Error 3022. I have spent a lot of
time
trying to figure out why this is so because as you suggested I have remmed
out the line: !GetRound_ID = Me.GetRound_ID, but the code stops on this
yellow line: .Update

another thing is; the SQL line; is it for inserting the subrecords or the
main records? the reason I ask is because I seem to have a an instruction
saying
INSERT into the master table (tbl_Getrounds) a field that belongs in the
subtable (tbl_Getround_Detail), the field is [GetRound_Detail_ID] and is
the
PK for that subtable; I dunno, maybe i'm wrong, but it doesen't look
correct
in there.

The code I adapated from Allen Brownes code was done by replacing what I
thought was the correct fields in the correct order; would you have a look
at
my list below and tell me if the code I have now reflects the list?

Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)


have I got my fields in the SQL correctly?



This is my very latest code:
**********
'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
' !GetRound_ID = Me.GetRound_ID
'!GetRoundPoint_ID = Me.GetRoundPoint_ID
'!FromStreetNameID = Me.FromStreetNameID
' !ToStreetNameID = ToStreetNameID
From_PostCode = From_PostCode
To_PostCode = To_PostCode
'!Direction_From = Direction_From
'!Direction_To = Direction_To

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !GetRound_ID

'Duplicate the related records: append query.
If Me.[frm_Getround_Detail].Form.RecordsetClone.RecordCount > 0
Then

strSql = "INSERT INTO [tbl_Getrounds] " & "(GetRound_ID,
GetRound_Detail_ID, " & "Run_Direction, Run_waypoint, Postcode) SELECT " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID] & "
As
NewID, GetRound_Detail_ID, Run_Direction, " & "Run_waypoint, Postcode FROM
[tbl_Getround_Detail] " & "WHERE GetRound_ID = " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![GetRound_ID] & ";"
CurrentDb.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, ,
"cmdDupe_Click"
Resume Exit_Handler

**********


Graham Mandeno said:
Hi Eric

I guess you are in daylight saving time now, so it must be nearly 1am!

Assuming your code is in the class module attached to the form in
question
(frm_GetRounds), all you need is the following substitutions:

lngOldID -> Me.GetRound_ID
lngNewID -> Me.cbo_copy_to_new_point_ID

And I didn't notice another error in your code: SQL doesn't understand
"Me"
so you must remove the "Me" after the "WHERE":

& "WHERE GetRound_ID = " & Me.GetRound_ID & ";"

--
Good Luck and Good Night :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Hello Graham,

Thanks for replying.

Yes, I want to copy all the existing many-side records related to
one-side
record A and relate the copies to an already-existing one-side record
B.

You also correctly say: Here, lngNewID is the ID of record B, which you
wish
to select from a combo box, and lngOldID is the ID of record A, which
is
presumably the current record on your form.

how do I get the references for lngNewID and lngOldID? Do i use
something
like this:


Record B

your version: SELECT " & lngNewID

my version: SELECT " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID]




Record A

Your version: WHERE Me.GetRound_ID = " & lngOldID

My version: WHERE Me.GetRound_ID = " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![GetRound_ID]


It's late here (blighty), and I have been on this for literally
hours...
so
I will give this a try and come back to you with a response, will you
get
an
auto-reply to this?, or what is the best time to catch you?

regards


Eric



:

Hi Eric

I'd just like to confirm that I understand you correctly: You want to
copy
all the existing many-side records related to one-side record A and
relate
the copies to an already-existing one-side record B. Is that correct?

This can be done with a single SQL statement, similar to the one you
already
have in your code:

strSql = "INSERT INTO [tbl_Getrounds] " _
& (GetRound_ID, GetRound_Detail_ID, " _
& "Run_Direction, Run_waypoint, Postcode) SELECT " _
& lngNewID & " As NewID, GetRound_Detail_ID, Run_Direction, " _
& "Run_waypoint, Postcode FROM [tbl_Getround_Detail] " _
& "WHERE Me.GetRound_ID = " & lngOldID & ";"
CurrentDb.Execute strSql, dbFailOnError

Here, lngNewID is the ID of record B, which you wish to select from a
combo
box, and lngOldID is the ID of record A, which is presumably the
current
record on your form.

By the way, the reason you are getting error 3022 when trying to
create a
*new* record is this line of code:
!GetRound_ID = Me.GetRound_ID

You are setting the primary key of the new record you are adding to be
the
same as the record you are copying. If GetRound_ID is an autonumber,
then
just delete that line. Otherwise, generate a new, unique value for
GetRound_ID and assign that to !GetRound_ID.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


I am trying to copy a Master Record and its Subform Record into a new
Master
and Subfrom Record.
Click to expand...
Click to expand...
Click to expand...
 
Hi Eric

Ahhhh! Eureka!

Then your code must do the following:

1. Add a record to a recordset based on tbl_Getrounds.

2. Copy all the fields from the current record to the new one EXCEPT for the
autonumber (GetRound_ID) and the one you want to change (GetRoundPoint_ID).

3. Set the new GetRoundPoint_ID and save the new GetRound_ID.

4. Save the new record.

5. Execute an INSERT INTO to copy the related records.

The code below should go most of the way to doing the trick.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

============= start code ===================
Private Sub btn_Do_Copy_Click()
Dim strSql As String
Dim lngNewID As Long
Dim strFieldList As String
On Error GoTo ProcErr

'Save any 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."
GoTo ProcExit
End If

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!FromStreetNameID = FromStreetNameID
!ToStreetNameID = ToStreetNameID
!From_PostCode = From_PostCode
!To_PostCode = To_PostCode
!Direction_From = Direction_From
!Direction_To = Direction_To
'etc for other fields.

' set the new GetRoundPoint_ID
!GetRoundPoint_ID = cbo_copy_to_new_point_ID

' save the new GetRound_ID
lngNewID = !GetRound_ID
.Update

' make a list of all the child table fields *except*
' GetRound_Detail_ID and GetRound_ID
strFieldList = ", Run_Direction, Run_waypoint" _
& ", Postcode, Lat, Notmapped, Run_No, StreetNameID "
strSql = "INSERT INTO tbl_Getround_Detail " _
& "(GetRound_ID" & strFieldList & ") " _
& "SELECT " & lngNewID & " As NewID" & strFieldList _
& "FROM tbl_Getround_Detail " _
& "WHERE GetRound_ID=" & Me.GetRound_ID & ";"
CurrentDb.Execute strSql, dbFailOnError

' navigate to the new record
Me.Bookmark = .LastModified
End With

ProcExit:
Exit Sub

ProcErr:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Resume ProcExit
End Sub
=============== end code =================

efandango said:
Hi Graham, (PS I wrote this in word, so it should cut n paste nicely for
easier reading if you need to)

OK, let's see if I can make myself completely clear (bear with me here.)
I want to insert into both tables: tbl_Getrounds and its subtable
'tbl_Getround_Detail'.

The table below (tbl_Getrounds) is what holds any given single record that
I
want to selectively copy by saying, "when I copy this record, I want
Access
to generate a new Autonumber 'GetRound_ID' for the new record. But I want
to
specfiy via a combo box which (already existing) 'GetRoundPoint_ID' to
assign
it to. Once it has done that, I then want the donor records in the
subtable
to also be copied to a new subtable matching the new/chosen
'GetRoundPoint_ID' record.
So that I end up with a mirror copy of the master/child records, but
assigned to another record (via GetRoundPoint).

The name of the box that I will select the 'destination' record for is:
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID]

I hope that makes sense for you, (If only Microsoft would allow some kind
of
image transfer, then I could just paste a screenshot which once you saw it
would make perfect sense, 1st time around) meanwhile.

Table Specs for both tables below: (For now I have put just the table
fields, if you still need the indexes let me know, I didn't want to swamp
you
with 5 pages of data.)

Table: tbl_Getrounds (Master containing subtable: tbl_Getround_Detail)

Link Master Fields: txtCurrentPoint
Link Child Fields: GetRoundPoint_ID
Columns
Name Type Size
GetRound_ID (Autonumber) Long Integer 4
GetRoundPoint_ID Long Integer 4
Run_No Long Integer 4
FromStreetNameID Long Integer 4
ToStreetNameID Long Integer 4
FromGetRound Text 100
ToGetRound Text 100
From_PostCode Text 8
To_PostCode Text 8
Reason Text 150
GetRoundPoint Text 100
GetRound_Note Text 255
GetRound_SetDown Text 10
Copied Yes/No 1

Table: tbl_Getround_Detail (Linked to Master table : tbl_Getrounds)


Link Master Fields: GetRound_ID
Link Child Fields: GetRound_ID
Columns
Name Type Size
GetRound_Detail_ID (Autonumber) Long Integer 4
GetRound_ID Long Integer 4
Run_Direction Text 255
Run_waypoint Text 255
Postcode Text 8
Lat Text 30
Notmapped Yes/No 1
Run_No Long Integer 4
StreetNameID Long Integer 4



Graham Mandeno said:
Hi Eric

If I've understood you correctly, you don't want to insert ANY records
into
the parent table, only the child table.

I wasn't aware of your table names until now, and you're right, the SQL
line
*was* inserting records into the parent table (tbl_Getrounds) not the
child
table (tbl_Getround_Detail).

Also, since GetRound_Detail_ID is the primary key of that table, and an
autonumber, you should not include it in the INSERT statement, otherwise
you
will be attempting to create duplicates and nothing will happen at all!

Here is an emended version:

strSql = "INSERT INTO tbl_Getround_Detail " _
& (GetRound_ID, Run_Direction, Run_waypoint, Postcode) " _
& "SELECT " & Me.Getround_ID & " As NewID, Run_Direction, " _
& "Run_waypoint, Postcode FROM tbl_Getround_Detail " _
& "WHERE GetRound_ID = " & Me.cbo_copy_to_new_point_ID & ";"
CurrentDb.Execute strSql, dbFailOnError

This will duplicate all the child records associated with the current
GetRound_ID on the mail form and associate those copies with the new
GetRound_ID that has been selected in your combo box.

Only three fields will be copied - Run_Direction, Run_waypoint, and
Postcode. The other field (StreetNameID) will be Null, or will get its
default value if it has one. I don't know whether or not this is what
you
intend.

The code below that is dying with error 3022 is attempting to duplicate
the
parent record, which is NOT what I understand you are trying to do. If
this
IS what you require, then post back with the details of the fields in
tbl_Getrounds, including all the indexes.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

efandango said:
Graham,

I tried the code below: But still get Error 3022. I have spent a lot of
time
trying to figure out why this is so because as you suggested I have
remmed
out the line: !GetRound_ID = Me.GetRound_ID, but the code stops on this
yellow line: .Update

another thing is; the SQL line; is it for inserting the subrecords or
the
main records? the reason I ask is because I seem to have a an
instruction
saying
INSERT into the master table (tbl_Getrounds) a field that belongs in
the
subtable (tbl_Getround_Detail), the field is [GetRound_Detail_ID] and
is
the
PK for that subtable; I dunno, maybe i'm wrong, but it doesen't look
correct
in there.

The code I adapated from Allen Brownes code was done by replacing what
I
thought was the correct fields in the correct order; would you have a
look
at
my list below and tell me if the code I have now reflects the list?

Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)


have I got my fields in the SQL correctly?



This is my very latest code:
**********
'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
' !GetRound_ID = Me.GetRound_ID
'!GetRoundPoint_ID = Me.GetRoundPoint_ID
'!FromStreetNameID = Me.FromStreetNameID
' !ToStreetNameID = ToStreetNameID
From_PostCode = From_PostCode
To_PostCode = To_PostCode
'!Direction_From = Direction_From
'!Direction_To = Direction_To

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for
the
related records.
.Bookmark = .LastModified
lngID = !GetRound_ID

'Duplicate the related records: append query.
If Me.[frm_Getround_Detail].Form.RecordsetClone.RecordCount
0
Then

strSql = "INSERT INTO [tbl_Getrounds] " & "(GetRound_ID,
GetRound_Detail_ID, " & "Run_Direction, Run_waypoint, Postcode) SELECT
" &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID] &
"
As
NewID, GetRound_Detail_ID, Run_Direction, " & "Run_waypoint, Postcode
FROM
[tbl_Getround_Detail] " & "WHERE GetRound_ID = " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![GetRound_ID] & ";"
CurrentDb.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, ,
"cmdDupe_Click"
Resume Exit_Handler

**********


:

Hi Eric

I guess you are in daylight saving time now, so it must be nearly 1am!

Assuming your code is in the class module attached to the form in
question
(frm_GetRounds), all you need is the following substitutions:

lngOldID -> Me.GetRound_ID
lngNewID -> Me.cbo_copy_to_new_point_ID

And I didn't notice another error in your code: SQL doesn't
understand
"Me"
so you must remove the "Me" after the "WHERE":

& "WHERE GetRound_ID = " & Me.GetRound_ID & ";"

--
Good Luck and Good Night :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Hello Graham,

Thanks for replying.

Yes, I want to copy all the existing many-side records related to
one-side
record A and relate the copies to an already-existing one-side
record
B.

You also correctly say: Here, lngNewID is the ID of record B, which
you
wish
to select from a combo box, and lngOldID is the ID of record A,
which
is
presumably the current record on your form.

how do I get the references for lngNewID and lngOldID? Do i use
something
like this:


Record B

your version: SELECT " & lngNewID

my version: SELECT " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID]




Record A

Your version: WHERE Me.GetRound_ID = " & lngOldID

My version: WHERE Me.GetRound_ID = " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![GetRound_ID]


It's late here (blighty), and I have been on this for literally
hours...
so
I will give this a try and come back to you with a response, will
you
get
an
auto-reply to this?, or what is the best time to catch you?

regards


Eric



:

Hi Eric

I'd just like to confirm that I understand you correctly: You want
to
copy
all the existing many-side records related to one-side record A and
relate
the copies to an already-existing one-side record B. Is that
correct?

This can be done with a single SQL statement, similar to the one
you
already
have in your code:

strSql = "INSERT INTO [tbl_Getrounds] " _
& (GetRound_ID, GetRound_Detail_ID, " _
& "Run_Direction, Run_waypoint, Postcode) SELECT " _
& lngNewID & " As NewID, GetRound_Detail_ID, Run_Direction, " _
& "Run_waypoint, Postcode FROM [tbl_Getround_Detail] " _
& "WHERE Me.GetRound_ID = " & lngOldID & ";"
CurrentDb.Execute strSql, dbFailOnError

Here, lngNewID is the ID of record B, which you wish to select from
a
combo
box, and lngOldID is the ID of record A, which is presumably the
current
record on your form.

By the way, the reason you are getting error 3022 when trying to
create a
*new* record is this line of code:
!GetRound_ID = Me.GetRound_ID

You are setting the primary key of the new record you are adding to
be
the
same as the record you are copying. If GetRound_ID is an
autonumber,
then
just delete that line. Otherwise, generate a new, unique value for
GetRound_ID and assign that to !GetRound_ID.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


I am trying to copy a Master Record and its Subform Record into a
new
Master
and Subfrom Record.
Click to expand...
Click to expand...
Click to expand...
 
Hi Eric

Ahhhh! Eureka!

Then your code must do the following:

1. Add a record to a recordset based on tbl_Getrounds.

2. Copy all the fields from the current record to the new one EXCEPT for the
autonumber (GetRound_ID) and the one you want to change (GetRoundPoint_ID).

3. Set the new GetRoundPoint_ID and save the new GetRound_ID.

4. Save the new record.

5. Execute an INSERT INTO to copy the related records.

The code below should go most of the way to doing the trick.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

============= start code ===================
Private Sub btn_Do_Copy_Click()
Dim strSql As String
Dim lngNewID As Long
Dim strFieldList As String
On Error GoTo ProcErr

'Save any 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."
GoTo ProcExit
End If

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!FromStreetNameID = FromStreetNameID
!ToStreetNameID = ToStreetNameID
!From_PostCode = From_PostCode
!To_PostCode = To_PostCode
!Direction_From = Direction_From
!Direction_To = Direction_To
'etc for other fields.

' set the new GetRoundPoint_ID
!GetRoundPoint_ID = cbo_copy_to_new_point_ID

' save the new GetRound_ID
lngNewID = !GetRound_ID
.Update

' make a list of all the child table fields *except*
' GetRound_Detail_ID and GetRound_ID
strFieldList = ", Run_Direction, Run_waypoint" _
& ", Postcode, Lat, Notmapped, Run_No, StreetNameID "
strSql = "INSERT INTO tbl_Getround_Detail " _
& "(GetRound_ID" & strFieldList & ") " _
& "SELECT " & lngNewID & " As NewID" & strFieldList _
& "FROM tbl_Getround_Detail " _
& "WHERE GetRound_ID=" & Me.GetRound_ID & ";"
CurrentDb.Execute strSql, dbFailOnError

' navigate to the new record
Me.Bookmark = .LastModified
End With

ProcExit:
Exit Sub

ProcErr:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Resume ProcExit
End Sub
=============== end code =================

efandango said:
Hi Graham, (PS I wrote this in word, so it should cut n paste nicely for
easier reading if you need to)

OK, let's see if I can make myself completely clear (bear with me here.)
I want to insert into both tables: tbl_Getrounds and its subtable
'tbl_Getround_Detail'.

The table below (tbl_Getrounds) is what holds any given single record that
I
want to selectively copy by saying, "when I copy this record, I want
Access
to generate a new Autonumber 'GetRound_ID' for the new record. But I want
to
specfiy via a combo box which (already existing) 'GetRoundPoint_ID' to
assign
it to. Once it has done that, I then want the donor records in the
subtable
to also be copied to a new subtable matching the new/chosen
'GetRoundPoint_ID' record.
So that I end up with a mirror copy of the master/child records, but
assigned to another record (via GetRoundPoint).

The name of the box that I will select the 'destination' record for is:
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID]

I hope that makes sense for you, (If only Microsoft would allow some kind
of
image transfer, then I could just paste a screenshot which once you saw it
would make perfect sense, 1st time around) meanwhile.

Table Specs for both tables below: (For now I have put just the table
fields, if you still need the indexes let me know, I didn't want to swamp
you
with 5 pages of data.)

Table: tbl_Getrounds (Master containing subtable: tbl_Getround_Detail)

Link Master Fields: txtCurrentPoint
Link Child Fields: GetRoundPoint_ID
Columns
Name Type Size
GetRound_ID (Autonumber) Long Integer 4
GetRoundPoint_ID Long Integer 4
Run_No Long Integer 4
FromStreetNameID Long Integer 4
ToStreetNameID Long Integer 4
FromGetRound Text 100
ToGetRound Text 100
From_PostCode Text 8
To_PostCode Text 8
Reason Text 150
GetRoundPoint Text 100
GetRound_Note Text 255
GetRound_SetDown Text 10
Copied Yes/No 1

Table: tbl_Getround_Detail (Linked to Master table : tbl_Getrounds)


Link Master Fields: GetRound_ID
Link Child Fields: GetRound_ID
Columns
Name Type Size
GetRound_Detail_ID (Autonumber) Long Integer 4
GetRound_ID Long Integer 4
Run_Direction Text 255
Run_waypoint Text 255
Postcode Text 8
Lat Text 30
Notmapped Yes/No 1
Run_No Long Integer 4
StreetNameID Long Integer 4



Graham Mandeno said:
Hi Eric

If I've understood you correctly, you don't want to insert ANY records
into
the parent table, only the child table.

I wasn't aware of your table names until now, and you're right, the SQL
line
*was* inserting records into the parent table (tbl_Getrounds) not the
child
table (tbl_Getround_Detail).

Also, since GetRound_Detail_ID is the primary key of that table, and an
autonumber, you should not include it in the INSERT statement, otherwise
you
will be attempting to create duplicates and nothing will happen at all!

Here is an emended version:

strSql = "INSERT INTO tbl_Getround_Detail " _
& (GetRound_ID, Run_Direction, Run_waypoint, Postcode) " _
& "SELECT " & Me.Getround_ID & " As NewID, Run_Direction, " _
& "Run_waypoint, Postcode FROM tbl_Getround_Detail " _
& "WHERE GetRound_ID = " & Me.cbo_copy_to_new_point_ID & ";"
CurrentDb.Execute strSql, dbFailOnError

This will duplicate all the child records associated with the current
GetRound_ID on the mail form and associate those copies with the new
GetRound_ID that has been selected in your combo box.

Only three fields will be copied - Run_Direction, Run_waypoint, and
Postcode. The other field (StreetNameID) will be Null, or will get its
default value if it has one. I don't know whether or not this is what
you
intend.

The code below that is dying with error 3022 is attempting to duplicate
the
parent record, which is NOT what I understand you are trying to do. If
this
IS what you require, then post back with the details of the fields in
tbl_Getrounds, including all the indexes.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

efandango said:
Graham,

I tried the code below: But still get Error 3022. I have spent a lot of
time
trying to figure out why this is so because as you suggested I have
remmed
out the line: !GetRound_ID = Me.GetRound_ID, but the code stops on this
yellow line: .Update

another thing is; the SQL line; is it for inserting the subrecords or
the
main records? the reason I ask is because I seem to have a an
instruction
saying
INSERT into the master table (tbl_Getrounds) a field that belongs in
the
subtable (tbl_Getround_Detail), the field is [GetRound_Detail_ID] and
is
the
PK for that subtable; I dunno, maybe i'm wrong, but it doesen't look
correct
in there.

The code I adapated from Allen Brownes code was done by replacing what
I
thought was the correct fields in the correct order; would you have a
look
at
my list below and tell me if the code I have now reflects the list?

Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)


have I got my fields in the SQL correctly?



This is my very latest code:
**********
'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
' !GetRound_ID = Me.GetRound_ID
'!GetRoundPoint_ID = Me.GetRoundPoint_ID
'!FromStreetNameID = Me.FromStreetNameID
' !ToStreetNameID = ToStreetNameID
From_PostCode = From_PostCode
To_PostCode = To_PostCode
'!Direction_From = Direction_From
'!Direction_To = Direction_To

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for
the
related records.
.Bookmark = .LastModified
lngID = !GetRound_ID

'Duplicate the related records: append query.
If Me.[frm_Getround_Detail].Form.RecordsetClone.RecordCount
0
Then

strSql = "INSERT INTO [tbl_Getrounds] " & "(GetRound_ID,
GetRound_Detail_ID, " & "Run_Direction, Run_waypoint, Postcode) SELECT
" &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID] &
"
As
NewID, GetRound_Detail_ID, Run_Direction, " & "Run_waypoint, Postcode
FROM
[tbl_Getround_Detail] " & "WHERE GetRound_ID = " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![GetRound_ID] & ";"
CurrentDb.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, ,
"cmdDupe_Click"
Resume Exit_Handler

**********


:

Hi Eric

I guess you are in daylight saving time now, so it must be nearly 1am!

Assuming your code is in the class module attached to the form in
question
(frm_GetRounds), all you need is the following substitutions:

lngOldID -> Me.GetRound_ID
lngNewID -> Me.cbo_copy_to_new_point_ID

And I didn't notice another error in your code: SQL doesn't
understand
"Me"
so you must remove the "Me" after the "WHERE":

& "WHERE GetRound_ID = " & Me.GetRound_ID & ";"

--
Good Luck and Good Night :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Hello Graham,

Thanks for replying.

Yes, I want to copy all the existing many-side records related to
one-side
record A and relate the copies to an already-existing one-side
record
B.

You also correctly say: Here, lngNewID is the ID of record B, which
you
wish
to select from a combo box, and lngOldID is the ID of record A,
which
is
presumably the current record on your form.

how do I get the references for lngNewID and lngOldID? Do i use
something
like this:


Record B

your version: SELECT " & lngNewID

my version: SELECT " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID]




Record A

Your version: WHERE Me.GetRound_ID = " & lngOldID

My version: WHERE Me.GetRound_ID = " &
[Forms]![frm_Runs]![frm_Getrounds].[Form]![GetRound_ID]


It's late here (blighty), and I have been on this for literally
hours...
so
I will give this a try and come back to you with a response, will
you
get
an
auto-reply to this?, or what is the best time to catch you?

regards


Eric



:

Hi Eric

I'd just like to confirm that I understand you correctly: You want
to
copy
all the existing many-side records related to one-side record A and
relate
the copies to an already-existing one-side record B. Is that
correct?

This can be done with a single SQL statement, similar to the one
you
already
have in your code:

strSql = "INSERT INTO [tbl_Getrounds] " _
& (GetRound_ID, GetRound_Detail_ID, " _
& "Run_Direction, Run_waypoint, Postcode) SELECT " _
& lngNewID & " As NewID, GetRound_Detail_ID, Run_Direction, " _
& "Run_waypoint, Postcode FROM [tbl_Getround_Detail] " _
& "WHERE Me.GetRound_ID = " & lngOldID & ";"
CurrentDb.Execute strSql, dbFailOnError

Here, lngNewID is the ID of record B, which you wish to select from
a
combo
box, and lngOldID is the ID of record A, which is presumably the
current
record on your form.

By the way, the reason you are getting error 3022 when trying to
create a
*new* record is this line of code:
!GetRound_ID = Me.GetRound_ID

You are setting the primary key of the new record you are adding to
be
the
same as the record you are copying. If GetRound_ID is an
autonumber,
then
just delete that line. Otherwise, generate a new, unique value for
GetRound_ID and assign that to !GetRound_ID.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


I am trying to copy a Master Record and its Subform Record into a
new
Master
and Subfrom Record.
Click to expand...
Click to expand...
Click to expand...
 
Oh my... Is that a light at the end of the tunnel is see?... I think we're
getting there... (This has been killing me for days now... ) :)

you may well chuckle to yourself at this, but I have been working in
parallel on a ludicrously mad 'Heath Robinson syle' multi-environment crash
inducing, multi-button, multi hidden-text boxes, multi-query,
multi-everything but the kitchen sink solution.

Mainly because just in case things didn't work out with the code, and partly
because I am (slightly better with queries and levers, than I am with syntax
heavy code. The upshot is that I very nearly... got it all working... and
then the cavelry turned up in the shape of your good self. Needless to say,
that your code works simply beautifully, and super quick!!!

I am truly greatful to you Graham, for your help, perseverance and patience.
it's 1.30am here now, and I'm exhausted but smiling...

have a sunny day.

regard


Eric











Graham Mandeno said:
Hi Eric

Ahhhh! Eureka!

Then your code must do the following:

1. Add a record to a recordset based on tbl_Getrounds.

2. Copy all the fields from the current record to the new one EXCEPT for the
autonumber (GetRound_ID) and the one you want to change (GetRoundPoint_ID).

3. Set the new GetRoundPoint_ID and save the new GetRound_ID.

4. Save the new record.

5. Execute an INSERT INTO to copy the related records.

The code below should go most of the way to doing the trick.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

============= start code ===================
Private Sub btn_Do_Copy_Click()
Dim strSql As String
Dim lngNewID As Long
Dim strFieldList As String
On Error GoTo ProcErr

'Save any 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."
GoTo ProcExit
End If

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!FromStreetNameID = FromStreetNameID
!ToStreetNameID = ToStreetNameID
!From_PostCode = From_PostCode
!To_PostCode = To_PostCode
!Direction_From = Direction_From
!Direction_To = Direction_To
'etc for other fields.

' set the new GetRoundPoint_ID
!GetRoundPoint_ID = cbo_copy_to_new_point_ID

' save the new GetRound_ID
lngNewID = !GetRound_ID
.Update

' make a list of all the child table fields *except*
' GetRound_Detail_ID and GetRound_ID
strFieldList = ", Run_Direction, Run_waypoint" _
& ", Postcode, Lat, Notmapped, Run_No, StreetNameID "
strSql = "INSERT INTO tbl_Getround_Detail " _
& "(GetRound_ID" & strFieldList & ") " _
& "SELECT " & lngNewID & " As NewID" & strFieldList _
& "FROM tbl_Getround_Detail " _
& "WHERE GetRound_ID=" & Me.GetRound_ID & ";"
CurrentDb.Execute strSql, dbFailOnError

' navigate to the new record
Me.Bookmark = .LastModified
End With

ProcExit:
Exit Sub

ProcErr:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Resume ProcExit
End Sub
=============== end code =================

efandango said:
Hi Graham, (PS I wrote this in word, so it should cut n paste nicely for
easier reading if you need to)

OK, let's see if I can make myself completely clear (bear with me here.)
I want to insert into both tables: tbl_Getrounds and its subtable
'tbl_Getround_Detail'.

The table below (tbl_Getrounds) is what holds any given single record that
I
want to selectively copy by saying, "when I copy this record, I want
Access
to generate a new Autonumber 'GetRound_ID' for the new record. But I want
to
specfiy via a combo box which (already existing) 'GetRoundPoint_ID' to
assign
it to. Once it has done that, I then want the donor records in the
subtable
to also be copied to a new subtable matching the new/chosen
'GetRoundPoint_ID' record.
So that I end up with a mirror copy of the master/child records, but
assigned to another record (via GetRoundPoint).

The name of the box that I will select the 'destination' record for is:
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID]

I hope that makes sense for you, (If only Microsoft would allow some kind
of
image transfer, then I could just paste a screenshot which once you saw it
would make perfect sense, 1st time around) meanwhile.

Table Specs for both tables below: (For now I have put just the table
fields, if you still need the indexes let me know, I didn't want to swamp
you
with 5 pages of data.)

Table: tbl_Getrounds (Master containing subtable: tbl_Getround_Detail)

Link Master Fields: txtCurrentPoint
Link Child Fields: GetRoundPoint_ID
Columns
Name Type Size
GetRound_ID (Autonumber) Long Integer 4
GetRoundPoint_ID Long Integer 4
Run_No Long Integer 4
FromStreetNameID Long Integer 4
ToStreetNameID Long Integer 4
FromGetRound Text 100
ToGetRound Text 100
From_PostCode Text 8
To_PostCode Text 8
Reason Text 150
GetRoundPoint Text 100
GetRound_Note Text 255
GetRound_SetDown Text 10
Copied Yes/No 1

Table: tbl_Getround_Detail (Linked to Master table : tbl_Getrounds)


Link Master Fields: GetRound_ID
Link Child Fields: GetRound_ID
Columns
Name Type Size
GetRound_Detail_ID (Autonumber) Long Integer 4
GetRound_ID Long Integer 4
Run_Direction Text 255
Run_waypoint Text 255
Postcode Text 8
Lat Text 30
Notmapped Yes/No 1
Run_No Long Integer 4
StreetNameID Long Integer 4



Graham Mandeno said:
Hi Eric

If I've understood you correctly, you don't want to insert ANY records
into
the parent table, only the child table.

I wasn't aware of your table names until now, and you're right, the SQL
line
*was* inserting records into the parent table (tbl_Getrounds) not the
child
table (tbl_Getround_Detail).

Also, since GetRound_Detail_ID is the primary key of that table, and an
autonumber, you should not include it in the INSERT statement, otherwise
you
will be attempting to create duplicates and nothing will happen at all!

Here is an emended version:

strSql = "INSERT INTO tbl_Getround_Detail " _
& (GetRound_ID, Run_Direction, Run_waypoint, Postcode) " _
& "SELECT " & Me.Getround_ID & " As NewID, Run_Direction, " _
& "Run_waypoint, Postcode FROM tbl_Getround_Detail " _
& "WHERE GetRound_ID = " & Me.cbo_copy_to_new_point_ID & ";"
CurrentDb.Execute strSql, dbFailOnError

This will duplicate all the child records associated with the current
GetRound_ID on the mail form and associate those copies with the new
GetRound_ID that has been selected in your combo box.

Only three fields will be copied - Run_Direction, Run_waypoint, and
Postcode. The other field (StreetNameID) will be Null, or will get its
default value if it has one. I don't know whether or not this is what
you
intend.

The code below that is dying with error 3022 is attempting to duplicate
the
parent record, which is NOT what I understand you are trying to do. If
this
IS what you require, then post back with the details of the fields in
tbl_Getrounds, including all the indexes.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

I tried the code below: But still get Error 3022. I have spent a lot of
time
trying to figure out why this is so because as you suggested I have
remmed
out the line: !GetRound_ID = Me.GetRound_ID, but the code stops on this
yellow line: .Update

another thing is; the SQL line; is it for inserting the subrecords or
the
main records? the reason I ask is because I seem to have a an
instruction
saying
INSERT into the master table (tbl_Getrounds) a field that belongs in
the
subtable (tbl_Getround_Detail), the field is [GetRound_Detail_ID] and
is
the
PK for that subtable; I dunno, maybe i'm wrong, but it doesen't look
correct
in there.

The code I adapated from Allen Brownes code was done by replacing what
I
thought was the correct fields in the correct order; would you have a
look
at
my list below and tell me if the code I have now reflects the list?

Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)


have I got my fields in the SQL correctly?



This is my very latest code:
**********
'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
' !GetRound_ID = Me.GetRound_ID
'!GetRoundPoint_ID = Me.GetRoundPoint_ID
'!FromStreetNameID = Me.FromStreetNameID
' !ToStreetNameID = ToStreetNameID
From_PostCode = From_PostCode
To_PostCode = To_PostCode
'!Direction_From = Direction_From
'!Direction_To = Direction_To

'etc for other fields.
Click to expand...
Click to expand...
Click to expand...
 
Oh my... Is that a light at the end of the tunnel is see?... I think we're
getting there... (This has been killing me for days now... ) :)

you may well chuckle to yourself at this, but I have been working in
parallel on a ludicrously mad 'Heath Robinson syle' multi-environment crash
inducing, multi-button, multi hidden-text boxes, multi-query,
multi-everything but the kitchen sink solution.

Mainly because just in case things didn't work out with the code, and partly
because I am (slightly better with queries and levers, than I am with syntax
heavy code. The upshot is that I very nearly... got it all working... and
then the cavelry turned up in the shape of your good self. Needless to say,
that your code works simply beautifully, and super quick!!!

I am truly greatful to you Graham, for your help, perseverance and patience.
it's 1.30am here now, and I'm exhausted but smiling...

have a sunny day.

regard


Eric











Graham Mandeno said:
Hi Eric

Ahhhh! Eureka!

Then your code must do the following:

1. Add a record to a recordset based on tbl_Getrounds.

2. Copy all the fields from the current record to the new one EXCEPT for the
autonumber (GetRound_ID) and the one you want to change (GetRoundPoint_ID).

3. Set the new GetRoundPoint_ID and save the new GetRound_ID.

4. Save the new record.

5. Execute an INSERT INTO to copy the related records.

The code below should go most of the way to doing the trick.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

============= start code ===================
Private Sub btn_Do_Copy_Click()
Dim strSql As String
Dim lngNewID As Long
Dim strFieldList As String
On Error GoTo ProcErr

'Save any 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."
GoTo ProcExit
End If

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!FromStreetNameID = FromStreetNameID
!ToStreetNameID = ToStreetNameID
!From_PostCode = From_PostCode
!To_PostCode = To_PostCode
!Direction_From = Direction_From
!Direction_To = Direction_To
'etc for other fields.

' set the new GetRoundPoint_ID
!GetRoundPoint_ID = cbo_copy_to_new_point_ID

' save the new GetRound_ID
lngNewID = !GetRound_ID
.Update

' make a list of all the child table fields *except*
' GetRound_Detail_ID and GetRound_ID
strFieldList = ", Run_Direction, Run_waypoint" _
& ", Postcode, Lat, Notmapped, Run_No, StreetNameID "
strSql = "INSERT INTO tbl_Getround_Detail " _
& "(GetRound_ID" & strFieldList & ") " _
& "SELECT " & lngNewID & " As NewID" & strFieldList _
& "FROM tbl_Getround_Detail " _
& "WHERE GetRound_ID=" & Me.GetRound_ID & ";"
CurrentDb.Execute strSql, dbFailOnError

' navigate to the new record
Me.Bookmark = .LastModified
End With

ProcExit:
Exit Sub

ProcErr:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Resume ProcExit
End Sub
=============== end code =================

efandango said:
Hi Graham, (PS I wrote this in word, so it should cut n paste nicely for
easier reading if you need to)

OK, let's see if I can make myself completely clear (bear with me here.)
I want to insert into both tables: tbl_Getrounds and its subtable
'tbl_Getround_Detail'.

The table below (tbl_Getrounds) is what holds any given single record that
I
want to selectively copy by saying, "when I copy this record, I want
Access
to generate a new Autonumber 'GetRound_ID' for the new record. But I want
to
specfiy via a combo box which (already existing) 'GetRoundPoint_ID' to
assign
it to. Once it has done that, I then want the donor records in the
subtable
to also be copied to a new subtable matching the new/chosen
'GetRoundPoint_ID' record.
So that I end up with a mirror copy of the master/child records, but
assigned to another record (via GetRoundPoint).

The name of the box that I will select the 'destination' record for is:
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID]

I hope that makes sense for you, (If only Microsoft would allow some kind
of
image transfer, then I could just paste a screenshot which once you saw it
would make perfect sense, 1st time around) meanwhile.

Table Specs for both tables below: (For now I have put just the table
fields, if you still need the indexes let me know, I didn't want to swamp
you
with 5 pages of data.)

Table: tbl_Getrounds (Master containing subtable: tbl_Getround_Detail)

Link Master Fields: txtCurrentPoint
Link Child Fields: GetRoundPoint_ID
Columns
Name Type Size
GetRound_ID (Autonumber) Long Integer 4
GetRoundPoint_ID Long Integer 4
Run_No Long Integer 4
FromStreetNameID Long Integer 4
ToStreetNameID Long Integer 4
FromGetRound Text 100
ToGetRound Text 100
From_PostCode Text 8
To_PostCode Text 8
Reason Text 150
GetRoundPoint Text 100
GetRound_Note Text 255
GetRound_SetDown Text 10
Copied Yes/No 1

Table: tbl_Getround_Detail (Linked to Master table : tbl_Getrounds)


Link Master Fields: GetRound_ID
Link Child Fields: GetRound_ID
Columns
Name Type Size
GetRound_Detail_ID (Autonumber) Long Integer 4
GetRound_ID Long Integer 4
Run_Direction Text 255
Run_waypoint Text 255
Postcode Text 8
Lat Text 30
Notmapped Yes/No 1
Run_No Long Integer 4
StreetNameID Long Integer 4



Graham Mandeno said:
Hi Eric

If I've understood you correctly, you don't want to insert ANY records
into
the parent table, only the child table.

I wasn't aware of your table names until now, and you're right, the SQL
line
*was* inserting records into the parent table (tbl_Getrounds) not the
child
table (tbl_Getround_Detail).

Also, since GetRound_Detail_ID is the primary key of that table, and an
autonumber, you should not include it in the INSERT statement, otherwise
you
will be attempting to create duplicates and nothing will happen at all!

Here is an emended version:

strSql = "INSERT INTO tbl_Getround_Detail " _
& (GetRound_ID, Run_Direction, Run_waypoint, Postcode) " _
& "SELECT " & Me.Getround_ID & " As NewID, Run_Direction, " _
& "Run_waypoint, Postcode FROM tbl_Getround_Detail " _
& "WHERE GetRound_ID = " & Me.cbo_copy_to_new_point_ID & ";"
CurrentDb.Execute strSql, dbFailOnError

This will duplicate all the child records associated with the current
GetRound_ID on the mail form and associate those copies with the new
GetRound_ID that has been selected in your combo box.

Only three fields will be copied - Run_Direction, Run_waypoint, and
Postcode. The other field (StreetNameID) will be Null, or will get its
default value if it has one. I don't know whether or not this is what
you
intend.

The code below that is dying with error 3022 is attempting to duplicate
the
parent record, which is NOT what I understand you are trying to do. If
this
IS what you require, then post back with the details of the fields in
tbl_Getrounds, including all the indexes.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

I tried the code below: But still get Error 3022. I have spent a lot of
time
trying to figure out why this is so because as you suggested I have
remmed
out the line: !GetRound_ID = Me.GetRound_ID, but the code stops on this
yellow line: .Update

another thing is; the SQL line; is it for inserting the subrecords or
the
main records? the reason I ask is because I seem to have a an
instruction
saying
INSERT into the master table (tbl_Getrounds) a field that belongs in
the
subtable (tbl_Getround_Detail), the field is [GetRound_Detail_ID] and
is
the
PK for that subtable; I dunno, maybe i'm wrong, but it doesen't look
correct
in there.

The code I adapated from Allen Brownes code was done by replacing what
I
thought was the correct fields in the correct order; would you have a
look
at
my list below and tell me if the code I have now reflects the list?

Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)


have I got my fields in the SQL correctly?



This is my very latest code:
**********
'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
' !GetRound_ID = Me.GetRound_ID
'!GetRoundPoint_ID = Me.GetRoundPoint_ID
'!FromStreetNameID = Me.FromStreetNameID
' !ToStreetNameID = ToStreetNameID
From_PostCode = From_PostCode
To_PostCode = To_PostCode
'!Direction_From = Direction_From
'!Direction_To = Direction_To

'etc for other fields.
Click to expand...
Click to expand...
Click to expand...
 
Hi Eric

I'm glad it's all working! Sleep well!

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

efandango said:
Oh my... Is that a light at the end of the tunnel is see?... I think we're
getting there... (This has been killing me for days now... ) :)

you may well chuckle to yourself at this, but I have been working in
parallel on a ludicrously mad 'Heath Robinson syle' multi-environment
crash
inducing, multi-button, multi hidden-text boxes, multi-query,
multi-everything but the kitchen sink solution.

Mainly because just in case things didn't work out with the code, and
partly
because I am (slightly better with queries and levers, than I am with
syntax
heavy code. The upshot is that I very nearly... got it all working... and
then the cavelry turned up in the shape of your good self. Needless to
say,
that your code works simply beautifully, and super quick!!!

I am truly greatful to you Graham, for your help, perseverance and
patience.
it's 1.30am here now, and I'm exhausted but smiling...

have a sunny day.

regard


Eric











Graham Mandeno said:
Hi Eric

Ahhhh! Eureka!

Then your code must do the following:

1. Add a record to a recordset based on tbl_Getrounds.

2. Copy all the fields from the current record to the new one EXCEPT for
the
autonumber (GetRound_ID) and the one you want to change
(GetRoundPoint_ID).

3. Set the new GetRoundPoint_ID and save the new GetRound_ID.

4. Save the new record.

5. Execute an INSERT INTO to copy the related records.

The code below should go most of the way to doing the trick.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

============= start code ===================
Private Sub btn_Do_Copy_Click()
Dim strSql As String
Dim lngNewID As Long
Dim strFieldList As String
On Error GoTo ProcErr

'Save any 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."
GoTo ProcExit
End If

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!FromStreetNameID = FromStreetNameID
!ToStreetNameID = ToStreetNameID
!From_PostCode = From_PostCode
!To_PostCode = To_PostCode
!Direction_From = Direction_From
!Direction_To = Direction_To
'etc for other fields.

' set the new GetRoundPoint_ID
!GetRoundPoint_ID = cbo_copy_to_new_point_ID

' save the new GetRound_ID
lngNewID = !GetRound_ID
.Update

' make a list of all the child table fields *except*
' GetRound_Detail_ID and GetRound_ID
strFieldList = ", Run_Direction, Run_waypoint" _
& ", Postcode, Lat, Notmapped, Run_No, StreetNameID "
strSql = "INSERT INTO tbl_Getround_Detail " _
& "(GetRound_ID" & strFieldList & ") " _
& "SELECT " & lngNewID & " As NewID" & strFieldList _
& "FROM tbl_Getround_Detail " _
& "WHERE GetRound_ID=" & Me.GetRound_ID & ";"
CurrentDb.Execute strSql, dbFailOnError

' navigate to the new record
Me.Bookmark = .LastModified
End With

ProcExit:
Exit Sub

ProcErr:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Resume ProcExit
End Sub
=============== end code =================

efandango said:
Hi Graham, (PS I wrote this in word, so it should cut n paste nicely
for
easier reading if you need to)

OK, let's see if I can make myself completely clear (bear with me
here.)
I want to insert into both tables: tbl_Getrounds and its subtable
'tbl_Getround_Detail'.

The table below (tbl_Getrounds) is what holds any given single record
that
I
want to selectively copy by saying, "when I copy this record, I want
Access
to generate a new Autonumber 'GetRound_ID' for the new record. But I
want
to
specfiy via a combo box which (already existing) 'GetRoundPoint_ID' to
assign
it to. Once it has done that, I then want the donor records in the
subtable
to also be copied to a new subtable matching the new/chosen
'GetRoundPoint_ID' record.
So that I end up with a mirror copy of the master/child records, but
assigned to another record (via GetRoundPoint).

The name of the box that I will select the 'destination' record for is:
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID]

I hope that makes sense for you, (If only Microsoft would allow some
kind
of
image transfer, then I could just paste a screenshot which once you saw
it
would make perfect sense, 1st time around) meanwhile.

Table Specs for both tables below: (For now I have put just the table
fields, if you still need the indexes let me know, I didn't want to
swamp
you
with 5 pages of data.)

Table: tbl_Getrounds (Master containing subtable: tbl_Getround_Detail)

Link Master Fields: txtCurrentPoint
Link Child Fields: GetRoundPoint_ID
Columns
Name Type Size
GetRound_ID (Autonumber) Long Integer 4
GetRoundPoint_ID Long Integer 4
Run_No Long Integer 4
FromStreetNameID Long Integer 4
ToStreetNameID Long Integer 4
FromGetRound Text 100
ToGetRound Text 100
From_PostCode Text 8
To_PostCode Text 8
Reason Text 150
GetRoundPoint Text 100
GetRound_Note Text 255
GetRound_SetDown Text 10
Copied Yes/No 1

Table: tbl_Getround_Detail (Linked to Master table : tbl_Getrounds)


Link Master Fields: GetRound_ID
Link Child Fields: GetRound_ID
Columns
Name Type Size
GetRound_Detail_ID (Autonumber) Long Integer 4
GetRound_ID Long Integer 4
Run_Direction Text 255
Run_waypoint Text 255
Postcode Text 8
Lat Text 30
Notmapped Yes/No 1
Run_No Long Integer 4
StreetNameID Long Integer 4



:

Hi Eric

If I've understood you correctly, you don't want to insert ANY records
into
the parent table, only the child table.

I wasn't aware of your table names until now, and you're right, the
SQL
line
*was* inserting records into the parent table (tbl_Getrounds) not the
child
table (tbl_Getround_Detail).

Also, since GetRound_Detail_ID is the primary key of that table, and
an
autonumber, you should not include it in the INSERT statement,
otherwise
you
will be attempting to create duplicates and nothing will happen at
all!

Here is an emended version:

strSql = "INSERT INTO tbl_Getround_Detail " _
& (GetRound_ID, Run_Direction, Run_waypoint, Postcode) " _
& "SELECT " & Me.Getround_ID & " As NewID, Run_Direction, " _
& "Run_waypoint, Postcode FROM tbl_Getround_Detail " _
& "WHERE GetRound_ID = " & Me.cbo_copy_to_new_point_ID & ";"
CurrentDb.Execute strSql, dbFailOnError

This will duplicate all the child records associated with the current
GetRound_ID on the mail form and associate those copies with the new
GetRound_ID that has been selected in your combo box.

Only three fields will be copied - Run_Direction, Run_waypoint, and
Postcode. The other field (StreetNameID) will be Null, or will get
its
default value if it has one. I don't know whether or not this is what
you
intend.

The code below that is dying with error 3022 is attempting to
duplicate
the
parent record, which is NOT what I understand you are trying to do.
If
this
IS what you require, then post back with the details of the fields in
tbl_Getrounds, including all the indexes.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

I tried the code below: But still get Error 3022. I have spent a lot
of
time
trying to figure out why this is so because as you suggested I have
remmed
out the line: !GetRound_ID = Me.GetRound_ID, but the code stops on
this
yellow line: .Update

another thing is; the SQL line; is it for inserting the subrecords
or
the
main records? the reason I ask is because I seem to have a an
instruction
saying
INSERT into the master table (tbl_Getrounds) a field that belongs in
the
subtable (tbl_Getround_Detail), the field is [GetRound_Detail_ID]
and
is
the
PK for that subtable; I dunno, maybe i'm wrong, but it doesen't look
correct
in there.

The code I adapated from Allen Brownes code was done by replacing
what
I
thought was the correct fields in the correct order; would you have
a
look
at
my list below and tell me if the code I have now reflects the list?

Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)


have I got my fields in the SQL correctly?



This is my very latest code:
**********
'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
' !GetRound_ID = Me.GetRound_ID
'!GetRoundPoint_ID = Me.GetRoundPoint_ID
'!FromStreetNameID = Me.FromStreetNameID
' !ToStreetNameID = ToStreetNameID
From_PostCode = From_PostCode
To_PostCode = To_PostCode
'!Direction_From = Direction_From
'!Direction_To = Direction_To

'etc for other fields.
Click to expand...
Click to expand...
Click to expand...
 
Hi Eric

I'm glad it's all working! Sleep well!

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

efandango said:
Oh my... Is that a light at the end of the tunnel is see?... I think we're
getting there... (This has been killing me for days now... ) :)

you may well chuckle to yourself at this, but I have been working in
parallel on a ludicrously mad 'Heath Robinson syle' multi-environment
crash
inducing, multi-button, multi hidden-text boxes, multi-query,
multi-everything but the kitchen sink solution.

Mainly because just in case things didn't work out with the code, and
partly
because I am (slightly better with queries and levers, than I am with
syntax
heavy code. The upshot is that I very nearly... got it all working... and
then the cavelry turned up in the shape of your good self. Needless to
say,
that your code works simply beautifully, and super quick!!!

I am truly greatful to you Graham, for your help, perseverance and
patience.
it's 1.30am here now, and I'm exhausted but smiling...

have a sunny day.

regard


Eric











Graham Mandeno said:
Hi Eric

Ahhhh! Eureka!

Then your code must do the following:

1. Add a record to a recordset based on tbl_Getrounds.

2. Copy all the fields from the current record to the new one EXCEPT for
the
autonumber (GetRound_ID) and the one you want to change
(GetRoundPoint_ID).

3. Set the new GetRoundPoint_ID and save the new GetRound_ID.

4. Save the new record.

5. Execute an INSERT INTO to copy the related records.

The code below should go most of the way to doing the trick.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

============= start code ===================
Private Sub btn_Do_Copy_Click()
Dim strSql As String
Dim lngNewID As Long
Dim strFieldList As String
On Error GoTo ProcErr

'Save any 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."
GoTo ProcExit
End If

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!FromStreetNameID = FromStreetNameID
!ToStreetNameID = ToStreetNameID
!From_PostCode = From_PostCode
!To_PostCode = To_PostCode
!Direction_From = Direction_From
!Direction_To = Direction_To
'etc for other fields.

' set the new GetRoundPoint_ID
!GetRoundPoint_ID = cbo_copy_to_new_point_ID

' save the new GetRound_ID
lngNewID = !GetRound_ID
.Update

' make a list of all the child table fields *except*
' GetRound_Detail_ID and GetRound_ID
strFieldList = ", Run_Direction, Run_waypoint" _
& ", Postcode, Lat, Notmapped, Run_No, StreetNameID "
strSql = "INSERT INTO tbl_Getround_Detail " _
& "(GetRound_ID" & strFieldList & ") " _
& "SELECT " & lngNewID & " As NewID" & strFieldList _
& "FROM tbl_Getround_Detail " _
& "WHERE GetRound_ID=" & Me.GetRound_ID & ";"
CurrentDb.Execute strSql, dbFailOnError

' navigate to the new record
Me.Bookmark = .LastModified
End With

ProcExit:
Exit Sub

ProcErr:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Resume ProcExit
End Sub
=============== end code =================

efandango said:
Hi Graham, (PS I wrote this in word, so it should cut n paste nicely
for
easier reading if you need to)

OK, let's see if I can make myself completely clear (bear with me
here.)
I want to insert into both tables: tbl_Getrounds and its subtable
'tbl_Getround_Detail'.

The table below (tbl_Getrounds) is what holds any given single record
that
I
want to selectively copy by saying, "when I copy this record, I want
Access
to generate a new Autonumber 'GetRound_ID' for the new record. But I
want
to
specfiy via a combo box which (already existing) 'GetRoundPoint_ID' to
assign
it to. Once it has done that, I then want the donor records in the
subtable
to also be copied to a new subtable matching the new/chosen
'GetRoundPoint_ID' record.
So that I end up with a mirror copy of the master/child records, but
assigned to another record (via GetRoundPoint).

The name of the box that I will select the 'destination' record for is:
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID]

I hope that makes sense for you, (If only Microsoft would allow some
kind
of
image transfer, then I could just paste a screenshot which once you saw
it
would make perfect sense, 1st time around) meanwhile.

Table Specs for both tables below: (For now I have put just the table
fields, if you still need the indexes let me know, I didn't want to
swamp
you
with 5 pages of data.)

Table: tbl_Getrounds (Master containing subtable: tbl_Getround_Detail)

Link Master Fields: txtCurrentPoint
Link Child Fields: GetRoundPoint_ID
Columns
Name Type Size
GetRound_ID (Autonumber) Long Integer 4
GetRoundPoint_ID Long Integer 4
Run_No Long Integer 4
FromStreetNameID Long Integer 4
ToStreetNameID Long Integer 4
FromGetRound Text 100
ToGetRound Text 100
From_PostCode Text 8
To_PostCode Text 8
Reason Text 150
GetRoundPoint Text 100
GetRound_Note Text 255
GetRound_SetDown Text 10
Copied Yes/No 1

Table: tbl_Getround_Detail (Linked to Master table : tbl_Getrounds)


Link Master Fields: GetRound_ID
Link Child Fields: GetRound_ID
Columns
Name Type Size
GetRound_Detail_ID (Autonumber) Long Integer 4
GetRound_ID Long Integer 4
Run_Direction Text 255
Run_waypoint Text 255
Postcode Text 8
Lat Text 30
Notmapped Yes/No 1
Run_No Long Integer 4
StreetNameID Long Integer 4



:

Hi Eric

If I've understood you correctly, you don't want to insert ANY records
into
the parent table, only the child table.

I wasn't aware of your table names until now, and you're right, the
SQL
line
*was* inserting records into the parent table (tbl_Getrounds) not the
child
table (tbl_Getround_Detail).

Also, since GetRound_Detail_ID is the primary key of that table, and
an
autonumber, you should not include it in the INSERT statement,
otherwise
you
will be attempting to create duplicates and nothing will happen at
all!

Here is an emended version:

strSql = "INSERT INTO tbl_Getround_Detail " _
& (GetRound_ID, Run_Direction, Run_waypoint, Postcode) " _
& "SELECT " & Me.Getround_ID & " As NewID, Run_Direction, " _
& "Run_waypoint, Postcode FROM tbl_Getround_Detail " _
& "WHERE GetRound_ID = " & Me.cbo_copy_to_new_point_ID & ";"
CurrentDb.Execute strSql, dbFailOnError

This will duplicate all the child records associated with the current
GetRound_ID on the mail form and associate those copies with the new
GetRound_ID that has been selected in your combo box.

Only three fields will be copied - Run_Direction, Run_waypoint, and
Postcode. The other field (StreetNameID) will be Null, or will get
its
default value if it has one. I don't know whether or not this is what
you
intend.

The code below that is dying with error 3022 is attempting to
duplicate
the
parent record, which is NOT what I understand you are trying to do.
If
this
IS what you require, then post back with the details of the fields in
tbl_Getrounds, including all the indexes.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

I tried the code below: But still get Error 3022. I have spent a lot
of
time
trying to figure out why this is so because as you suggested I have
remmed
out the line: !GetRound_ID = Me.GetRound_ID, but the code stops on
this
yellow line: .Update

another thing is; the SQL line; is it for inserting the subrecords
or
the
main records? the reason I ask is because I seem to have a an
instruction
saying
INSERT into the master table (tbl_Getrounds) a field that belongs in
the
subtable (tbl_Getround_Detail), the field is [GetRound_Detail_ID]
and
is
the
PK for that subtable; I dunno, maybe i'm wrong, but it doesen't look
correct
in there.

The code I adapated from Allen Brownes code was done by replacing
what
I
thought was the correct fields in the correct order; would you have
a
look
at
my list below and tell me if the code I have now reflects the list?

Main Form fields are:

GetRoundID (PK for this form record)
GetRoundPoint_ID (Linked to Unique ID on overall master record)
FromStreetNameID (text combo)
ToStreetNameID (text combo)
From_PostCode (Text)
To_PostCode (Text)
Direction_From (Text)
Direction_From (Text)

Sub Form fields:
GetRound_Detail_ID (PK for this form record)
GetRound_ID (Linked to Main Form)
StreetNameID (text combo)
Run_Direction (Text)
Run_waypoint (Text)
Postcode (Text)


have I got my fields in the SQL correctly?



This is my very latest code:
**********
'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
' !GetRound_ID = Me.GetRound_ID
'!GetRoundPoint_ID = Me.GetRoundPoint_ID
'!FromStreetNameID = Me.FromStreetNameID
' !ToStreetNameID = ToStreetNameID
From_PostCode = From_PostCode
To_PostCode = To_PostCode
'!Direction_From = Direction_From
'!Direction_To = Direction_To

'etc for other fields.
Click to expand...
Click to expand...
Click to expand...
 
Graham, one lsight problem...

I have one crucial missing field that has not copied across. The field is
[GetRoundPoint] and is the name of the actual point that the copied record is
going to.

I get it from this combobox: [cbo_copy_to_new_point_ID.Column(1) which is
the same combobox that gives us the reference for its ID as in:
!GetRoundPoint_ID = cbo_copy_to_new_point_ID.

How can I incorporate the additional field in the code?

regards

Eric



Graham Mandeno said:
Hi Eric

I'm glad it's all working! Sleep well!

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

efandango said:
Oh my... Is that a light at the end of the tunnel is see?... I think we're
getting there... (This has been killing me for days now... ) :)

you may well chuckle to yourself at this, but I have been working in
parallel on a ludicrously mad 'Heath Robinson syle' multi-environment
crash
inducing, multi-button, multi hidden-text boxes, multi-query,
multi-everything but the kitchen sink solution.

Mainly because just in case things didn't work out with the code, and
partly
because I am (slightly better with queries and levers, than I am with
syntax
heavy code. The upshot is that I very nearly... got it all working... and
then the cavelry turned up in the shape of your good self. Needless to
say,
that your code works simply beautifully, and super quick!!!

I am truly greatful to you Graham, for your help, perseverance and
patience.
it's 1.30am here now, and I'm exhausted but smiling...

have a sunny day.

regard


Eric











Graham Mandeno said:
Hi Eric

Ahhhh! Eureka!

Then your code must do the following:

1. Add a record to a recordset based on tbl_Getrounds.

2. Copy all the fields from the current record to the new one EXCEPT for
the
autonumber (GetRound_ID) and the one you want to change
(GetRoundPoint_ID).

3. Set the new GetRoundPoint_ID and save the new GetRound_ID.

4. Save the new record.

5. Execute an INSERT INTO to copy the related records.

The code below should go most of the way to doing the trick.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

============= start code ===================
Private Sub btn_Do_Copy_Click()
Dim strSql As String
Dim lngNewID As Long
Dim strFieldList As String
On Error GoTo ProcErr

'Save any 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."
GoTo ProcExit
End If

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!FromStreetNameID = FromStreetNameID
!ToStreetNameID = ToStreetNameID
!From_PostCode = From_PostCode
!To_PostCode = To_PostCode
!Direction_From = Direction_From
!Direction_To = Direction_To
'etc for other fields.

' set the new GetRoundPoint_ID
!GetRoundPoint_ID = cbo_copy_to_new_point_ID

' save the new GetRound_ID
lngNewID = !GetRound_ID
.Update

' make a list of all the child table fields *except*
' GetRound_Detail_ID and GetRound_ID
strFieldList = ", Run_Direction, Run_waypoint" _
& ", Postcode, Lat, Notmapped, Run_No, StreetNameID "
strSql = "INSERT INTO tbl_Getround_Detail " _
& "(GetRound_ID" & strFieldList & ") " _
& "SELECT " & lngNewID & " As NewID" & strFieldList _
& "FROM tbl_Getround_Detail " _
& "WHERE GetRound_ID=" & Me.GetRound_ID & ";"
CurrentDb.Execute strSql, dbFailOnError

' navigate to the new record
Me.Bookmark = .LastModified
End With

ProcExit:
Exit Sub

ProcErr:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Resume ProcExit
End Sub
=============== end code =================

Hi Graham, (PS I wrote this in word, so it should cut n paste nicely
for
easier reading if you need to)

OK, let's see if I can make myself completely clear (bear with me
here.)
I want to insert into both tables: tbl_Getrounds and its subtable
'tbl_Getround_Detail'.

The table below (tbl_Getrounds) is what holds any given single record
that
I
want to selectively copy by saying, "when I copy this record, I want
Access
to generate a new Autonumber 'GetRound_ID' for the new record. But I
want
to
specfiy via a combo box which (already existing) 'GetRoundPoint_ID' to
assign
it to. Once it has done that, I then want the donor records in the
subtable
to also be copied to a new subtable matching the new/chosen
'GetRoundPoint_ID' record.
So that I end up with a mirror copy of the master/child records, but
assigned to another record (via GetRoundPoint).

The name of the box that I will select the 'destination' record for is:
[Forms]![frm_Runs]![frm_Getrounds].[Form]![cbo_copy_to_new_point_ID]

I hope that makes sense for you, (If only Microsoft would allow some
kind
of
image transfer, then I could just paste a screenshot which once you saw
it
would make perfect sense, 1st time around) meanwhile.

Table Specs for both tables below: (For now I have put just the table
fields, if you still need the indexes let me know, I didn't want to
swamp
you
with 5 pages of data.)

Table: tbl_Getrounds (Master containing subtable: tbl_Getround_Detail)

Link Master Fields: txtCurrentPoint
Link Child Fields: GetRoundPoint_ID
Columns
Name Type Size
GetRound_ID (Autonumber) Long Integer 4
GetRoundPoint_ID Long Integer 4
Run_No Long Integer 4
FromStreetNameID Long Integer 4
ToStreetNameID Long Integer 4
FromGetRound Text 100
ToGetRound Text 100
From_PostCode Text 8
To_PostCode Text 8
Reason Text 150
GetRoundPoint Text 100
GetRound_Note Text 255
GetRound_SetDown Text 10
Copied Yes/No 1

Table: tbl_Getround_Detail (Linked to Master table : tbl_Getrounds)


Link Master Fields: GetRound_ID
Link Child Fields: GetRound_ID
Columns
Name Type Size
GetRound_Detail_ID (Autonumber) Long Integer 4
GetRound_ID Long Integer 4
Run_Direction Text 255
Run_waypoint Text 255
Postcode Text 8
Lat Text 30
Notmapped Yes/No 1
Run_No Long Integer 4
StreetNameID Long Integer 4



:

Hi Eric

If I've understood you correctly, you don't want to insert ANY records
into
the parent table, only the child table.

I wasn't aware of your table names until now, and you're right, the
SQL
line
*was* inserting records into the parent table (tbl_Getrounds) not the
child
table (tbl_Getround_Detail).

Also, since GetRound_Detail_ID is the primary key of that table, and
an
autonumber, you should not include it in the INSERT statement,
otherwise
you
will be attempting to create duplicates and nothing will happen at
all!

Here is an emended version:

strSql = "INSERT INTO tbl_Getround_Detail " _
& (GetRound_ID, Run_Direction, Run_waypoint, Postcode) " _
& "SELECT " & Me.Getround_ID & " As NewID, Run_Direction, " _
& "Run_waypoint, Postcode FROM tbl_Getround_Detail " _
& "WHERE GetRound_ID = " & Me.cbo_copy_to_new_point_ID & ";"
CurrentDb.Execute strSql, dbFailOnError

This will duplicate all the child records associated with the current
GetRound_ID on the mail form and associate those copies with the new
GetRound_ID that has been selected in your combo box.

Only three fields will be copied - Run_Direction, Run_waypoint, and
Postcode. The other field (StreetNameID) will be Null, or will get
its
default value if it has one. I don't know whether or not this is what
you
intend.

The code below that is dying with error 3022 is attempting to
duplicate
the
parent record, which is NOT what I understand you are trying to do.
If
this
IS what you require, then post back with the details of the fields in
tbl_Getrounds, including all the indexes.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

I tried the code below: But still get Error 3022. I have spent a lot
of
time
trying to figure out why this is so because as you suggested I have
remmed
out the line: !GetRound_ID = Me.GetRound_ID, but the code stops on
this
yellow line: .Update

another thing is; the SQL line; is it for inserting the subrecords
or
the
main records? the reason I ask is because I seem to have a an
instruction
Click to expand...
Click to expand...
Click to expand...
 
Back
Top