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.
--
Marsh
MVP [MS Access]
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?
Marshall Barton said:
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
