best way to copy a record?

[Marshall Barton]

I guess somewhere along to road I lost track of the subform
part of the issue, but that's Ok, what you've now described
is close enough to what I was thinking it was. The code to
dup the subform record is in the subform's dup button's
Click event procedure. As far as I can tell, that part is
ok, except for the missing parent record issue.

Since you're not getting a key violation error (yet?) I'm
hoping that the new record's primary key is ok, or at least
not invalid.

Since the message indicates a Relationship violation, we
have to carefully examine the values of the new record's
field(s?) that are used as foreign keys. Somewhere, one or
more of them are not being set properly to link the new
record to an existing parent record. I suggest that you
start with the fields used in the Link Master/Child
properties and double check that the duping code sets those
fields appropriately.

If that doesn't clear things up, check the relationships
window and note all the fields that are used as foreign
keys. Then double check the code to see how those field
values are being set.

The code to dup a record is really a straightforward
operation so we're probably missing something simple here.
--
Marsh
MVP [MS Access]

the part about the 'next' cycle just came on the radar for me today, so you
didn't 'miss' anything really; it's a new 'need' of theirs. i have always
been conceptualizing this thing as involving a duplicate of a sub-form record
(for which presumably the parent record will always exist) but as my users
seem to speak a language all their own at times, things can and will 'get
lost in translation'. so the aim is apparently for the data entry clerk to be
able to 'tag' a sub-form record as worthy of appearing in the subsequent
cycle. [btw-- this has all to do with side-effects of experimental drug
treatments and their continuing from one to another 'cycle' in the life of a
clinical trial and the obligation to record what they are]

at this point in my inexpert level of sophistication, if a listbox field
called "Continuing" (meaning continuing into the next cycle) is checked
"Yes", we will want to utilize a "Duplicate" record button which has had its
properties set to "enabled" and appears in the sub-form's header to duplicate
(as far as i can gather) the values of all the fields in the sub-form into a
record that appears as a record in the sub-form for the same combination of
the 5 member PK with the exception that one of them, i.e. "Cycle" will be
equal to "Cycle + 1" as i think i wrote earlier. i don't think that there's
anything in the main form that requires being duped unless i've misunderstood
them --- they're just talking about avoiding the hassle of having to
re-record stuff from a sub-form record from one cycle to the next the long
way and to get out of doing data entry that could be done by the smarts we
build into the application.

Ahhh... I missed the part about the new record being
associated with the "next" cycle. I'll bet the missing
parent record is the next cycle record. You'll have to make
sure that exists before "dup"ing the child record. I could
have sworn that you wnated to dup the main form record, but
now it sounds like you're working on a subform record so I'm
kind of confused here. Maybe you should try explaining this
"dup" process again so I can get a better grip on what we're
trying to do here.

If you were duping the main form record, then, yes, the code
would be in the dup button's click event procedure. And,
yes, that is how you would assign the next cycle value. If
the button is in the main form and you want to dup both the
main form record and a subform record, then there's more
work to do, but I don't understand enough yet to describe
the code.

Sure Geof's code will work, but it does essentially the same
thing as my code except that you must declare all those
variables, save all the values to to the variables, then get
to the new record and copy all the variables back to the new
record's controls. Using the RecordsetClone allows you to
copy the form's control values directly to a new record
using only one third the number of lines of code.

this is really good info, mini-tutorial, that they probably just sort of
loopily covered in the 3day wonder intro to ms vba access programming i took
about a year ago and for whatever reason, i don't get 'round to having to
delve into.

what i learned about my users' needs regarding all this record duplication
effort is that when they want to duplicate it, they want it to belong to the
next value of the 'cycle' control's (which is one of the constituents of the
5 control PK), ie. to have the values belong on a record where 'cycle' =
'cycle' + 1. i'd imagine that your code would read something like "!Cycle =
Me.Cycle + 1" at the appropriate point. would i be wrong about this?

on a slightly different tack, on 10/11 before you entered this thread, Geof
Wyght opined that

"Jerome,
While on your current, old record, store the data in
variables:
strName = Me.Name.Value and so on.
Then do:
DoCmd.GoToRecord , , acNewRec
When you at your new record do:
Me.Name.Value = strName and so on.
Geof."

in absolute terms, would that work.

and third and lastly, your code.....is it intended to go into the cmdbtn's
'On Click' event as written, w/o anything else...i'm wondering how it 'knows'
to align itself w/ the PK values of the main form's? does it do that
automatically?


:
When you're viewing the code that you're working on, click
in the far left margin of a line where you want execution to
stop, The line should then be highlighted (usually in dark
red).

Then back to your form in design view, switch the form to
form view and click on whatever starts the record dup
process. When the code stops at the break point, hover the
mouse over a term in a line to get a little popup box with
the value of the item. If that isn't sufficient, hit ctrl+g
to open the immediate/debug window and type something useful
such as:
? Me!somefield
to display the value of somefield. In general, you're just
poking around to see what clues you can pick up.

There are a ton of debugging features in the VB environment
so check Help for details.

One point of caution. Do not edit your code while your form
is still open in form view. Always switch the form back to
design view before making any changes to the code. After
you make changes, be sure to compile (Debug menu) before
switching the form back to form view.


Ted wrote:
breakpoints... how does that work marsh?

:
I don't know what else to say Ted. The error message says
what it says.

I guess I would start setting some breakpoints and double
check the values of every field before the Update executes.



Ted wrote:
in looking at the relationships between the parent (treatment and toxicity)
and child (Adverse Events (child)) tables and in trying to grasp the meaning
behind your diagnosis, i see that the parent's pk's consist of just "patient
number" and "current cycle number" and these are in a one-to-many
relationship with child's "patient number" and "cycle" (which are 2 of the 5
members of the composite pk of child's -- the remaining once again being
"category", "subtype", "toxicity", and lastly "onset".

what's troubling me by your diagnosis is that as i understood it the
duplication is occuring on a sub-record that has no counterpart in the parent
table, which i believe could not be the case inasmuch as i actuated the
duplication on a form that clearly had values of the child's in the parent'
(main form) part.


:
That message means that the new record has a foreign key
that doesn't match up with the primary key of any record in
its related parent table (probably the main form's record
source table).

I would guess that either you forgot to copy that field or
you set it to some inappropriate value.


Ted wrote:
i've been doing some experimenting on the hunch that the code you generated
below would look like yours and i'm getting a "Run time error 3201 You cannot
add or change a record because a related record is required in table
"Treatment and Toxicity" " and my code window has ".Update" highlighted in
yellow.


Ted wrote:
i did some snooping around the help documentation and sort of got the
impression that that was what the wizardly software was up to.

finding myself in the vba newbie dimension, i'm not clear what all that code
means and where to plonk it (in the "click event" of my "Duplicate Record"
cmdbtn i suppose):

i mean, assuming that we're talking about my sub-form and that i have
patient_number (#), cycle (#), ae_description (txt), subtype (txt), onset
(date/time) comprising the composite PK and a litany of sundry other fields
like resolved (date/time), grade (#),....., continuing (txt) in the table
behind the sub-form and the user wants to 'clone' the active record when they
click "Duplicate Record" w/ the exception that one field's values get
substituted for another's (e.g. resolved's get put into onsets's, resolved's
are set to empty, and continuing's becomes "No" from "Yes") what does my code
begin to look like at this point?

:
Yes it goes in the button's click event and it will look
like the template code above with your own field names:

With Me.RecordsetClone
.AddNew
![patient_number] = Me.[patient_number]
!cycle = Me.cycle
. . .
!resolved = Null
!onset = Me.resolved
!continuing = "No"
. . .
.Update
Me.Bookmark = .LastModified
End With

 

[Guest]

With Me.RecordsetClone
.AddNew
![patient_number] = Me.[patient_number]

I guess somewhere along to road I lost track of the subform
part of the issue, but that's Ok, what you've now described
is close enough to what I was thinking it was. The code to
dup the subform record is in the subform's dup button's
Click event procedure. As far as I can tell, that part is
ok, except for the missing parent record issue.

Since you're not getting a key violation error (yet?) I'm
hoping that the new record's primary key is ok, or at least
not invalid.

Since the message indicates a Relationship violation, we
have to carefully examine the values of the new record's
field(s?) that are used as foreign keys. Somewhere, one or
more of them are not being set properly to link the new
record to an existing parent record. I suggest that you
start with the fields used in the Link Master/Child
properties and double check that the duping code sets those
fields appropriately.

If that doesn't clear things up, check the relationships
window and note all the fields that are used as foreign
keys. Then double check the code to see how those field
values are being set.

The code to dup a record is really a straightforward
operation so we're probably missing something simple here.
--
Marsh
MVP [MS Access]

the part about the 'next' cycle just came on the radar for me today, so you
didn't 'miss' anything really; it's a new 'need' of theirs. i have always
been conceptualizing this thing as involving a duplicate of a sub-form record
(for which presumably the parent record will always exist) but as my users
seem to speak a language all their own at times, things can and will 'get
lost in translation'. so the aim is apparently for the data entry clerk to be
able to 'tag' a sub-form record as worthy of appearing in the subsequent
cycle. [btw-- this has all to do with side-effects of experimental drug
treatments and their continuing from one to another 'cycle' in the life of a
clinical trial and the obligation to record what they are]

at this point in my inexpert level of sophistication, if a listbox field
called "Continuing" (meaning continuing into the next cycle) is checked
"Yes", we will want to utilize a "Duplicate" record button which has had its
properties set to "enabled" and appears in the sub-form's header to duplicate
(as far as i can gather) the values of all the fields in the sub-form into a
record that appears as a record in the sub-form for the same combination of
the 5 member PK with the exception that one of them, i.e. "Cycle" will be
equal to "Cycle + 1" as i think i wrote earlier. i don't think that there's
anything in the main form that requires being duped unless i've misunderstood
them --- they're just talking about avoiding the hassle of having to
re-record stuff from a sub-form record from one cycle to the next the long
way and to get out of doing data entry that could be done by the smarts we
build into the application.

Ahhh... I missed the part about the new record being
associated with the "next" cycle. I'll bet the missing
parent record is the next cycle record. You'll have to make
sure that exists before "dup"ing the child record. I could
have sworn that you wnated to dup the main form record, but
now it sounds like you're working on a subform record so I'm
kind of confused here. Maybe you should try explaining this
"dup" process again so I can get a better grip on what we're
trying to do here.

If you were duping the main form record, then, yes, the code
would be in the dup button's click event procedure. And,
yes, that is how you would assign the next cycle value. If
the button is in the main form and you want to dup both the
main form record and a subform record, then there's more
work to do, but I don't understand enough yet to describe
the code.

Sure Geof's code will work, but it does essentially the same
thing as my code except that you must declare all those
variables, save all the values to to the variables, then get
to the new record and copy all the variables back to the new
record's controls. Using the RecordsetClone allows you to
copy the form's control values directly to a new record
using only one third the number of lines of code.


Ted wrote:
this is really good info, mini-tutorial, that they probably just sort of
loopily covered in the 3day wonder intro to ms vba access programming i took
about a year ago and for whatever reason, i don't get 'round to having to
delve into.

what i learned about my users' needs regarding all this record duplication
effort is that when they want to duplicate it, they want it to belong to the
next value of the 'cycle' control's (which is one of the constituents of the
5 control PK), ie. to have the values belong on a record where 'cycle' =
'cycle' + 1. i'd imagine that your code would read something like "!Cycle =
Me.Cycle + 1" at the appropriate point. would i be wrong about this?

on a slightly different tack, on 10/11 before you entered this thread, Geof
Wyght opined that

"Jerome,
While on your current, old record, store the data in
variables:
strName = Me.Name.Value and so on.
Then do:
DoCmd.GoToRecord , , acNewRec
When you at your new record do:
Me.Name.Value = strName and so on.
Geof."

in absolute terms, would that work.

and third and lastly, your code.....is it intended to go into the cmdbtn's
'On Click' event as written, w/o anything else...i'm wondering how it 'knows'
to align itself w/ the PK values of the main form's? does it do that
automatically?


:
When you're viewing the code that you're working on, click
in the far left margin of a line where you want execution to
stop, The line should then be highlighted (usually in dark
red).

Then back to your form in design view, switch the form to
form view and click on whatever starts the record dup
process. When the code stops at the break point, hover the
mouse over a term in a line to get a little popup box with
the value of the item. If that isn't sufficient, hit ctrl+g
to open the immediate/debug window and type something useful
such as:
? Me!somefield
to display the value of somefield. In general, you're just
poking around to see what clues you can pick up.

There are a ton of debugging features in the VB environment
so check Help for details.

One point of caution. Do not edit your code while your form
is still open in form view. Always switch the form back to
design view before making any changes to the code. After
you make changes, be sure to compile (Debug menu) before
switching the form back to form view.


Ted wrote:
breakpoints... how does that work marsh?

:
I don't know what else to say Ted. The error message says
what it says.

I guess I would start setting some breakpoints and double
check the values of every field before the Update executes.



Ted wrote:
in looking at the relationships between the parent (treatment and toxicity)
and child (Adverse Events (child)) tables and in trying to grasp the meaning
behind your diagnosis, i see that the parent's pk's consist of just "patient
number" and "current cycle number" and these are in a one-to-many
relationship with child's "patient number" and "cycle" (which are 2 of the 5
members of the composite pk of child's -- the remaining once again being
"category", "subtype", "toxicity", and lastly "onset".

what's troubling me by your diagnosis is that as i understood it the
duplication is occuring on a sub-record that has no counterpart in the parent
table, which i believe could not be the case inasmuch as i actuated the
duplication on a form that clearly had values of the child's in the parent'
(main form) part.


:
That message means that the new record has a foreign key
that doesn't match up with the primary key of any record in
its related parent table (probably the main form's record
source table).

I would guess that either you forgot to copy that field or
you set it to some inappropriate value.


Ted wrote:
i've been doing some experimenting on the hunch that the code you generated
below would look like yours and i'm getting a "Run time error 3201 You cannot
add or change a record because a related record is required in table
"Treatment and Toxicity" " and my code window has ".Update" highlighted in
yellow.


Ted wrote:
i did some snooping around the help documentation and sort of got the
impression that that was what the wizardly software was up to.

finding myself in the vba newbie dimension, i'm not clear what all that code
means and where to plonk it (in the "click event" of my "Duplicate Record"
cmdbtn i suppose):

i mean, assuming that we're talking about my sub-form and that i have
patient_number (#), cycle (#), ae_description (txt), subtype (txt), onset
(date/time) comprising the composite PK and a litany of sundry other fields
like resolved (date/time), grade (#),....., continuing (txt) in the table
behind the sub-form and the user wants to 'clone' the active record when they
click "Duplicate Record" w/ the exception that one field's values get
substituted for another's (e.g. resolved's get put into onsets's, resolved's
are set to empty, and continuing's becomes "No" from "Yes") what does my code
begin to look like at this point?

:
Yes it goes in the button's click event and it will look
like the template code above with your own field names:

With Me.RecordsetClone
.AddNew
![patient_number] = Me.[patient_number]
!cycle = Me.cycle
. . .
!resolved = Null
!onset = Me.resolved
!continuing = "No"
. . .
.Update
Me.Bookmark = .LastModified
End With

 

[Guest]

before it happens once more, i've been having troubles with either my desktop
and/or the net and/or my system....seems it hiccups sometimes when i am
writing these and it issues a (false, apparently) message that the server had
a problem receiving/sending my message or it seems to kick me out of the
newgroup while preserving the part of my response that i'd been writing. so,
i'm not losing my mind (quite yet) which is an attempt at describing why you
seem to be seeing some duplicate/incomplete postings.

with that said,

we're saying that the code

With Me.RecordsetClone

.AddNew
![patient_number] = Me.[patient_number]
!cycle = Me.cycle
. . .
!resolved = Null
!onset = Me.resolved
!continuing = "No"
. . .
.Update
Me.Bookmark = .LastModified
End With

pasted into an 'On Click' [Event] area in the VBA editor would (properly
modified) do the job. there is no code before or after it that's required?

i will reprise my efforts and get back with the results.

with thanks,

ted

I guess somewhere along to road I lost track of the subform
part of the issue, but that's Ok, what you've now described
is close enough to what I was thinking it was. The code to
dup the subform record is in the subform's dup button's
Click event procedure. As far as I can tell, that part is
ok, except for the missing parent record issue.

Since you're not getting a key violation error (yet?) I'm
hoping that the new record's primary key is ok, or at least
not invalid.

Since the message indicates a Relationship violation, we
have to carefully examine the values of the new record's
field(s?) that are used as foreign keys. Somewhere, one or
more of them are not being set properly to link the new
record to an existing parent record. I suggest that you
start with the fields used in the Link Master/Child
properties and double check that the duping code sets those
fields appropriately.

If that doesn't clear things up, check the relationships
window and note all the fields that are used as foreign
keys. Then double check the code to see how those field
values are being set.

The code to dup a record is really a straightforward
operation so we're probably missing something simple here.
--
Marsh
MVP [MS Access]

the part about the 'next' cycle just came on the radar for me today, so you
didn't 'miss' anything really; it's a new 'need' of theirs. i have always
been conceptualizing this thing as involving a duplicate of a sub-form record
(for which presumably the parent record will always exist) but as my users
seem to speak a language all their own at times, things can and will 'get
lost in translation'. so the aim is apparently for the data entry clerk to be
able to 'tag' a sub-form record as worthy of appearing in the subsequent
cycle. [btw-- this has all to do with side-effects of experimental drug
treatments and their continuing from one to another 'cycle' in the life of a
clinical trial and the obligation to record what they are]

at this point in my inexpert level of sophistication, if a listbox field
called "Continuing" (meaning continuing into the next cycle) is checked
"Yes", we will want to utilize a "Duplicate" record button which has had its
properties set to "enabled" and appears in the sub-form's header to duplicate
(as far as i can gather) the values of all the fields in the sub-form into a
record that appears as a record in the sub-form for the same combination of
the 5 member PK with the exception that one of them, i.e. "Cycle" will be
equal to "Cycle + 1" as i think i wrote earlier. i don't think that there's
anything in the main form that requires being duped unless i've misunderstood
them --- they're just talking about avoiding the hassle of having to
re-record stuff from a sub-form record from one cycle to the next the long
way and to get out of doing data entry that could be done by the smarts we
build into the application.

Ahhh... I missed the part about the new record being
associated with the "next" cycle. I'll bet the missing
parent record is the next cycle record. You'll have to make
sure that exists before "dup"ing the child record. I could
have sworn that you wnated to dup the main form record, but
now it sounds like you're working on a subform record so I'm
kind of confused here. Maybe you should try explaining this
"dup" process again so I can get a better grip on what we're
trying to do here.

If you were duping the main form record, then, yes, the code
would be in the dup button's click event procedure. And,
yes, that is how you would assign the next cycle value. If
the button is in the main form and you want to dup both the
main form record and a subform record, then there's more
work to do, but I don't understand enough yet to describe
the code.

Sure Geof's code will work, but it does essentially the same
thing as my code except that you must declare all those
variables, save all the values to to the variables, then get
to the new record and copy all the variables back to the new
record's controls. Using the RecordsetClone allows you to
copy the form's control values directly to a new record
using only one third the number of lines of code.


Ted wrote:
this is really good info, mini-tutorial, that they probably just sort of
loopily covered in the 3day wonder intro to ms vba access programming i took
about a year ago and for whatever reason, i don't get 'round to having to
delve into.

what i learned about my users' needs regarding all this record duplication
effort is that when they want to duplicate it, they want it to belong to the
next value of the 'cycle' control's (which is one of the constituents of the
5 control PK), ie. to have the values belong on a record where 'cycle' =
'cycle' + 1. i'd imagine that your code would read something like "!Cycle =
Me.Cycle + 1" at the appropriate point. would i be wrong about this?

on a slightly different tack, on 10/11 before you entered this thread, Geof
Wyght opined that

"Jerome,
While on your current, old record, store the data in
variables:
strName = Me.Name.Value and so on.
Then do:
DoCmd.GoToRecord , , acNewRec
When you at your new record do:
Me.Name.Value = strName and so on.
Geof."

in absolute terms, would that work.

and third and lastly, your code.....is it intended to go into the cmdbtn's
'On Click' event as written, w/o anything else...i'm wondering how it 'knows'
to align itself w/ the PK values of the main form's? does it do that
automatically?


:
When you're viewing the code that you're working on, click
in the far left margin of a line where you want execution to
stop, The line should then be highlighted (usually in dark
red).

Then back to your form in design view, switch the form to
form view and click on whatever starts the record dup
process. When the code stops at the break point, hover the
mouse over a term in a line to get a little popup box with
the value of the item. If that isn't sufficient, hit ctrl+g
to open the immediate/debug window and type something useful
such as:
? Me!somefield
to display the value of somefield. In general, you're just
poking around to see what clues you can pick up.

There are a ton of debugging features in the VB environment
so check Help for details.

One point of caution. Do not edit your code while your form
is still open in form view. Always switch the form back to
design view before making any changes to the code. After
you make changes, be sure to compile (Debug menu) before
switching the form back to form view.


Ted wrote:
breakpoints... how does that work marsh?

:
I don't know what else to say Ted. The error message says
what it says.

I guess I would start setting some breakpoints and double
check the values of every field before the Update executes.



Ted wrote:
in looking at the relationships between the parent (treatment and toxicity)
and child (Adverse Events (child)) tables and in trying to grasp the meaning
behind your diagnosis, i see that the parent's pk's consist of just "patient
number" and "current cycle number" and these are in a one-to-many
relationship with child's "patient number" and "cycle" (which are 2 of the 5
members of the composite pk of child's -- the remaining once again being
"category", "subtype", "toxicity", and lastly "onset".

what's troubling me by your diagnosis is that as i understood it the
duplication is occuring on a sub-record that has no counterpart in the parent
table, which i believe could not be the case inasmuch as i actuated the
duplication on a form that clearly had values of the child's in the parent'
(main form) part.


:
That message means that the new record has a foreign key
that doesn't match up with the primary key of any record in
its related parent table (probably the main form's record
source table).

I would guess that either you forgot to copy that field or
you set it to some inappropriate value.


Ted wrote:
i've been doing some experimenting on the hunch that the code you generated
below would look like yours and i'm getting a "Run time error 3201 You cannot
add or change a record because a related record is required in table
"Treatment and Toxicity" " and my code window has ".Update" highlighted in
yellow.


Ted wrote:
i did some snooping around the help documentation and sort of got the
impression that that was what the wizardly software was up to.

finding myself in the vba newbie dimension, i'm not clear what all that code
means and where to plonk it (in the "click event" of my "Duplicate Record"
cmdbtn i suppose):

i mean, assuming that we're talking about my sub-form and that i have
patient_number (#), cycle (#), ae_description (txt), subtype (txt), onset
(date/time) comprising the composite PK and a litany of sundry other fields
like resolved (date/time), grade (#),....., continuing (txt) in the table
behind the sub-form and the user wants to 'clone' the active record when they
click "Duplicate Record" w/ the exception that one field's values get
substituted for another's (e.g. resolved's get put into onsets's, resolved's
are set to empty, and continuing's becomes "No" from "Yes") what does my code
begin to look like at this point?

:
Yes it goes in the button's click event and it will look
like the template code above with your own field names:

With Me.RecordsetClone
.AddNew
![patient_number] = Me.[patient_number]
!cycle = Me.cycle
. . .
!resolved = Null
!onset = Me.resolved
!continuing = "No"
. . .
.Update
Me.Bookmark = .LastModified
End With

 

[Guest]

well....here i am again.

the code i'm using is as follows (in the on click of the dupicate record
cmdbtn's):

Private Sub Duplicate_Click()
Dim Response As Integer
' pending pending
Response = MsgBox("Before proceding to duplicate this record, verify that
the Main Form's data of this" & Chr(13) & _
"patient's for the next cycle were already entered. If not, press 'Cancel',
otherwise press 'OK'.", vbOKCancel + vbCritical + vbDefaultButton2,
"Critical!")
If Response = 1 Then
With Me.RecordsetClone
.AddNew
![Patient Number] = Me.Patient_Number
![Cycle] = Me.Cycle + 1
![AE Description] = Me.AE_Description
![Subtype] = Me.Subtype
![Onset] = Me.Onset
.Update
Me.Bookmark = .LastModified
End With
Else
End If
End Sub

the error that it's returning is a '3022' -- which reads thus "The changes
you requested to the table were not successful because they would create
duplicate values in the index, pk, or relationship. Changes the data in the
field or fields that contain duplicate data, remove the index, or redefine
the index to permit duplicate entries and try again."

the table belonging to this sub-form's called "Adverse Events (child) and
its composite pk is comprised of the above 5 field names (from its design
view) in the same order listed in my vba above. i checked, and their
controls' names on the sub-form are the same as their names on the left side
of the assignment statements. [what is a little puzzling to me, though, is
that in the case of the 1st and 3rd, vba automatically placed an "_" in each'
when i selected it from the dropdown list].

i am reasonably comfortable with the existence of a cycle 2 visit for the
particular patient where this failure occurs. also, when i go into debug mode
and hover my cursor over the names of the fields to the right of the
assignment statements, i find they all correspond with what i see on the open
form with the exception of the 'Cycle' value which is reading as '0'
(zero!!!), even though i'm on the cycle value of 1 record for this
patient!!!???

can't seem to understand what's going on. could it have something to do with
the fact that in the link child/master fields definitions the names of the
'Cycle' control in the sub-form is linked with 'Current Cycle Number' (the
analogous field) in the main/parent form?

best,

ted

I guess somewhere along to road I lost track of the subform
part of the issue, but that's Ok, what you've now described
is close enough to what I was thinking it was. The code to
dup the subform record is in the subform's dup button's
Click event procedure. As far as I can tell, that part is
ok, except for the missing parent record issue.

Since you're not getting a key violation error (yet?) I'm
hoping that the new record's primary key is ok, or at least
not invalid.

Since the message indicates a Relationship violation, we
have to carefully examine the values of the new record's
field(s?) that are used as foreign keys. Somewhere, one or
more of them are not being set properly to link the new
record to an existing parent record. I suggest that you
start with the fields used in the Link Master/Child
properties and double check that the duping code sets those
fields appropriately.

If that doesn't clear things up, check the relationships
window and note all the fields that are used as foreign
keys. Then double check the code to see how those field
values are being set.

The code to dup a record is really a straightforward
operation so we're probably missing something simple here.
--
Marsh
MVP [MS Access]

the part about the 'next' cycle just came on the radar for me today, so you
didn't 'miss' anything really; it's a new 'need' of theirs. i have always
been conceptualizing this thing as involving a duplicate of a sub-form record
(for which presumably the parent record will always exist) but as my users
seem to speak a language all their own at times, things can and will 'get
lost in translation'. so the aim is apparently for the data entry clerk to be
able to 'tag' a sub-form record as worthy of appearing in the subsequent
cycle. [btw-- this has all to do with side-effects of experimental drug
treatments and their continuing from one to another 'cycle' in the life of a
clinical trial and the obligation to record what they are]

at this point in my inexpert level of sophistication, if a listbox field
called "Continuing" (meaning continuing into the next cycle) is checked
"Yes", we will want to utilize a "Duplicate" record button which has had its
properties set to "enabled" and appears in the sub-form's header to duplicate
(as far as i can gather) the values of all the fields in the sub-form into a
record that appears as a record in the sub-form for the same combination of
the 5 member PK with the exception that one of them, i.e. "Cycle" will be
equal to "Cycle + 1" as i think i wrote earlier. i don't think that there's
anything in the main form that requires being duped unless i've misunderstood
them --- they're just talking about avoiding the hassle of having to
re-record stuff from a sub-form record from one cycle to the next the long
way and to get out of doing data entry that could be done by the smarts we
build into the application.

Ahhh... I missed the part about the new record being
associated with the "next" cycle. I'll bet the missing
parent record is the next cycle record. You'll have to make
sure that exists before "dup"ing the child record. I could
have sworn that you wnated to dup the main form record, but
now it sounds like you're working on a subform record so I'm
kind of confused here. Maybe you should try explaining this
"dup" process again so I can get a better grip on what we're
trying to do here.

If you were duping the main form record, then, yes, the code
would be in the dup button's click event procedure. And,
yes, that is how you would assign the next cycle value. If
the button is in the main form and you want to dup both the
main form record and a subform record, then there's more
work to do, but I don't understand enough yet to describe
the code.

Sure Geof's code will work, but it does essentially the same
thing as my code except that you must declare all those
variables, save all the values to to the variables, then get
to the new record and copy all the variables back to the new
record's controls. Using the RecordsetClone allows you to
copy the form's control values directly to a new record
using only one third the number of lines of code.


Ted wrote:
this is really good info, mini-tutorial, that they probably just sort of
loopily covered in the 3day wonder intro to ms vba access programming i took
about a year ago and for whatever reason, i don't get 'round to having to
delve into.

what i learned about my users' needs regarding all this record duplication
effort is that when they want to duplicate it, they want it to belong to the
next value of the 'cycle' control's (which is one of the constituents of the
5 control PK), ie. to have the values belong on a record where 'cycle' =
'cycle' + 1. i'd imagine that your code would read something like "!Cycle =
Me.Cycle + 1" at the appropriate point. would i be wrong about this?

on a slightly different tack, on 10/11 before you entered this thread, Geof
Wyght opined that

"Jerome,
While on your current, old record, store the data in
variables:
strName = Me.Name.Value and so on.
Then do:
DoCmd.GoToRecord , , acNewRec
When you at your new record do:
Me.Name.Value = strName and so on.
Geof."

in absolute terms, would that work.

and third and lastly, your code.....is it intended to go into the cmdbtn's
'On Click' event as written, w/o anything else...i'm wondering how it 'knows'
to align itself w/ the PK values of the main form's? does it do that
automatically?


:
When you're viewing the code that you're working on, click
in the far left margin of a line where you want execution to
stop, The line should then be highlighted (usually in dark
red).

Then back to your form in design view, switch the form to
form view and click on whatever starts the record dup
process. When the code stops at the break point, hover the
mouse over a term in a line to get a little popup box with
the value of the item. If that isn't sufficient, hit ctrl+g
to open the immediate/debug window and type something useful
such as:
? Me!somefield
to display the value of somefield. In general, you're just
poking around to see what clues you can pick up.

There are a ton of debugging features in the VB environment
so check Help for details.

One point of caution. Do not edit your code while your form
is still open in form view. Always switch the form back to
design view before making any changes to the code. After
you make changes, be sure to compile (Debug menu) before
switching the form back to form view.


Ted wrote:
breakpoints... how does that work marsh?

:
I don't know what else to say Ted. The error message says
what it says.

I guess I would start setting some breakpoints and double
check the values of every field before the Update executes.



Ted wrote:
in looking at the relationships between the parent (treatment and toxicity)
and child (Adverse Events (child)) tables and in trying to grasp the meaning
behind your diagnosis, i see that the parent's pk's consist of just "patient
number" and "current cycle number" and these are in a one-to-many
relationship with child's "patient number" and "cycle" (which are 2 of the 5
members of the composite pk of child's -- the remaining once again being
"category", "subtype", "toxicity", and lastly "onset".

what's troubling me by your diagnosis is that as i understood it the
duplication is occuring on a sub-record that has no counterpart in the parent
table, which i believe could not be the case inasmuch as i actuated the
duplication on a form that clearly had values of the child's in the parent'
(main form) part.


:
That message means that the new record has a foreign key
that doesn't match up with the primary key of any record in
its related parent table (probably the main form's record
source table).

I would guess that either you forgot to copy that field or
you set it to some inappropriate value.


Ted wrote:
i've been doing some experimenting on the hunch that the code you generated
below would look like yours and i'm getting a "Run time error 3201 You cannot
add or change a record because a related record is required in table
"Treatment and Toxicity" " and my code window has ".Update" highlighted in
yellow.


Ted wrote:
i did some snooping around the help documentation and sort of got the
impression that that was what the wizardly software was up to.

finding myself in the vba newbie dimension, i'm not clear what all that code
means and where to plonk it (in the "click event" of my "Duplicate Record"
cmdbtn i suppose):

i mean, assuming that we're talking about my sub-form and that i have
patient_number (#), cycle (#), ae_description (txt), subtype (txt), onset
(date/time) comprising the composite PK and a litany of sundry other fields
like resolved (date/time), grade (#),....., continuing (txt) in the table
behind the sub-form and the user wants to 'clone' the active record when they
click "Duplicate Record" w/ the exception that one field's values get
substituted for another's (e.g. resolved's get put into onsets's, resolved's
are set to empty, and continuing's becomes "No" from "Yes") what does my code
begin to look like at this point?

:
Yes it goes in the button's click event and it will look
like the template code above with your own field names:

With Me.RecordsetClone
.AddNew
![patient_number] = Me.[patient_number]
!cycle = Me.cycle
. . .
!resolved = Null
!onset = Me.resolved
!continuing = "No"
. . .
.Update
Me.Bookmark = .LastModified
End With

 

[Marshall Barton]

the code i'm using is as follows (in the on click of the dupicate record
cmdbtn's):

Private Sub Duplicate_Click()
Dim Response As Integer
' pending pending
Response = MsgBox("Before proceding to duplicate this record, verify that
the Main Form's data of this" & Chr(13) & _
"patient's for the next cycle were already entered. If not, press 'Cancel',
otherwise press 'OK'.", vbOKCancel + vbCritical + vbDefaultButton2,
"Critical!")
If Response = 1 Then
With Me.RecordsetClone
.AddNew
![Patient Number] = Me.Patient_Number
![Cycle] = Me.Cycle + 1
![AE Description] = Me.AE_Description
![Subtype] = Me.Subtype
![Onset] = Me.Onset
.Update
Me.Bookmark = .LastModified
End With
Else
End If
End Sub

the error that it's returning is a '3022' -- which reads thus "The changes
you requested to the table were not successful because they would create
duplicate values in the index, pk, or relationship. Changes the data in the
field or fields that contain duplicate data, remove the index, or redefine
the index to permit duplicate entries and try again."

the table belonging to this sub-form's called "Adverse Events (child) and
its composite pk is comprised of the above 5 field names (from its design
view) in the same order listed in my vba above. i checked, and their
controls' names on the sub-form are the same as their names on the left side
of the assignment statements. [what is a little puzzling to me, though, is
that in the case of the 1st and 3rd, vba automatically placed an "_" in each'
when i selected it from the dropdown list].

i am reasonably comfortable with the existence of a cycle 2 visit for the
particular patient where this failure occurs. also, when i go into debug mode
and hover my cursor over the names of the fields to the right of the
assignment statements, i find they all correspond with what i see on the open
form with the exception of the 'Cycle' value which is reading as '0'
(zero!!!), even though i'm on the cycle value of 1 record for this
patient!!!???

can't seem to understand what's going on. could it have something to do with
the fact that in the link child/master fields definitions the names of the
'Cycle' control in the sub-form is linked with 'Current Cycle Number' (the
analogous field) in the main/parent form?

The underscore problem **might** be the issue here, although
I would have expected you to get an undefined variable
message when you wrote the code (you do have Option Explicit
at the top of the module, right).

If you're going to use names with spaces or other funky
characters in them, then you MUST enclose them in square
brackets:
![Patient Number] = Me.[Patient Number]
![AE Description] = Me.[AE Description]

Note that in this duplicate situation, I intended for you to
use the field names on both sides. It usually doesn't
matter if the control names are the same, but to be safe I
make each control name different from the name of the field
it is bound to. For example, I would use the field name
PatientNumber (no spaces) and the text box control that's
bound to that field would be named txtPatientNumber. This
way, I know when I'm referring to the field or the control.

I am concerned about Me.Cycle having a value of zero. I
don't see how that can be, unless there's a name mixup
getting in the way. If you do not have Option Explicit, and
the field/control were actually named something other than
Cycle, then a zero would be expected.

I don't think the Link Master/Child stuff is getting in the
way since we're adding a record directly to the recodset
without letting form navigation or other mechanisms get
involved.

 

[Guest]

hi marsh,

prior to your latest, i modified the Cycle statement to read

![Cycle] = Forms![Treatment and Toxicity].[Current Cycle Number] + 1

which, leaving everything else alone, seemed to work.

since your latest, i restored the simpler version of the above and entered
the spaces where there were "_"s which also seems to work.

i'm keeping my fingers xx-ed

best,

ted

the code i'm using is as follows (in the on click of the dupicate record
cmdbtn's):

Private Sub Duplicate_Click()
Dim Response As Integer
' pending pending
Response = MsgBox("Before proceding to duplicate this record, verify that
the Main Form's data of this" & Chr(13) & _
"patient's for the next cycle were already entered. If not, press 'Cancel',
otherwise press 'OK'.", vbOKCancel + vbCritical + vbDefaultButton2,
"Critical!")
If Response = 1 Then
With Me.RecordsetClone
.AddNew
![Patient Number] = Me.Patient_Number
![Cycle] = Me.Cycle + 1
![AE Description] = Me.AE_Description
![Subtype] = Me.Subtype
![Onset] = Me.Onset
.Update
Me.Bookmark = .LastModified
End With
Else
End If
End Sub

the error that it's returning is a '3022' -- which reads thus "The changes
you requested to the table were not successful because they would create
duplicate values in the index, pk, or relationship. Changes the data in the
field or fields that contain duplicate data, remove the index, or redefine
the index to permit duplicate entries and try again."

the table belonging to this sub-form's called "Adverse Events (child) and
its composite pk is comprised of the above 5 field names (from its design
view) in the same order listed in my vba above. i checked, and their
controls' names on the sub-form are the same as their names on the left side
of the assignment statements. [what is a little puzzling to me, though, is
that in the case of the 1st and 3rd, vba automatically placed an "_" in each'
when i selected it from the dropdown list].

i am reasonably comfortable with the existence of a cycle 2 visit for the
particular patient where this failure occurs. also, when i go into debug mode
and hover my cursor over the names of the fields to the right of the
assignment statements, i find they all correspond with what i see on the open
form with the exception of the 'Cycle' value which is reading as '0'
(zero!!!), even though i'm on the cycle value of 1 record for this
patient!!!???

can't seem to understand what's going on. could it have something to do with
the fact that in the link child/master fields definitions the names of the
'Cycle' control in the sub-form is linked with 'Current Cycle Number' (the
analogous field) in the main/parent form?

The underscore problem **might** be the issue here, although
I would have expected you to get an undefined variable
message when you wrote the code (you do have Option Explicit
at the top of the module, right).

If you're going to use names with spaces or other funky
characters in them, then you MUST enclose them in square
brackets:
![Patient Number] = Me.[Patient Number]
![AE Description] = Me.[AE Description]

Note that in this duplicate situation, I intended for you to
use the field names on both sides. It usually doesn't
matter if the control names are the same, but to be safe I
make each control name different from the name of the field
it is bound to. For example, I would use the field name
PatientNumber (no spaces) and the text box control that's
bound to that field would be named txtPatientNumber. This
way, I know when I'm referring to the field or the control.

I am concerned about Me.Cycle having a value of zero. I
don't see how that can be, unless there's a name mixup
getting in the way. If you do not have Option Explicit, and
the field/control were actually named something other than
Cycle, then a zero would be expected.

I don't think the Link Master/Child stuff is getting in the
way since we're adding a record directly to the recodset
without letting form navigation or other mechanisms get
involved.

 

[Marshall Barton]

Hallelujah! That's good to hear.

I'll bet it's hard to type with crossed fingers though ;-)
--
Marsh
MVP [MS Access]

prior to your latest, i modified the Cycle statement to read

![Cycle] = Forms![Treatment and Toxicity].[Current Cycle Number] + 1

which, leaving everything else alone, seemed to work.

since your latest, i restored the simpler version of the above and entered
the spaces where there were "_"s which also seems to work.

i'm keeping my fingers xx-ed

the code i'm using is as follows (in the on click of the dupicate record
cmdbtn's):

Private Sub Duplicate_Click()
Dim Response As Integer
' pending pending
Response = MsgBox("Before proceding to duplicate this record, verify that
the Main Form's data of this" & Chr(13) & _
"patient's for the next cycle were already entered. If not, press 'Cancel',
otherwise press 'OK'.", vbOKCancel + vbCritical + vbDefaultButton2,
"Critical!")
If Response = 1 Then
With Me.RecordsetClone
.AddNew
![Patient Number] = Me.Patient_Number
![Cycle] = Me.Cycle + 1
![AE Description] = Me.AE_Description
![Subtype] = Me.Subtype
![Onset] = Me.Onset
.Update
Me.Bookmark = .LastModified
End With
Else
End If
End Sub

the error that it's returning is a '3022' -- which reads thus "The changes
you requested to the table were not successful because they would create
duplicate values in the index, pk, or relationship. Changes the data in the
field or fields that contain duplicate data, remove the index, or redefine
the index to permit duplicate entries and try again."

the table belonging to this sub-form's called "Adverse Events (child) and
its composite pk is comprised of the above 5 field names (from its design
view) in the same order listed in my vba above. i checked, and their
controls' names on the sub-form are the same as their names on the left side
of the assignment statements. [what is a little puzzling to me, though, is
that in the case of the 1st and 3rd, vba automatically placed an "_" in each'
when i selected it from the dropdown list].

i am reasonably comfortable with the existence of a cycle 2 visit for the
particular patient where this failure occurs. also, when i go into debug mode
and hover my cursor over the names of the fields to the right of the
assignment statements, i find they all correspond with what i see on the open
form with the exception of the 'Cycle' value which is reading as '0'
(zero!!!), even though i'm on the cycle value of 1 record for this
patient!!!???

can't seem to understand what's going on. could it have something to do with
the fact that in the link child/master fields definitions the names of the
'Cycle' control in the sub-form is linked with 'Current Cycle Number' (the
analogous field) in the main/parent form?

The underscore problem **might** be the issue here, although
I would have expected you to get an undefined variable
message when you wrote the code (you do have Option Explicit
at the top of the module, right).

If you're going to use names with spaces or other funky
characters in them, then you MUST enclose them in square
brackets:
![Patient Number] = Me.[Patient Number]
![AE Description] = Me.[AE Description]

Note that in this duplicate situation, I intended for you to
use the field names on both sides. It usually doesn't
matter if the control names are the same, but to be safe I
make each control name different from the name of the field
it is bound to. For example, I would use the field name
PatientNumber (no spaces) and the text box control that's
bound to that field would be named txtPatientNumber. This
way, I know when I'm referring to the field or the control.

I am concerned about Me.Cycle having a value of zero. I
don't see how that can be, unless there's a name mixup
getting in the way. If you do not have Option Explicit, and
the field/control were actually named something other than
Cycle, then a zero would be expected.

I don't think the Link Master/Child stuff is getting in the
way since we're adding a record directly to the recodset
without letting form navigation or other mechanisms get
involved.

 

[Guest]

with all 8 fingers xx-ed i have to tell you it isn't over 'til it's over...

i'm now finding that what i had said isn't so after all. did i misperceive
something? i don't know, but now the only code that works is the one with the
underscores in the two fields' names and the reference to the "Current Cycle
Number"'s value in the parent form!!

too weird, but i'll take it most anyway i can get it

and the beat goes on,

ted

Hallelujah! That's good to hear.

I'll bet it's hard to type with crossed fingers though ;-)
--
Marsh
MVP [MS Access]

prior to your latest, i modified the Cycle statement to read

![Cycle] = Forms![Treatment and Toxicity].[Current Cycle Number] + 1

which, leaving everything else alone, seemed to work.

since your latest, i restored the simpler version of the above and entered
the spaces where there were "_"s which also seems to work.

i'm keeping my fingers xx-ed

Ted wrote:
the code i'm using is as follows (in the on click of the dupicate record
cmdbtn's):

Private Sub Duplicate_Click()
Dim Response As Integer
' pending pending
Response = MsgBox("Before proceding to duplicate this record, verify that
the Main Form's data of this" & Chr(13) & _
"patient's for the next cycle were already entered. If not, press 'Cancel',
otherwise press 'OK'.", vbOKCancel + vbCritical + vbDefaultButton2,
"Critical!")
If Response = 1 Then
With Me.RecordsetClone
.AddNew
![Patient Number] = Me.Patient_Number
![Cycle] = Me.Cycle + 1
![AE Description] = Me.AE_Description
![Subtype] = Me.Subtype
![Onset] = Me.Onset
.Update
Me.Bookmark = .LastModified
End With
Else
End If
End Sub

the error that it's returning is a '3022' -- which reads thus "The changes
you requested to the table were not successful because they would create
duplicate values in the index, pk, or relationship. Changes the data in the
field or fields that contain duplicate data, remove the index, or redefine
the index to permit duplicate entries and try again."

the table belonging to this sub-form's called "Adverse Events (child) and
its composite pk is comprised of the above 5 field names (from its design
view) in the same order listed in my vba above. i checked, and their
controls' names on the sub-form are the same as their names on the left side
of the assignment statements. [what is a little puzzling to me, though, is
that in the case of the 1st and 3rd, vba automatically placed an "_" in each'
when i selected it from the dropdown list].

i am reasonably comfortable with the existence of a cycle 2 visit for the
particular patient where this failure occurs. also, when i go into debug mode
and hover my cursor over the names of the fields to the right of the
assignment statements, i find they all correspond with what i see on the open
form with the exception of the 'Cycle' value which is reading as '0'
(zero!!!), even though i'm on the cycle value of 1 record for this
patient!!!???

can't seem to understand what's going on. could it have something to do with
the fact that in the link child/master fields definitions the names of the
'Cycle' control in the sub-form is linked with 'Current Cycle Number' (the
analogous field) in the main/parent form?

The underscore problem **might** be the issue here, although
I would have expected you to get an undefined variable
message when you wrote the code (you do have Option Explicit
at the top of the module, right).

If you're going to use names with spaces or other funky
characters in them, then you MUST enclose them in square
brackets:
![Patient Number] = Me.[Patient Number]
![AE Description] = Me.[AE Description]

Note that in this duplicate situation, I intended for you to
use the field names on both sides. It usually doesn't
matter if the control names are the same, but to be safe I
make each control name different from the name of the field
it is bound to. For example, I would use the field name
PatientNumber (no spaces) and the text box control that's
bound to that field would be named txtPatientNumber. This
way, I know when I'm referring to the field or the control.

I am concerned about Me.Cycle having a value of zero. I
don't see how that can be, unless there's a name mixup
getting in the way. If you do not have Option Explicit, and
the field/control were actually named something other than
Cycle, then a zero would be expected.

I don't think the Link Master/Child stuff is getting in the
way since we're adding a record directly to the recodset
without letting form navigation or other mechanisms get
involved.