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

E

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



Graham Mandeno said:
Hi Eric

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

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

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

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

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

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

have a sunny day.

regard


Eric











Graham Mandeno said:
Hi Eric

Ahhhh! Eureka!

Then your code must do the following:

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

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

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

4. Save the new record.

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

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

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

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

'Save any edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
GoTo ProcExit
End If

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

' set the new GetRoundPoint_ID
!GetRoundPoint_ID = cbo_copy_to_new_point_ID

' save the new GetRound_ID
lngNewID = !GetRound_ID
.Update

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

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

ProcExit:
Exit Sub

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

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

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

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

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

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

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

Table: tbl_Getrounds (Master containing subtable: tbl_Getround_Detail)

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

Table: tbl_Getround_Detail (Linked to Master table : tbl_Getrounds)


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



:

Hi Eric

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

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

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

Here is an emended version:

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

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

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

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

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

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

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

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

efandango said:
Graham, one lsight problem...

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

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

How can I incorporate the additional field in the code?

regards

Eric



Graham Mandeno said:
Hi Eric

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

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

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

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

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

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

have a sunny day.

regard


Eric











:

Hi Eric

Ahhhh! Eureka!

Then your code must do the following:

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

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

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

4. Save the new record.

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

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

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

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

'Save any edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
GoTo ProcExit
End If

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

' set the new GetRoundPoint_ID
!GetRoundPoint_ID = cbo_copy_to_new_point_ID

' save the new GetRound_ID
lngNewID = !GetRound_ID
.Update

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

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

ProcExit:
Exit Sub

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

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

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

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

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

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

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

Table: tbl_Getrounds (Master containing subtable:
tbl_Getround_Detail)

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

Table: tbl_Getround_Detail (Linked to Master table : tbl_Getrounds)


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



:

Hi Eric

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

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

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

Here is an emended version:

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

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

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

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

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

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

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

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

efandango said:
Graham, one lsight problem...

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

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

How can I incorporate the additional field in the code?

regards

Eric



Graham Mandeno said:
Hi Eric

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

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

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

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

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

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

have a sunny day.

regard


Eric











:

Hi Eric

Ahhhh! Eureka!

Then your code must do the following:

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

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

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

4. Save the new record.

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

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

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

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

'Save any edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
GoTo ProcExit
End If

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

' set the new GetRoundPoint_ID
!GetRoundPoint_ID = cbo_copy_to_new_point_ID

' save the new GetRound_ID
lngNewID = !GetRound_ID
.Update

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

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

ProcExit:
Exit Sub

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

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

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

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

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

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

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

Table: tbl_Getrounds (Master containing subtable:
tbl_Getround_Detail)

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

Table: tbl_Getround_Detail (Linked to Master table : tbl_Getrounds)


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



:

Hi Eric

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

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

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

Here is an emended version:

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

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

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

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

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

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

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

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



Graham Mandeno said:
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

efandango said:
Graham, one lsight problem...

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

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

How can I incorporate the additional field in the code?

regards

Eric



Graham Mandeno said:
Hi Eric

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

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

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
Click to expand...
Click to expand...
Click to expand...
 
E

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



Graham Mandeno said:
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

efandango said:
Graham, one lsight problem...

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

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

How can I incorporate the additional field in the code?

regards

Eric



Graham Mandeno said:
Hi Eric

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

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

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
Click to expand...
Click to expand...
Click to expand...
 
G

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

efandango said:
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_to_Other_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.



Graham Mandeno said:
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

efandango said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

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

efandango said:
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_to_Other_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.



Graham Mandeno said:
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

efandango said:
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
Click to expand...
Click to expand...
Click to expand...
 
E

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


Graham Mandeno said:
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

efandango said:
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_to_Other_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.



Graham Mandeno said:
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
Click to expand...
Click to expand...
Click to expand...
 
E

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


Graham Mandeno said:
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

efandango said:
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_to_Other_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.



Graham Mandeno said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Duplicate record in Form and Subform 0
synching subforms - The object doesn't contain the Automation obje 6
3 time lucky - Copy Sub records to new sub form - Kiwi in trouble 4
Duplicate record with child fields 1
Allen Browne - Help with duplicating Form & Subform! 10
Duplicate record from Combo Box - autonumber and multiple tables 3
Help with code to copy record 14
Copy Data From an ACCESS form to a WORD Template 7

Top