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

[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

 

[Graham Mandeno]

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

 

[efandango]

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

 

[Graham Mandeno]

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.

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

 

[efandango]

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.

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

 

[Graham Mandeno]

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

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

 

[efandango]

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

 

[Graham Mandeno]

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

 

[efandango]

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

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

 

[Graham Mandeno]

Hi Eric

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

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

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

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

 

[efandango]

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

Hi Eric

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

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

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

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

 

[Graham Mandeno]

Hi Eric

It sounds to me like you should not even have a GetRoundPoint (text) field
in that table. Isn't that just a copy of the text from the GetRoundPoints
table? You can always get that from a query which joins the two tables.

--
Cheers,
Graham

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

Hi Eric

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

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

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











:

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

 

[efandango]

Hi Graham,

Yes it is just a copy of exisitng text, but I didn't want to involve another
query, prefering to do it in the existing code. I'll paste the code that I
tweaked below to make it happen. But right now I have a different and very
critical problem. When the subtable data is copied, it doesnt' copy the
records in exactly the same order that they exisit in the donor recordset.

So when I go to the new target record, the fields are in a different order,
which defeats the whole object of why I am trying to do. I can't really
figure out why or where it is doing it, because in your code the rem
statement says:

' make a list of all the child table fields *except* GetRound_Detail_ID
and GetRound_ID, so I have no way of placing an orderby or something to make
it write the records in the same order that it reads them.

the field in question is: [GetRound_Detail_ID].

here is my existing code: (and below is an sql of the donor table/form for
'frm_Getround_Detail')



My latest code (note i created a var string for (strCopiedFrom)



Private Sub btn_Copy_tther_Point_Click()

Dim strSql As String
Dim lngNewID As Long
Dim strFieldList As String
Dim strCopiedFrom As String

'' declare variable for target point name
Dim strNewPointName As String
On Error GoTo ProcErr
''get the target point name

'Capture these two original values for the target form before they are
replaced by new values

strNewPointName = cbo_copy_to_new_point_ID.Column(2)
CopiedFrom = Me.GetRoundPoint

'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
!GetRoundPoint = GetRoundPoint
!Reason = Reason
!GetRound_SetDown = GetRound_SetDown
!CopiedFrom = CopiedFrom
'etc for other fields.

' set the new GetRoundPoint_ID
!GetRoundPoint_ID = cbo_copy_to_new_point_ID
!GetRoundPoint = cbo_copy_to_new_point_ID.Column(1)

' save the new GetRound_ID
lngNewID = !GetRound_ID
strNewPointName = !GetRoundPoint

.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

Me.Requery

End With



' Parent.[frm_Points].SetFocus
' Parent.[frm_Points].Form.[Run_point_Venue].SetFocus
'Parent.[frm_Points].Requery

ProcExit:
Exit Sub


ProcErr:
MsgBox "Error:" & " You forgot to choose a point to copy this turnaround
to"
'MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Resume ProcExit
End Sub





data source SQL for 'frm_Getround_Detail'

SELECT tbl_Getround_Detail.GetRound_Detail_ID,
tbl_Getround_Detail.GetRound_ID, tbl_Getround_Detail.Run_Direction,
tbl_Getround_Detail.Run_waypoint, tbl_Getround_Detail.Postcode,
tbl_Getround_Detail.Lat, tbl_Getround_Detail.Notmapped,
tbl_Getround_Detail.Run_No, tbl_Getround_Detail.StreetNameID
FROM tbl_Getrounds INNER JOIN tbl_Getround_Detail ON
tbl_Getrounds.GetRound_ID = tbl_Getround_Detail.GetRound_ID
ORDER BY tbl_Getround_Detail.GetRound_Detail_ID;


If you need anything else, just let me know.

Hi Eric

It sounds to me like you should not even have a GetRoundPoint (text) field
in that table. Isn't that just a copy of the text from the GetRoundPoints
table? You can always get that from a query which joins the two tables.

--
Cheers,
Graham

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

Hi Eric

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

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

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











:

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

 

[Graham Mandeno]

Hi Eric

You've missed the point about copying the GetRoundPoint text. The purpose
of a relational database is to access related data via a join between
primary and foreign keys. If you are going to copy fields from the primary
side to the foreign side then you defeat that purpose. You break one of the
golden rules ff normalisation and you create duplicate copies of the same
data that can get out of step.

Imagine this example: You have an order entry database (like NorthWind,
say), and every time you create an order, you copy all the customer details
from the customer record to the order record - company name, addresses,
phone numbers, etc. Now your customer calls you no notify you of a change
of phone number. You modify the customer record, but you still have dozens
of order records with the incorrect phone number. Chasing up one of those
orders could prove rather difficult!

And besides, getting fields from a related record via a join is the *easy*
option - much easier than copying data whenever you relate two records,
which would always require code.

The *only* time you should copy data is when it is "point in time" data,
which needs to be preserved as it was at the time the foreign record was
created. An example of this is the unit price of a product in an invoice
details record. Having sold a product at one price, you don't want your
invoice for that sale to change when you subsequently change the price.

Looking at the fields in your GetRound_Detail table makes me suspect that
you are actually committing this deadly sin in that table as well. Aren't
Postcode, Lat, StreetNameID, etc all fields that are associated with a
Waypoint (for which you presumably have a separate table)?

On the ordering question, records in a table have *no* inherent order - you
can think of a table as simply a bucket of data. You can impose order on a
recordset with an ORDER BY clause, but unless you have one, you should never
be surprised by the order in which records are returned.

I see your recordset does have "ORDER BY GetRound_Detail_ID", but this field
is an autonumber and its value cannot be relied on (for example, if you ever
turned on replication for your database then new records would get random
autonumber values). Also, I don't know your application well, but what
would you do if you needed to insert a GetRound_Detail record (an extra
waypoint?) between two existing ones? There would be no way to add that
record with an autonumber value that falls between two existing records.

I suggest you add your own numeric "WaypointOrder" field (or whatever name
is appropriate).

Having said all that, you should be able to force the order of creation of
records in the INSERT INTO (and therefore the order of their autonumbers) by
adding an ORDER BY clause to its SELECT statement:

....
& "WHERE GetRound_ID=" & Me.GetRound_ID _
& " ORDER BY GetRound_ID;"


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Yes it is just a copy of exisitng text, but I didn't want to involve
another
query, prefering to do it in the existing code. I'll paste the code that I
tweaked below to make it happen. But right now I have a different and very
critical problem. When the subtable data is copied, it doesnt' copy the
records in exactly the same order that they exisit in the donor recordset.

So when I go to the new target record, the fields are in a different
order,
which defeats the whole object of why I am trying to do. I can't really
figure out why or where it is doing it, because in your code the rem
statement says:

' make a list of all the child table fields *except* GetRound_Detail_ID
and GetRound_ID, so I have no way of placing an orderby or something to
make
it write the records in the same order that it reads them.

the field in question is: [GetRound_Detail_ID].

here is my existing code: (and below is an sql of the donor table/form for
'frm_Getround_Detail')



My latest code (note i created a var string for (strCopiedFrom)



Private Sub btn_Copy_tther_Point_Click()

Dim strSql As String
Dim lngNewID As Long
Dim strFieldList As String
Dim strCopiedFrom As String

'' declare variable for target point name
Dim strNewPointName As String
On Error GoTo ProcErr
''get the target point name

'Capture these two original values for the target form before they are
replaced by new values

strNewPointName = cbo_copy_to_new_point_ID.Column(2)
CopiedFrom = Me.GetRoundPoint

'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
!GetRoundPoint = GetRoundPoint
!Reason = Reason
!GetRound_SetDown = GetRound_SetDown
!CopiedFrom = CopiedFrom
'etc for other fields.

' set the new GetRoundPoint_ID
!GetRoundPoint_ID = cbo_copy_to_new_point_ID
!GetRoundPoint = cbo_copy_to_new_point_ID.Column(1)

' save the new GetRound_ID
lngNewID = !GetRound_ID
strNewPointName = !GetRoundPoint

.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

Me.Requery

End With



' Parent.[frm_Points].SetFocus
' Parent.[frm_Points].Form.[Run_point_Venue].SetFocus
'Parent.[frm_Points].Requery

ProcExit:
Exit Sub


ProcErr:
MsgBox "Error:" & " You forgot to choose a point to copy this
turnaround
to"
'MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Resume ProcExit
End Sub





data source SQL for 'frm_Getround_Detail'

SELECT tbl_Getround_Detail.GetRound_Detail_ID,
tbl_Getround_Detail.GetRound_ID, tbl_Getround_Detail.Run_Direction,
tbl_Getround_Detail.Run_waypoint, tbl_Getround_Detail.Postcode,
tbl_Getround_Detail.Lat, tbl_Getround_Detail.Notmapped,
tbl_Getround_Detail.Run_No, tbl_Getround_Detail.StreetNameID
FROM tbl_Getrounds INNER JOIN tbl_Getround_Detail ON
tbl_Getrounds.GetRound_ID = tbl_Getround_Detail.GetRound_ID
ORDER BY tbl_Getround_Detail.GetRound_Detail_ID;


If you need anything else, just let me know.

Hi Eric

It sounds to me like you should not even have a GetRoundPoint (text)
field
in that table. Isn't that just a copy of the text from the
GetRoundPoints
table? You can always get that from a query which joins the two tables.

--
Cheers,
Graham

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



:

Hi Eric

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

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

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











:

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

 

[efandango]

Hi Graham,

I hear you, and completely understand the principle point that you make
about normalisation, and records getting out of step by creating
‘non-relational’ data in new records with code as opposed to doing it the
proper way via a relational query.

It is not a practice that I will adopt for any situation, rather it is
something that I would only use in circumstances where it is warranted. I
think this is actually one of those situations, because all I want to do is
record a ‘moment in time’ of what record the original data came from. The
field in question, ‘GetRoundPoint ‘ is put into another field called,
[CopiedFrom], which is really just a simple field that reminds the user where
the original copy came from, in the event that they may they need to check
the details for accuracy, etc.

Because of the nature of its purpose, which is effectively a snapshot of the
moment, it doesn’t really form part of the relational chain, unless of course
the original name was changed, but this will not happen as the records in
question, by their nature cannot be changed.

Having said all that, you have certainly given me food for thought, to the
extent that I will try to figure a way to make this scenario
‘relational-compliant’, with the appropriate queries, etc.

On the other matter, regarding the ‘ORDER BY GetRound_Detail_ID’ situation,
that is being an autonumber and therefore cannot be relied on for the purpose
of setting the copy order of the subtable, you are of course utterly correct,
and I understand the issue; I have taken your advice and instigated an
[OrderSeq] setup, that I borrowed from another feature that I already had
elsewhere in the database; whereby I am able to add an incremental number to
each new record I created relative to any given recordset. In other words I
have:

MasterRecord 1
SubRecord [OrderSeq]
34 1
34 2
34 3

MasterRecord 33
SubRecord [OrderSeq]
79 1
79 2
79 3
79 4
79 5

....and so on, which works a charm, and has the extra, and very useful
benefit that I can move the records up or down in the recordset should they
be entered in the wrong order (a not uncommon error)

Once again Graham, I am grateful for your valued feedback and comments.

Kind Regards

Eric

Hi Eric

You've missed the point about copying the GetRoundPoint text. The purpose
of a relational database is to access related data via a join between
primary and foreign keys. If you are going to copy fields from the primary
side to the foreign side then you defeat that purpose. You break one of the
golden rules ff normalisation and you create duplicate copies of the same
data that can get out of step.

Imagine this example: You have an order entry database (like NorthWind,
say), and every time you create an order, you copy all the customer details
from the customer record to the order record - company name, addresses,
phone numbers, etc. Now your customer calls you no notify you of a change
of phone number. You modify the customer record, but you still have dozens
of order records with the incorrect phone number. Chasing up one of those
orders could prove rather difficult!

And besides, getting fields from a related record via a join is the *easy*
option - much easier than copying data whenever you relate two records,
which would always require code.

The *only* time you should copy data is when it is "point in time" data,
which needs to be preserved as it was at the time the foreign record was
created. An example of this is the unit price of a product in an invoice
details record. Having sold a product at one price, you don't want your
invoice for that sale to change when you subsequently change the price.

Looking at the fields in your GetRound_Detail table makes me suspect that
you are actually committing this deadly sin in that table as well. Aren't
Postcode, Lat, StreetNameID, etc all fields that are associated with a
Waypoint (for which you presumably have a separate table)?

On the ordering question, records in a table have *no* inherent order - you
can think of a table as simply a bucket of data. You can impose order on a
recordset with an ORDER BY clause, but unless you have one, you should never
be surprised by the order in which records are returned.

I see your recordset does have "ORDER BY GetRound_Detail_ID", but this field
is an autonumber and its value cannot be relied on (for example, if you ever
turned on replication for your database then new records would get random
autonumber values). Also, I don't know your application well, but what
would you do if you needed to insert a GetRound_Detail record (an extra
waypoint?) between two existing ones? There would be no way to add that
record with an autonumber value that falls between two existing records.

I suggest you add your own numeric "WaypointOrder" field (or whatever name
is appropriate).

Having said all that, you should be able to force the order of creation of
records in the INSERT INTO (and therefore the order of their autonumbers) by
adding an ORDER BY clause to its SELECT statement:

...
& "WHERE GetRound_ID=" & Me.GetRound_ID _
& " ORDER BY GetRound_ID;"


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Yes it is just a copy of exisitng text, but I didn't want to involve
another
query, prefering to do it in the existing code. I'll paste the code that I
tweaked below to make it happen. But right now I have a different and very
critical problem. When the subtable data is copied, it doesnt' copy the
records in exactly the same order that they exisit in the donor recordset.

So when I go to the new target record, the fields are in a different
order,
which defeats the whole object of why I am trying to do. I can't really
figure out why or where it is doing it, because in your code the rem
statement says:

' make a list of all the child table fields *except* GetRound_Detail_ID
and GetRound_ID, so I have no way of placing an orderby or something to
make
it write the records in the same order that it reads them.

the field in question is: [GetRound_Detail_ID].

here is my existing code: (and below is an sql of the donor table/form for
'frm_Getround_Detail')



My latest code (note i created a var string for (strCopiedFrom)



Private Sub btn_Copy_tther_Point_Click()

Dim strSql As String
Dim lngNewID As Long
Dim strFieldList As String
Dim strCopiedFrom As String

'' declare variable for target point name
Dim strNewPointName As String
On Error GoTo ProcErr
''get the target point name

'Capture these two original values for the target form before they are
replaced by new values

strNewPointName = cbo_copy_to_new_point_ID.Column(2)
CopiedFrom = Me.GetRoundPoint

'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
!GetRoundPoint = GetRoundPoint
!Reason = Reason
!GetRound_SetDown = GetRound_SetDown
!CopiedFrom = CopiedFrom
'etc for other fields.

' set the new GetRoundPoint_ID
!GetRoundPoint_ID = cbo_copy_to_new_point_ID
!GetRoundPoint = cbo_copy_to_new_point_ID.Column(1)

' save the new GetRound_ID
lngNewID = !GetRound_ID
strNewPointName = !GetRoundPoint

.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

Me.Requery

End With



' Parent.[frm_Points].SetFocus
' Parent.[frm_Points].Form.[Run_point_Venue].SetFocus
'Parent.[frm_Points].Requery

ProcExit:
Exit Sub


ProcErr:
MsgBox "Error:" & " You forgot to choose a point to copy this
turnaround
to"
'MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Resume ProcExit
End Sub





data source SQL for 'frm_Getround_Detail'

SELECT tbl_Getround_Detail.GetRound_Detail_ID,
tbl_Getround_Detail.GetRound_ID, tbl_Getround_Detail.Run_Direction,
tbl_Getround_Detail.Run_waypoint, tbl_Getround_Detail.Postcode,
tbl_Getround_Detail.Lat, tbl_Getround_Detail.Notmapped,
tbl_Getround_Detail.Run_No, tbl_Getround_Detail.StreetNameID
FROM tbl_Getrounds INNER JOIN tbl_Getround_Detail ON
tbl_Getrounds.GetRound_ID = tbl_Getround_Detail.GetRound_ID
ORDER BY tbl_Getround_Detail.GetRound_Detail_ID;


If you need anything else, just let me know.

Hi Eric

It sounds to me like you should not even have a GetRoundPoint (text)
field
in that table. Isn't that just a copy of the text from the
GetRoundPoints
table? You can always get that from a query which joins the two tables.

--
Cheers,
Graham

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



:

Hi Eric

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

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

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]

Hi Eric

I'm glad you've got it all working.

All the best!