Writing Executable VBA Statements

[Ted]

i use a2k on a windows 2000 professional desktop.

i built an application a while back which my users have finally got 'round
to testing and i want to go over one of the features i put together as part
of a response to a 'wish list' from one. the same one wishes it would go one
better.....

the ingredients of the scenario go something like this:

there is a main form with a nested sub-form. the main one's called
'Treatment and Toxicity' and the sub-form's called 'Adverse Events (child).
the linking child fields are "Patient Number" and "Cycle". the linking master
fields are "Patient Number" and "Current Cycle Number".

there is a listbox on the subform which can be "Yes" or "No" which indicates
whether the information coded for the patient on the current cycle continues
into the next cycle. since these are adverse events in a clinical trial and
the data are being captured for each of multiple numbers of cycles in the
trial the patient goest through until he de-enrolls or finishes it, an
adverse event (e.g. 'headache', etc. etc) have continue beyond the end of the
current cycle. so, the data entry person asked if it'd be possible to
automatically duplicate a lot of the information in the subsequent cycle
which she entered for the patient's current cycle.

the code i designed to do this is below:

Private Sub Duplicate_Click()
Dim Response As Integer
' pending pending
Response = MsgBox("Before proceding to duplicate this record, verify that
the top part of this form " & Chr(13) & _
"for this patient's next cycle (i.e., MR and 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
' ignore ![Cycle] = Me.Cycle + 1
![Cycle] = Forms![Treatment and Toxicity].[Current Cycle Number] + 1
![AE Description] = Me.AE_Description
![Subtype] = Me.Subtype
![Onset] = Me.Onset
![Grade] = Me.Grade
![Serious] = Me.Serious
![Attribution] = Me.Attribution
![Action] = Me.Action
![Outcome] = Me.Outcome
![DLT] = Me.DLT
![AER Filed] = Me.AER_Filed
![ContinuingEndCycle] = "No"
' the following is intended to add a comment in the Updates field
![Updates] = "On " & Now() & " , " & LAS_GetUserName() & " duplicated this
patient's Cycle #" & Forms![Treatment and Toxicity].[Current Cycle Number] &
" record."
..Update
Me.Bookmark = .LastModified
End With
Else
End If
End Sub

according to this data entry person, it works like the proverbial charm.
however, she's apparently gotten even lazier since i developed this and now
wishes that there were some way that pressing a button would set off a series
of events which would automatically populate the next cycle for the current
patient whenever the listbox were 'Yes' without having to individualize the
requests which she currently has to do.

the 'Duplicate' command button on the subform is only 'active' when the
value of the listbox (whose name is 'Continuing') is 'Yes' as a safety
feature to minimize erroneously launching it. so the machinegun approach to
this would involve somehow sending my code through the database for the
current patient and current cycle, sifting through all records having a 'Yes'
for Me.Continuing and performing the desired action.

i'm waiting for the mis people to send their technician to try to repair the
vba help documentation -- which could take quite a while -- so i figured i'd
bring this up in this venue.

anyone?

 

[Rob Oldfield]

If I have what you're after correct, then instead of doing it using
recordset type code, it's going to be a great deal easier just to use an
append query. Search for all the records you want duplicated, add 1 to the
cycle number, and append it back on to the same table.

(...let me know if you need any more detail.)

i use a2k on a windows 2000 professional desktop.

i built an application a while back which my users have finally got 'round
to testing and i want to go over one of the features i put together as part
of a response to a 'wish list' from one. the same one wishes it would go one
better.....

the ingredients of the scenario go something like this:

there is a main form with a nested sub-form. the main one's called
'Treatment and Toxicity' and the sub-form's called 'Adverse Events (child).
the linking child fields are "Patient Number" and "Cycle". the linking master
fields are "Patient Number" and "Current Cycle Number".

there is a listbox on the subform which can be "Yes" or "No" which indicates
whether the information coded for the patient on the current cycle continues
into the next cycle. since these are adverse events in a clinical trial and
the data are being captured for each of multiple numbers of cycles in the
trial the patient goest through until he de-enrolls or finishes it, an
adverse event (e.g. 'headache', etc. etc) have continue beyond the end of the
current cycle. so, the data entry person asked if it'd be possible to
automatically duplicate a lot of the information in the subsequent cycle
which she entered for the patient's current cycle.

the code i designed to do this is below:

Private Sub Duplicate_Click()
Dim Response As Integer
' pending pending
Response = MsgBox("Before proceding to duplicate this record, verify that
the top part of this form " & Chr(13) & _
"for this patient's next cycle (i.e., MR and 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
' ignore ![Cycle] = Me.Cycle + 1
![Cycle] = Forms![Treatment and Toxicity].[Current Cycle Number] + 1
![AE Description] = Me.AE_Description
![Subtype] = Me.Subtype
![Onset] = Me.Onset
![Grade] = Me.Grade
![Serious] = Me.Serious
![Attribution] = Me.Attribution
![Action] = Me.Action
![Outcome] = Me.Outcome
![DLT] = Me.DLT
![AER Filed] = Me.AER_Filed
![ContinuingEndCycle] = "No"
' the following is intended to add a comment in the Updates field
![Updates] = "On " & Now() & " , " & LAS_GetUserName() & " duplicated this
patient's Cycle #" & Forms![Treatment and Toxicity].[Current Cycle Number] &
" record."
.Update
Me.Bookmark = .LastModified
End With
Else
End If
End Sub

according to this data entry person, it works like the proverbial charm.
however, she's apparently gotten even lazier since i developed this and now
wishes that there were some way that pressing a button would set off a series
of events which would automatically populate the next cycle for the current
patient whenever the listbox were 'Yes' without having to individualize the
requests which she currently has to do.

the 'Duplicate' command button on the subform is only 'active' when the
value of the listbox (whose name is 'Continuing') is 'Yes' as a safety
feature to minimize erroneously launching it. so the machinegun approach to
this would involve somehow sending my code through the database for the
current patient and current cycle, sifting through all records having a 'Yes'
for Me.Continuing and performing the desired action.

i'm waiting for the mis people to send their technician to try to repair the
vba help documentation -- which could take quite a while -- so i figured i'd
bring this up in this venue.

anyone?

 

[Ted]

hi rob, and first of all, thanks for the bandwidth. it sounds promising. i'm
usually reluctant to use macros and queries 'cause they seem somehow less
'high-tech' and glossy than this stuff, but the apparent ease of implementing
this is attractive.

after posting my query, it occurred to me that the user might have reason to
do a bulk duplication on more than one occasion for the same individual, e.g.
say two weeks after it, she entered some more adverse events for the same
patient which continued into the next cycle. at that point we have a
situation where i think we would want to cull just those aes which had not
been duplicated the first time for this bulk duplication. what would that
require, then, some sort of flag ('Duplicated' = 'Yes' or 'No') field which
is by default 'No' and which gets toggled to 'Yes' in the process.

-ted

If I have what you're after correct, then instead of doing it using
recordset type code, it's going to be a great deal easier just to use an
append query. Search for all the records you want duplicated, add 1 to the
cycle number, and append it back on to the same table.

(...let me know if you need any more detail.)

i use a2k on a windows 2000 professional desktop.

i built an application a while back which my users have finally got 'round
to testing and i want to go over one of the features i put together as part
of a response to a 'wish list' from one. the same one wishes it would go one
better.....

the ingredients of the scenario go something like this:

there is a main form with a nested sub-form. the main one's called
'Treatment and Toxicity' and the sub-form's called 'Adverse Events (child).
the linking child fields are "Patient Number" and "Cycle". the linking master
fields are "Patient Number" and "Current Cycle Number".

there is a listbox on the subform which can be "Yes" or "No" which indicates
whether the information coded for the patient on the current cycle continues
into the next cycle. since these are adverse events in a clinical trial and
the data are being captured for each of multiple numbers of cycles in the
trial the patient goest through until he de-enrolls or finishes it, an
adverse event (e.g. 'headache', etc. etc) have continue beyond the end of the
current cycle. so, the data entry person asked if it'd be possible to
automatically duplicate a lot of the information in the subsequent cycle
which she entered for the patient's current cycle.

the code i designed to do this is below:

Private Sub Duplicate_Click()
Dim Response As Integer
' pending pending
Response = MsgBox("Before proceding to duplicate this record, verify that
the top part of this form " & Chr(13) & _
"for this patient's next cycle (i.e., MR and 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
' ignore ![Cycle] = Me.Cycle + 1
![Cycle] = Forms![Treatment and Toxicity].[Current Cycle Number] + 1
![AE Description] = Me.AE_Description
![Subtype] = Me.Subtype
![Onset] = Me.Onset
![Grade] = Me.Grade
![Serious] = Me.Serious
![Attribution] = Me.Attribution
![Action] = Me.Action
![Outcome] = Me.Outcome
![DLT] = Me.DLT
![AER Filed] = Me.AER_Filed
![ContinuingEndCycle] = "No"
' the following is intended to add a comment in the Updates field
![Updates] = "On " & Now() & " , " & LAS_GetUserName() & " duplicated this
patient's Cycle #" & Forms![Treatment and Toxicity].[Current Cycle Number] &
" record."
.Update
Me.Bookmark = .LastModified
End With
Else
End If
End Sub

according to this data entry person, it works like the proverbial charm.
however, she's apparently gotten even lazier since i developed this and now
wishes that there were some way that pressing a button would set off a series
of events which would automatically populate the next cycle for the current
patient whenever the listbox were 'Yes' without having to individualize the
requests which she currently has to do.

the 'Duplicate' command button on the subform is only 'active' when the
value of the listbox (whose name is 'Continuing') is 'Yes' as a safety
feature to minimize erroneously launching it. so the machinegun approach to
this would involve somehow sending my code through the database for the
current patient and current cycle, sifting through all records having a 'Yes'
for Me.Continuing and performing the desired action.

i'm waiting for the mis people to send their technician to try to repair the
vba help documentation -- which could take quite a while -- so i figured i'd
bring this up in this venue.

anyone?

 

[Rob Oldfield]

I've never actually run any speed tests to attempt to compare the recordset
approach against the query approach but I'd guess that the query should
generally be faster - the Jet engine outperforming VBA. A query will,
almost certainly, be easier to understand when your user asks for a 'small
change' in a year's time as well.

And yes, I think you could certainly extend the idea to fit the additional
idea you've come up with. That would be dependent upon just ensuring that
your flag system is fully normalised.

hi rob, and first of all, thanks for the bandwidth. it sounds promising. i'm
usually reluctant to use macros and queries 'cause they seem somehow less
'high-tech' and glossy than this stuff, but the apparent ease of implementing
this is attractive.

after posting my query, it occurred to me that the user might have reason to
do a bulk duplication on more than one occasion for the same individual, e.g.
say two weeks after it, she entered some more adverse events for the same
patient which continued into the next cycle. at that point we have a
situation where i think we would want to cull just those aes which had not
been duplicated the first time for this bulk duplication. what would that
require, then, some sort of flag ('Duplicated' = 'Yes' or 'No') field which
is by default 'No' and which gets toggled to 'Yes' in the process.

-ted

If I have what you're after correct, then instead of doing it using
recordset type code, it's going to be a great deal easier just to use an
append query. Search for all the records you want duplicated, add 1 to the
cycle number, and append it back on to the same table.

(...let me know if you need any more detail.)

i use a2k on a windows 2000 professional desktop.

i built an application a while back which my users have finally got 'round
to testing and i want to go over one of the features i put together as part
of a response to a 'wish list' from one. the same one wishes it would

go
one

better.....

the ingredients of the scenario go something like this:

there is a main form with a nested sub-form. the main one's called
'Treatment and Toxicity' and the sub-form's called 'Adverse Events (child).
the linking child fields are "Patient Number" and "Cycle". the linking master
fields are "Patient Number" and "Current Cycle Number".

there is a listbox on the subform which can be "Yes" or "No" which indicates
whether the information coded for the patient on the current cycle continues
into the next cycle. since these are adverse events in a clinical

trial
and

the data are being captured for each of multiple numbers of cycles in the
trial the patient goest through until he de-enrolls or finishes it, an
adverse event (e.g. 'headache', etc. etc) have continue beyond the end

of
the

current cycle. so, the data entry person asked if it'd be possible to
automatically duplicate a lot of the information in the subsequent cycle
which she entered for the patient's current cycle.

the code i designed to do this is below:

Private Sub Duplicate_Click()
Dim Response As Integer
' pending pending
Response = MsgBox("Before proceding to duplicate this record, verify that
the top part of this form " & Chr(13) & _
"for this patient's next cycle (i.e., MR and 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
' ignore ![Cycle] = Me.Cycle + 1
![Cycle] = Forms![Treatment and Toxicity].[Current Cycle Number] + 1
![AE Description] = Me.AE_Description
![Subtype] = Me.Subtype
![Onset] = Me.Onset
![Grade] = Me.Grade
![Serious] = Me.Serious
![Attribution] = Me.Attribution
![Action] = Me.Action
![Outcome] = Me.Outcome
![DLT] = Me.DLT
![AER Filed] = Me.AER_Filed
![ContinuingEndCycle] = "No"
' the following is intended to add a comment in the Updates field
![Updates] = "On " & Now() & " , " & LAS_GetUserName() & " duplicated this
patient's Cycle #" & Forms![Treatment and Toxicity].[Current Cycle

Number]
&

" record."
.Update
Me.Bookmark = .LastModified
End With
Else
End If
End Sub

according to this data entry person, it works like the proverbial charm.
however, she's apparently gotten even lazier since i developed this

and
now

wishes that there were some way that pressing a button would set off a series
of events which would automatically populate the next cycle for the current
patient whenever the listbox were 'Yes' without having to

individualize
the

requests which she currently has to do.

the 'Duplicate' command button on the subform is only 'active' when the
value of the listbox (whose name is 'Continuing') is 'Yes' as a safety
feature to minimize erroneously launching it. so the machinegun

approach
to

this would involve somehow sending my code through the database for the
current patient and current cycle, sifting through all records having

a
'Yes'

for Me.Continuing and performing the desired action.

i'm waiting for the mis people to send their technician to try to

repair
the

vba help documentation -- which could take quite a while -- so i

figured
i'd

bring this up in this venue.

anyone?

 

[Ted]

well, that's very well and i'd like to try cobble down some real details if i
may at this point, rob...

so here's my take

say i create a cmdButton called 'Big Duplicate' or other. i place this on
the sub-form (called "Adverse Events (child)") which is itself on the main
form (called "Treatment and Toxicity").

say i also need a query or two. i think i need an select and/or an update
query and your append query. i think the select and/or update query would be
restricted to just those records having "Continuing" = "Yes" and "Duplicated"
= "No". its/their task would be to cull those records having the same
Patient Number and Cycle number as the record the user was viewing on his
screen and "update" the value of the "Duplicated" value to "Yes" and somehow
add 1 to "Cycle". then this subset would be appended back to the main table
"Adverse Events (child)" (same name as its sub-form). these would go into the
design of a macro's.

i know i could use some help getting the above straightened out.

moreover, it'd be good to know how to make the task of the macro's sifting
through the table seamless. so, my next question's got to do with how to
adjust the query/macro to ignore those records other than those corresponding
with the "Patient Number" and "Cycle" the user's looking at on the form on
the entry screen. would i put something into the 'Criteria' field in the
query or in the macro. given the names i'm using what would that look like.

thanks for the suggestion, this looks promising -- i might even just decide
to retain the original button i wrote about since it wouldn't do any harm
that i can foresee.

-ted

I've never actually run any speed tests to attempt to compare the recordset
approach against the query approach but I'd guess that the query should
generally be faster - the Jet engine outperforming VBA. A query will,
almost certainly, be easier to understand when your user asks for a 'small
change' in a year's time as well.

And yes, I think you could certainly extend the idea to fit the additional
idea you've come up with. That would be dependent upon just ensuring that
your flag system is fully normalised.

hi rob, and first of all, thanks for the bandwidth. it sounds promising. i'm
usually reluctant to use macros and queries 'cause they seem somehow less
'high-tech' and glossy than this stuff, but the apparent ease of implementing
this is attractive.

after posting my query, it occurred to me that the user might have reason to
do a bulk duplication on more than one occasion for the same individual, e.g.
say two weeks after it, she entered some more adverse events for the same
patient which continued into the next cycle. at that point we have a
situation where i think we would want to cull just those aes which had not
been duplicated the first time for this bulk duplication. what would that
require, then, some sort of flag ('Duplicated' = 'Yes' or 'No') field which
is by default 'No' and which gets toggled to 'Yes' in the process.

-ted

If I have what you're after correct, then instead of doing it using
recordset type code, it's going to be a great deal easier just to use an
append query. Search for all the records you want duplicated, add 1 to the
cycle number, and append it back on to the same table.

(...let me know if you need any more detail.)


i use a2k on a windows 2000 professional desktop.

i built an application a while back which my users have finally got 'round
to testing and i want to go over one of the features i put together as
part
of a response to a 'wish list' from one. the same one wishes it would go
one
better.....

the ingredients of the scenario go something like this:

there is a main form with a nested sub-form. the main one's called
'Treatment and Toxicity' and the sub-form's called 'Adverse Events
(child).
the linking child fields are "Patient Number" and "Cycle". the linking
master
fields are "Patient Number" and "Current Cycle Number".

there is a listbox on the subform which can be "Yes" or "No" which
indicates
whether the information coded for the patient on the current cycle
continues
into the next cycle. since these are adverse events in a clinical trial
and
the data are being captured for each of multiple numbers of cycles in the
trial the patient goest through until he de-enrolls or finishes it, an
adverse event (e.g. 'headache', etc. etc) have continue beyond the end of
the
current cycle. so, the data entry person asked if it'd be possible to
automatically duplicate a lot of the information in the subsequent cycle
which she entered for the patient's current cycle.

the code i designed to do this is below:

Private Sub Duplicate_Click()
Dim Response As Integer
' pending pending
Response = MsgBox("Before proceding to duplicate this record, verify that
the top part of this form " & Chr(13) & _
"for this patient's next cycle (i.e., MR and 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
' ignore ![Cycle] = Me.Cycle + 1
![Cycle] = Forms![Treatment and Toxicity].[Current Cycle Number] + 1
![AE Description] = Me.AE_Description
![Subtype] = Me.Subtype
![Onset] = Me.Onset
![Grade] = Me.Grade
![Serious] = Me.Serious
![Attribution] = Me.Attribution
![Action] = Me.Action
![Outcome] = Me.Outcome
![DLT] = Me.DLT
![AER Filed] = Me.AER_Filed
![ContinuingEndCycle] = "No"
' the following is intended to add a comment in the Updates field
![Updates] = "On " & Now() & " , " & LAS_GetUserName() & " duplicated this
patient's Cycle #" & Forms![Treatment and Toxicity].[Current Cycle Number]
&
" record."
.Update
Me.Bookmark = .LastModified
End With
Else
End If
End Sub

according to this data entry person, it works like the proverbial charm.
however, she's apparently gotten even lazier since i developed this and
now
wishes that there were some way that pressing a button would set off a
series
of events which would automatically populate the next cycle for the
current
patient whenever the listbox were 'Yes' without having to individualize
the
requests which she currently has to do.

the 'Duplicate' command button on the subform is only 'active' when the
value of the listbox (whose name is 'Continuing') is 'Yes' as a safety
feature to minimize erroneously launching it. so the machinegun approach
to
this would involve somehow sending my code through the database for the
current patient and current cycle, sifting through all records having a
'Yes'
for Me.Continuing and performing the desired action.

i'm waiting for the mis people to send their technician to try to repair
the
vba help documentation -- which could take quite a while -- so i figured
i'd
bring this up in this venue.

anyone?

 

[Marshall Barton]

Excuse me, but I think queries are very high tech.
Certainly doing a mass operation in a single SQL statement
is more glamorous (not to mention more efficient) than a
bunch of code.

My opinions aside, you can do an append query that won't
duplicate ones that have already been duplicated by joining
the table to itself and checking if the patient's next cycle
record doesn't already exist:

INSERT INTO table ( , , ,)
SELECT . . .
FROM table AS T1 LEFT JOIN table AS T2
ON T1.PatientID = T2.PatientID
AND T1.Cycle + 1 = T2.Cycle
WHERE T2.PatientID Is Null

hi rob, and first of all, thanks for the bandwidth. it sounds promising. i'm
usually reluctant to use macros and queries 'cause they seem somehow less
'high-tech' and glossy than this stuff, but the apparent ease of implementing
this is attractive.

after posting my query, it occurred to me that the user might have reason to
do a bulk duplication on more than one occasion for the same individual, e.g.
say two weeks after it, she entered some more adverse events for the same
patient which continued into the next cycle. at that point we have a
situation where i think we would want to cull just those aes which had not
been duplicated the first time for this bulk duplication. what would that
require, then, some sort of flag ('Duplicated' = 'Yes' or 'No') field which
is by default 'No' and which gets toggled to 'Yes' in the process.

-ted

If I have what you're after correct, then instead of doing it using
recordset type code, it's going to be a great deal easier just to use an
append query. Search for all the records you want duplicated, add 1 to the
cycle number, and append it back on to the same table.

(...let me know if you need any more detail.)

i use a2k on a windows 2000 professional desktop.

i built an application a while back which my users have finally got 'round
to testing and i want to go over one of the features i put together as part
of a response to a 'wish list' from one. the same one wishes it would go one
better.....

the ingredients of the scenario go something like this:

there is a main form with a nested sub-form. the main one's called
'Treatment and Toxicity' and the sub-form's called 'Adverse Events (child).
the linking child fields are "Patient Number" and "Cycle". the linking master
fields are "Patient Number" and "Current Cycle Number".

there is a listbox on the subform which can be "Yes" or "No" which indicates
whether the information coded for the patient on the current cycle continues
into the next cycle. since these are adverse events in a clinical trial and
the data are being captured for each of multiple numbers of cycles in the
trial the patient goest through until he de-enrolls or finishes it, an
adverse event (e.g. 'headache', etc. etc) have continue beyond the end of the
current cycle. so, the data entry person asked if it'd be possible to
automatically duplicate a lot of the information in the subsequent cycle
which she entered for the patient's current cycle.

the code i designed to do this is below:

Private Sub Duplicate_Click()
Dim Response As Integer
' pending pending
Response = MsgBox("Before proceding to duplicate this record, verify that
the top part of this form " & Chr(13) & _
"for this patient's next cycle (i.e., MR and 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
' ignore ![Cycle] = Me.Cycle + 1
![Cycle] = Forms![Treatment and Toxicity].[Current Cycle Number] + 1
![AE Description] = Me.AE_Description
![Subtype] = Me.Subtype
![Onset] = Me.Onset
![Grade] = Me.Grade
![Serious] = Me.Serious
![Attribution] = Me.Attribution
![Action] = Me.Action
![Outcome] = Me.Outcome
![DLT] = Me.DLT
![AER Filed] = Me.AER_Filed
![ContinuingEndCycle] = "No"
' the following is intended to add a comment in the Updates field
![Updates] = "On " & Now() & " , " & LAS_GetUserName() & " duplicated this
patient's Cycle #" & Forms![Treatment and Toxicity].[Current Cycle Number] &
" record."
.Update
Me.Bookmark = .LastModified
End With
Else
End If
End Sub

according to this data entry person, it works like the proverbial charm.
however, she's apparently gotten even lazier since i developed this and now
wishes that there were some way that pressing a button would set off a series
of events which would automatically populate the next cycle for the current
patient whenever the listbox were 'Yes' without having to individualize the
requests which she currently has to do.

the 'Duplicate' command button on the subform is only 'active' when the
value of the listbox (whose name is 'Continuing') is 'Yes' as a safety
feature to minimize erroneously launching it. so the machinegun approach to
this would involve somehow sending my code through the database for the
current patient and current cycle, sifting through all records having a 'Yes'
for Me.Continuing and performing the desired action.

i'm waiting for the mis people to send their technician to try to repair the
vba help documentation -- which could take quite a while -- so i figured i'd
bring this up in this venue.

anyone?

 

[Ted]

and although i'm no stranger to sql, i'm not exactly its greatest living
exponent, so with that said, can you put into english what the code doeth. i
sort of get the feeling your calling the same table by two different
'aliases' and 'stacking' one atop the other, but (naively) how does this work

ON T1.PatientID = T2.PatientID
AND T1.Cycle + 1 = T2.Cycle
WHERE T2.PatientID Is Null


tia,

-ted

Excuse me, but I think queries are very high tech.
Certainly doing a mass operation in a single SQL statement
is more glamorous (not to mention more efficient) than a
bunch of code.

My opinions aside, you can do an append query that won't
duplicate ones that have already been duplicated by joining
the table to itself and checking if the patient's next cycle
record doesn't already exist:

INSERT INTO table ( , , ,)
SELECT . . .
FROM table AS T1 LEFT JOIN table AS T2
ON T1.PatientID = T2.PatientID
AND T1.Cycle + 1 = T2.Cycle
WHERE T2.PatientID Is Null

hi rob, and first of all, thanks for the bandwidth. it sounds promising. i'm
usually reluctant to use macros and queries 'cause they seem somehow less
'high-tech' and glossy than this stuff, but the apparent ease of implementing
this is attractive.

after posting my query, it occurred to me that the user might have reason to
do a bulk duplication on more than one occasion for the same individual, e.g.
say two weeks after it, she entered some more adverse events for the same
patient which continued into the next cycle. at that point we have a
situation where i think we would want to cull just those aes which had not
been duplicated the first time for this bulk duplication. what would that
require, then, some sort of flag ('Duplicated' = 'Yes' or 'No') field which
is by default 'No' and which gets toggled to 'Yes' in the process.

-ted

If I have what you're after correct, then instead of doing it using
recordset type code, it's going to be a great deal easier just to use an
append query. Search for all the records you want duplicated, add 1 to the
cycle number, and append it back on to the same table.

(...let me know if you need any more detail.)


i use a2k on a windows 2000 professional desktop.

i built an application a while back which my users have finally got 'round
to testing and i want to go over one of the features i put together as
part
of a response to a 'wish list' from one. the same one wishes it would go
one
better.....

the ingredients of the scenario go something like this:

there is a main form with a nested sub-form. the main one's called
'Treatment and Toxicity' and the sub-form's called 'Adverse Events
(child).
the linking child fields are "Patient Number" and "Cycle". the linking
master
fields are "Patient Number" and "Current Cycle Number".

there is a listbox on the subform which can be "Yes" or "No" which
indicates
whether the information coded for the patient on the current cycle
continues
into the next cycle. since these are adverse events in a clinical trial
and
the data are being captured for each of multiple numbers of cycles in the
trial the patient goest through until he de-enrolls or finishes it, an
adverse event (e.g. 'headache', etc. etc) have continue beyond the end of
the
current cycle. so, the data entry person asked if it'd be possible to
automatically duplicate a lot of the information in the subsequent cycle
which she entered for the patient's current cycle.

the code i designed to do this is below:

Private Sub Duplicate_Click()
Dim Response As Integer
' pending pending
Response = MsgBox("Before proceding to duplicate this record, verify that
the top part of this form " & Chr(13) & _
"for this patient's next cycle (i.e., MR and 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
' ignore ![Cycle] = Me.Cycle + 1
![Cycle] = Forms![Treatment and Toxicity].[Current Cycle Number] + 1
![AE Description] = Me.AE_Description
![Subtype] = Me.Subtype
![Onset] = Me.Onset
![Grade] = Me.Grade
![Serious] = Me.Serious
![Attribution] = Me.Attribution
![Action] = Me.Action
![Outcome] = Me.Outcome
![DLT] = Me.DLT
![AER Filed] = Me.AER_Filed
![ContinuingEndCycle] = "No"
' the following is intended to add a comment in the Updates field
![Updates] = "On " & Now() & " , " & LAS_GetUserName() & " duplicated this
patient's Cycle #" & Forms![Treatment and Toxicity].[Current Cycle Number]
&
" record."
.Update
Me.Bookmark = .LastModified
End With
Else
End If
End Sub

according to this data entry person, it works like the proverbial charm.
however, she's apparently gotten even lazier since i developed this and
now
wishes that there were some way that pressing a button would set off a
series
of events which would automatically populate the next cycle for the
current
patient whenever the listbox were 'Yes' without having to individualize
the
requests which she currently has to do.

the 'Duplicate' command button on the subform is only 'active' when the
value of the listbox (whose name is 'Continuing') is 'Yes' as a safety
feature to minimize erroneously launching it. so the machinegun approach
to
this would involve somehow sending my code through the database for the
current patient and current cycle, sifting through all records having a
'Yes'
for Me.Continuing and performing the desired action.

i'm waiting for the mis people to send their technician to try to repair
the
vba help documentation -- which could take quite a while -- so i figured
i'd
bring this up in this venue.

anyone?

 

[Marshall Barton]

It connect the table to itself for each patient but from
this cyclr yo the next cycle. The WHERE clause discards all
records where the next cycle record does not exist.

I'm pretty sure you would also have to add a criteria to
deal with the Continuing allowed/nor allowed.

The point here is that it's possible to do what Rob advised
by constructing the Select part of an Insert Into query so
that only the appropriate records are duplicated regardless
of how many times the user clicks the duplicate button.
--
Marsh
MVP [MS Access]

and although i'm no stranger to sql, i'm not exactly its greatest living
exponent, so with that said, can you put into english what the code doeth. i
sort of get the feeling your calling the same table by two different
'aliases' and 'stacking' one atop the other, but (naively) how does this work

ON T1.PatientID = T2.PatientID
AND T1.Cycle + 1 = T2.Cycle
WHERE T2.PatientID Is Null

Excuse me, but I think queries are very high tech.
Certainly doing a mass operation in a single SQL statement
is more glamorous (not to mention more efficient) than a
bunch of code.

My opinions aside, you can do an append query that won't
duplicate ones that have already been duplicated by joining
the table to itself and checking if the patient's next cycle
record doesn't already exist:

INSERT INTO table ( , , ,)
SELECT . . .
FROM table AS T1 LEFT JOIN table AS T2
ON T1.PatientID = T2.PatientID
AND T1.Cycle + 1 = T2.Cycle
WHERE T2.PatientID Is Null

hi rob, and first of all, thanks for the bandwidth. it sounds promising. i'm
usually reluctant to use macros and queries 'cause they seem somehow less
'high-tech' and glossy than this stuff, but the apparent ease of implementing
this is attractive.

after posting my query, it occurred to me that the user might have reason to
do a bulk duplication on more than one occasion for the same individual, e.g.
say two weeks after it, she entered some more adverse events for the same
patient which continued into the next cycle. at that point we have a
situation where i think we would want to cull just those aes which had not
been duplicated the first time for this bulk duplication. what would that
require, then, some sort of flag ('Duplicated' = 'Yes' or 'No') field which
is by default 'No' and which gets toggled to 'Yes' in the process.


:
If I have what you're after correct, then instead of doing it using
recordset type code, it's going to be a great deal easier just to use an
append query. Search for all the records you want duplicated, add 1 to the
cycle number, and append it back on to the same table.

(...let me know if you need any more detail.)


i use a2k on a windows 2000 professional desktop.

i built an application a while back which my users have finally got 'round
to testing and i want to go over one of the features i put together as
part
of a response to a 'wish list' from one. the same one wishes it would go
one
better.....

the ingredients of the scenario go something like this:

there is a main form with a nested sub-form. the main one's called
'Treatment and Toxicity' and the sub-form's called 'Adverse Events
(child).
the linking child fields are "Patient Number" and "Cycle". the linking
master
fields are "Patient Number" and "Current Cycle Number".

there is a listbox on the subform which can be "Yes" or "No" which
indicates
whether the information coded for the patient on the current cycle
continues
into the next cycle. since these are adverse events in a clinical trial
and
the data are being captured for each of multiple numbers of cycles in the
trial the patient goest through until he de-enrolls or finishes it, an
adverse event (e.g. 'headache', etc. etc) have continue beyond the end of
the
current cycle. so, the data entry person asked if it'd be possible to
automatically duplicate a lot of the information in the subsequent cycle
which she entered for the patient's current cycle.

the code i designed to do this is below:

Private Sub Duplicate_Click()
Dim Response As Integer
' pending pending
Response = MsgBox("Before proceding to duplicate this record, verify that
the top part of this form " & Chr(13) & _
"for this patient's next cycle (i.e., MR and 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
' ignore ![Cycle] = Me.Cycle + 1
![Cycle] = Forms![Treatment and Toxicity].[Current Cycle Number] + 1
![AE Description] = Me.AE_Description
![Subtype] = Me.Subtype
![Onset] = Me.Onset
![Grade] = Me.Grade
![Serious] = Me.Serious
![Attribution] = Me.Attribution
![Action] = Me.Action
![Outcome] = Me.Outcome
![DLT] = Me.DLT
![AER Filed] = Me.AER_Filed
![ContinuingEndCycle] = "No"
' the following is intended to add a comment in the Updates field
![Updates] = "On " & Now() & " , " & LAS_GetUserName() & " duplicated this
patient's Cycle #" & Forms![Treatment and Toxicity].[Current Cycle Number]
&
" record."
.Update
Me.Bookmark = .LastModified
End With
Else
End If
End Sub

according to this data entry person, it works like the proverbial charm.
however, she's apparently gotten even lazier since i developed this and
now
wishes that there were some way that pressing a button would set off a
series
of events which would automatically populate the next cycle for the
current
patient whenever the listbox were 'Yes' without having to individualize
the
requests which she currently has to do.

the 'Duplicate' command button on the subform is only 'active' when the
value of the listbox (whose name is 'Continuing') is 'Yes' as a safety
feature to minimize erroneously launching it. so the machinegun approach
to
this would involve somehow sending my code through the database for the
current patient and current cycle, sifting through all records having a
'Yes'
for Me.Continuing and performing the desired action.

i'm waiting for the mis people to send their technician to try to repair
the
vba help documentation -- which could take quite a while -- so i figured
i'd
bring this up in this venue.

 

[Ted]

ok, i'm getting closer to nirvana here.
i'm going to want to try it out some tomorrow at the office.
one or two other possible requirement needing tending too though...
remember the two forms i spoke about, the main one and its correlated
sub-form. the original duplicate record button went on the sub-form and let
the user duplicate the cycle 'N' record for the patient the user was looking
at via the data entry screen. are we talking about creating this sql query,
giving it some name, and launching it from a button on the sub-form. would
this automatically take care of identifying the Patient Number the user's
looking at at the time the button got clicked: the functionality of this is
supposed to restrict the duplicating to just those adverse events appearing
on the cycle 'N' sub-form that are 'Continuing' = 'Yes' for the Patient
Number the user's looking at. hope i'm making sense.

-ted

It connect the table to itself for each patient but from
this cyclr yo the next cycle. The WHERE clause discards all
records where the next cycle record does not exist.

I'm pretty sure you would also have to add a criteria to
deal with the Continuing allowed/nor allowed.

The point here is that it's possible to do what Rob advised
by constructing the Select part of an Insert Into query so
that only the appropriate records are duplicated regardless
of how many times the user clicks the duplicate button.
--
Marsh
MVP [MS Access]

and although i'm no stranger to sql, i'm not exactly its greatest living
exponent, so with that said, can you put into english what the code doeth. i
sort of get the feeling your calling the same table by two different
'aliases' and 'stacking' one atop the other, but (naively) how does this work

ON T1.PatientID = T2.PatientID
AND T1.Cycle + 1 = T2.Cycle
WHERE T2.PatientID Is Null

Excuse me, but I think queries are very high tech.
Certainly doing a mass operation in a single SQL statement
is more glamorous (not to mention more efficient) than a
bunch of code.

My opinions aside, you can do an append query that won't
duplicate ones that have already been duplicated by joining
the table to itself and checking if the patient's next cycle
record doesn't already exist:

INSERT INTO table ( , , ,)
SELECT . . .
FROM table AS T1 LEFT JOIN table AS T2
ON T1.PatientID = T2.PatientID
AND T1.Cycle + 1 = T2.Cycle
WHERE T2.PatientID Is Null


Ted wrote:
hi rob, and first of all, thanks for the bandwidth. it sounds promising. i'm
usually reluctant to use macros and queries 'cause they seem somehow less
'high-tech' and glossy than this stuff, but the apparent ease of implementing
this is attractive.

after posting my query, it occurred to me that the user might have reason to
do a bulk duplication on more than one occasion for the same individual, e.g.
say two weeks after it, she entered some more adverse events for the same
patient which continued into the next cycle. at that point we have a
situation where i think we would want to cull just those aes which had not
been duplicated the first time for this bulk duplication. what would that
require, then, some sort of flag ('Duplicated' = 'Yes' or 'No') field which
is by default 'No' and which gets toggled to 'Yes' in the process.


:
If I have what you're after correct, then instead of doing it using
recordset type code, it's going to be a great deal easier just to use an
append query. Search for all the records you want duplicated, add 1 to the
cycle number, and append it back on to the same table.

(...let me know if you need any more detail.)


i use a2k on a windows 2000 professional desktop.

i built an application a while back which my users have finally got 'round
to testing and i want to go over one of the features i put together as
part
of a response to a 'wish list' from one. the same one wishes it would go
one
better.....

the ingredients of the scenario go something like this:

there is a main form with a nested sub-form. the main one's called
'Treatment and Toxicity' and the sub-form's called 'Adverse Events
(child).
the linking child fields are "Patient Number" and "Cycle". the linking
master
fields are "Patient Number" and "Current Cycle Number".

there is a listbox on the subform which can be "Yes" or "No" which
indicates
whether the information coded for the patient on the current cycle
continues
into the next cycle. since these are adverse events in a clinical trial
and
the data are being captured for each of multiple numbers of cycles in the
trial the patient goest through until he de-enrolls or finishes it, an
adverse event (e.g. 'headache', etc. etc) have continue beyond the end of
the
current cycle. so, the data entry person asked if it'd be possible to
automatically duplicate a lot of the information in the subsequent cycle
which she entered for the patient's current cycle.

the code i designed to do this is below:

Private Sub Duplicate_Click()
Dim Response As Integer
' pending pending
Response = MsgBox("Before proceding to duplicate this record, verify that
the top part of this form " & Chr(13) & _
"for this patient's next cycle (i.e., MR and 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
' ignore ![Cycle] = Me.Cycle + 1
![Cycle] = Forms![Treatment and Toxicity].[Current Cycle Number] + 1
![AE Description] = Me.AE_Description
![Subtype] = Me.Subtype
![Onset] = Me.Onset
![Grade] = Me.Grade
![Serious] = Me.Serious
![Attribution] = Me.Attribution
![Action] = Me.Action
![Outcome] = Me.Outcome
![DLT] = Me.DLT
![AER Filed] = Me.AER_Filed
![ContinuingEndCycle] = "No"
' the following is intended to add a comment in the Updates field
![Updates] = "On " & Now() & " , " & LAS_GetUserName() & " duplicated this
patient's Cycle #" & Forms![Treatment and Toxicity].[Current Cycle Number]
&
" record."
.Update
Me.Bookmark = .LastModified
End With
Else
End If
End Sub

according to this data entry person, it works like the proverbial charm.
however, she's apparently gotten even lazier since i developed this and
now
wishes that there were some way that pressing a button would set off a
series
of events which would automatically populate the next cycle for the
current
patient whenever the listbox were 'Yes' without having to individualize
the
requests which she currently has to do.

the 'Duplicate' command button on the subform is only 'active' when the
value of the listbox (whose name is 'Continuing') is 'Yes' as a safety
feature to minimize erroneously launching it. so the machinegun approach
to
this would involve somehow sending my code through the database for the
current patient and current cycle, sifting through all records having a
'Yes'
for Me.Continuing and performing the desired action.

i'm waiting for the mis people to send their technician to try to repair
the
vba help documentation -- which could take quite a while -- so i figured
i'd
bring this up in this venue.

 

[Marshall Barton]

If you're not too comfortable with queries, I suggest you
start by creating the Select query in the query design
window and using some sample criteria that the form would
have in a real situation (be sure you're working with a test
copy of the data tables). From this side of the issue, I
think the key issue is to get the proper criteria in the
WHERE clause (the Join part seems clear at this point).

Once you have the select query selecting the right records
to be duplicated, then you can convert it to an append query
and either parameterize it using controls on the form or (as
I would) convert it to a constructed query using code.

 

[Ted]

well ok, i'm going to try this out (with thanks), but what we haven't talked
about is my earlier question wrt how all this gets to deal with just the
Patient Number and Cycle number value the user's observing when looking at
this nested sub-form and the command button that's going to launch the query.
maybe this is obvious?

-ted

If you're not too comfortable with queries, I suggest you
start by creating the Select query in the query design
window and using some sample criteria that the form would
have in a real situation (be sure you're working with a test
copy of the data tables). From this side of the issue, I
think the key issue is to get the proper criteria in the
WHERE clause (the Join part seems clear at this point).

Once you have the select query selecting the right records
to be duplicated, then you can convert it to an append query
and either parameterize it using controls on the form or (as
I would) convert it to a constructed query using code.
--
Marsh
MVP [MS Access]

ok, i'm getting closer to nirvana here.
i'm going to want to try it out some tomorrow at the office.
one or two other possible requirement needing tending too though...
remember the two forms i spoke about, the main one and its correlated
sub-form. the original duplicate record button went on the sub-form and let
the user duplicate the cycle 'N' record for the patient the user was looking
at via the data entry screen. are we talking about creating this sql query,
giving it some name, and launching it from a button on the sub-form. would
this automatically take care of identifying the Patient Number the user's
looking at at the time the button got clicked: the functionality of this is
supposed to restrict the duplicating to just those adverse events appearing

 

[Ted]

what i'm trying to say in my previous reply is how do i 'pass' the values of
the current Patient Number and Cycle number to the query so they can be used
as add'l criteria?

If you're not too comfortable with queries, I suggest you
start by creating the Select query in the query design
window and using some sample criteria that the form would
have in a real situation (be sure you're working with a test
copy of the data tables). From this side of the issue, I
think the key issue is to get the proper criteria in the
WHERE clause (the Join part seems clear at this point).

Once you have the select query selecting the right records
to be duplicated, then you can convert it to an append query
and either parameterize it using controls on the form or (as
I would) convert it to a constructed query using code.
--
Marsh
MVP [MS Access]

ok, i'm getting closer to nirvana here.
i'm going to want to try it out some tomorrow at the office.
one or two other possible requirement needing tending too though...
remember the two forms i spoke about, the main one and its correlated
sub-form. the original duplicate record button went on the sub-form and let
the user duplicate the cycle 'N' record for the patient the user was looking
at via the data entry screen. are we talking about creating this sql query,
giving it some name, and launching it from a button on the sub-form. would
this automatically take care of identifying the Patient Number the user's
looking at at the time the button got clicked: the functionality of this is
supposed to restrict the duplicating to just those adverse events appearing

 

[Marshall Barton]

I may not understand the issue with patient number, etc. on
the form/subform, but I thought it was obvious ;-)
All you need to do is get the proper criteria in the Where
clause.

Ignore all the complications(?) of the button and actually
duplicating records. For now, just try to get that Select
query working the way you want it to using whatever criteria
you need using sample data as constants.

Start with something like this:

SELECT . . .
FROM table AS T1 LEFT JOIN table AS T2
ON T1.PatientID = T2.PatientID
AND T1.Cycle + 1 = T2.Cycle
WHERE T2.PatientID Is Null
AND T1.PatientID = 12345
AND T1.Continuing = 'Yes'
AND ? ? ?

Once that is selecting only the records you want to
duplicate, the rest of the probelm is mostly mechanics.

 

[Ted]

well ....

here's where it's at @ this point...my sql code based on your template's
below and it seemed to work when i eliminated all records from the table
except for the Patient Number i hard coded into it when that patient's Cycle
number was a 1.


SELECT T1.[Patient Number], T1.Cycle, T1.[AE Description], T1.Subtype,
T1.Onset, T1.Resolved, T1.[Continuing as of], T1.Grade, T1.Attribution,
T1.Serious, T1.Action, T1.Outcome, T1.DLT, T1.[AER Filed],
T1.ContinuingEndCycle, T1.Duplicated, T1.Updates
FROM [Adverse Events (child)] AS T1 LEFT JOIN [Adverse Events (child)] AS T2
ON (T1.[Patient Number]=T2.[Patient Number]) AND (T1.Cycle+1=T2.Cycle)
WHERE T2.[Patient Number] Is Null And T1.[Patient Number]=1097977 And
T1.ContinuingEndCycle="Yes";

i must've done something right

so, where are we now?

-ted

I may not understand the issue with patient number, etc. on
the form/subform, but I thought it was obvious ;-)
All you need to do is get the proper criteria in the Where
clause.

Ignore all the complications(?) of the button and actually
duplicating records. For now, just try to get that Select
query working the way you want it to using whatever criteria
you need using sample data as constants.

Start with something like this:

SELECT . . .
FROM table AS T1 LEFT JOIN table AS T2
ON T1.PatientID = T2.PatientID
AND T1.Cycle + 1 = T2.Cycle
WHERE T2.PatientID Is Null
AND T1.PatientID = 12345
AND T1.Continuing = 'Yes'
AND ? ? ?

Once that is selecting only the records you want to
duplicate, the rest of the probelm is mostly mechanics.
--
Marsh
MVP [MS Access]

well ok, i'm going to try this out (with thanks), but what we haven't talked
about is my earlier question wrt how all this gets to deal with just the
Patient Number and Cycle number value the user's observing when looking at
this nested sub-form and the command button that's going to launch the query.
maybe this is obvious?

 

[Marshall Barton]

here's where it's at @ this point...my sql code based on your template's
below and it seemed to work when i eliminated all records from the table
except for the Patient Number i hard coded into it when that patient's Cycle
number was a 1.

SELECT T1.[Patient Number], T1.Cycle, T1.[AE Description], T1.Subtype,
T1.Onset, T1.Resolved, T1.[Continuing as of], T1.Grade, T1.Attribution,
T1.Serious, T1.Action, T1.Outcome, T1.DLT, T1.[AER Filed],
T1.ContinuingEndCycle, T1.Duplicated, T1.Updates
FROM [Adverse Events (child)] AS T1 LEFT JOIN [Adverse Events (child)] AS T2
ON (T1.[Patient Number]=T2.[Patient Number]) AND (T1.Cycle+1=T2.Cycle)
WHERE T2.[Patient Number] Is Null And T1.[Patient Number]=1097977 And
T1.ContinuingEndCycle="Yes";

Don't you need to check the cycle number too? You don't
want to duplicate records that may have been marked as
continuing, but never needed to be duplicated, right?

Try this on a copy of the real table with lots of records to
make sure you are not picking up unwanted duplicates.

 

[Marshall Barton]

Let's not get ahead of ourselves here. You (and most
everybody) can deal with one issue at a time a lot better
than munging about in a bigger collection of problems. In
programming circles this is often called stepwise refinement
(take a big problem and break it down into a bunch of small
problems).

It's critical that the select query be infallible, before
stuffing it into a real environment, so test this query
thoroughly before going off on tangential issues.

 

[Ted]

marsh,

i'm a little confused by this (below)

Don't you need to check the cycle number too? You don't
want to duplicate records that may have been marked as
continuing, but never needed to be duplicated, right?

Try this on a copy of the real table with lots of records to
make sure you are not picking up unwanted duplicates.

what i ultimately want to let the user do is press a 'DUPLICATE RECORDS'
button which actuates something behind the scense. the user can always be
assumed correctly to have this button available to him/her at the bottom of
the sub-form he is using to view some combination of Patient Number and Cycle
number.

when the user clicks the DUPLICATES RECORDS button, its effect is to go
through all the sub-form records having a 'Yes' for 'Continuing at end of
cycle' and duplicate them into the next cycle (which he will have been
previously advised to guarantee already exists in the mainform).

regarding your remarks above, i believe that if a record is marked as
'Continuing...' the point is that it must appear in the subsequent cycle and
that user is too lazy to want to have to bother herself with manually
entering the same information into the sub-form for that record. so i don't
think it's an issue, or to put it another way, i think you've hit on a
non-issue.

on the flip-side of this, there may be a problem arising from the primary
keys which are comprised of all of the following in this order: Patient
Number, Cycle, AE Description, Subtype, Onset......

if each time the button is clicked, the effect is to unfailingly try to
duplicate every record that meets the Where criteria, the don't you run afoul
of the constraint imposed by the pk. if i'm right, then possibly we need to
have a field in the sub-form table called 'Duplicated' which by default is
'No' and which the sql query toggles to 'Yes' and which is added into the
where clause so as to overlook any records not having 'Duplicated' = 'No' and
thefore avoid duplicating previously duplicated records.

does that make sense?

here's where it's at @ this point...my sql code based on your template's
below and it seemed to work when i eliminated all records from the table
except for the Patient Number i hard coded into it when that patient's Cycle
number was a 1.

SELECT T1.[Patient Number], T1.Cycle, T1.[AE Description], T1.Subtype,
T1.Onset, T1.Resolved, T1.[Continuing as of], T1.Grade, T1.Attribution,
T1.Serious, T1.Action, T1.Outcome, T1.DLT, T1.[AER Filed],
T1.ContinuingEndCycle, T1.Duplicated, T1.Updates
FROM [Adverse Events (child)] AS T1 LEFT JOIN [Adverse Events (child)] AS T2
ON (T1.[Patient Number]=T2.[Patient Number]) AND (T1.Cycle+1=T2.Cycle)
WHERE T2.[Patient Number] Is Null And T1.[Patient Number]=1097977 And
T1.ContinuingEndCycle="Yes";

Don't you need to check the cycle number too? You don't
want to duplicate records that may have been marked as
continuing, but never needed to be duplicated, right?

Try this on a copy of the real table with lots of records to
make sure you are not picking up unwanted duplicates.

 

[Ted]

right, i'll cool my jets...

and with that, things appear to have gone a bit awry for some reason.

the code below:

SELECT T1.[Patient Number], T1.Cycle, T1.[AE Description], T1.Subtype,
T1.Onset, T1.Resolved, T1.[Continuing as of], T1.Grade, T1.Attribution,
T1.Serious, T1.Action, T1.Outcome, T1.DLT, T1.[AER Filed],
T1.ContinuingEndCycle, T1.Updates
FROM [Adverse Events (child)] AS T1 LEFT JOIN [Adverse Events (child)] AS T2
ON (T1.[Patient Number]=T2.[Patient Number]) AND (T1.Cycle+1=T2.Cycle)
WHERE T2.[Patient Number] Is Null And T1.[Patient Number]=01328164 And
T1.ContinuingEndCycle="Yes";

which i'm pasting as copied from the sql view window returns the desired
patient number when the continuingcycle value is 'Yes' but it doesn't bump up
the value of the cycle number by '1'.

i have tested it and tested it and still no cigar!

-ted

Let's not get ahead of ourselves here. You (and most
everybody) can deal with one issue at a time a lot better
than munging about in a bigger collection of problems. In
programming circles this is often called stepwise refinement
(take a big problem and break it down into a bunch of small
problems).

It's critical that the select query be infallible, before
stuffing it into a real environment, so test this query
thoroughly before going off on tangential issues.
--
Marsh
MVP [MS Access]

what i'm trying to say in my previous reply is how do i 'pass' the values of
the current Patient Number and Cycle number to the query so they can be used
as add'l criteria?

 

[Marshall Barton]

Comments inline below.
--
Marsh
MVP [MS Access]

i'm a little confused by this (below)


what i ultimately want to let the user do is press a 'DUPLICATE RECORDS'
button which actuates something behind the scense. the user can always be
assumed correctly to have this button available to him/her at the bottom of
the sub-form he is using to view some combination of Patient Number and Cycle
number.

when the user clicks the DUPLICATES RECORDS button, its effect is to go
through all the sub-form records having a 'Yes' for 'Continuing at end of
cycle' and duplicate them into the next cycle (which he will have been
previously advised to guarantee already exists in the mainform).

regarding your remarks above, i believe that if a record is marked as
'Continuing...' the point is that it must appear in the subsequent cycle and
that user is too lazy to want to have to bother herself with manually
entering the same information into the sub-form for that record. so i don't
think it's an issue, or to put it another way, i think you've hit on a
non-issue.
Good.


on the flip-side of this, there may be a problem arising from the primary
keys which are comprised of all of the following in this order: Patient
Number, Cycle, AE Description, Subtype, Onset......

That will just make the query a little messier

if each time the button is clicked, the effect is to unfailingly try to
duplicate every record that meets the Where criteria, the don't you run afoul
of the constraint imposed by the pk. if i'm right, then possibly we need to
have a field in the sub-form table called 'Duplicated' which by default is
'No' and which the sql query toggles to 'Yes' and which is added into the
where clause so as to overlook any records not having 'Duplicated' = 'No' and
thefore avoid duplicating previously duplicated records.

No need for that. The query will only duplicate the ones
that have not been duplicated before. That's what Joining
the table to itself and the Is Null criteria are for. We'll
just have to expand the ON clause to include all the PK
fields.

 

[Marshall Barton]

right, i'll cool my jets...

and with that, things appear to have gone a bit awry for some reason.

the code below:

SELECT T1.[Patient Number], T1.Cycle, T1.[AE Description], T1.Subtype,
T1.Onset, T1.Resolved, T1.[Continuing as of], T1.Grade, T1.Attribution,
T1.Serious, T1.Action, T1.Outcome, T1.DLT, T1.[AER Filed],
T1.ContinuingEndCycle, T1.Updates
FROM [Adverse Events (child)] AS T1 LEFT JOIN [Adverse Events (child)] AS T2
ON (T1.[Patient Number]=T2.[Patient Number]) AND (T1.Cycle+1=T2.Cycle)
WHERE T2.[Patient Number] Is Null And T1.[Patient Number]=01328164 And
T1.ContinuingEndCycle="Yes";

which i'm pasting as copied from the sql view window returns the desired
patient number when the continuingcycle value is 'Yes' but it doesn't bump up
the value of the cycle number by '1'.

Good catch. I missed that.

Just change the T1.Cycle in the field list above to:

T1.Cycle + 1 As Cycle

As you pointed out in another post, the ON clause needs to
include all the PK fields:

ON (T1.[Patient Number]=T2.[Patient Number])
AND (T1.[AE Description] = T2.[AE Description]),
AND (T1.Subtype = T2.Subtype),
AND . . .
AND (T1.Cycle+1=T2.Cycle)

Keep checking to see if we've missed anything else. This
query is the key to the whole operation of duplicating a
record and making sure that it doesn't duplicate it more
than once. So we have to make sure that it does everthing
correctly, including not doing anything if it has already
been done.

Let me know if the above changes are an improvement or what
happens.