vba code to duplicate edits on sub-form

[Ted]

I am using A2K and have as my objective dynamically updating sub-form records
which have a certain relationship when one or more controls on them gets
modified.

My application has two forms, we’ll call “Treatment and Toxicity†(the
parent) and “Adverse Events (child)†(the child --child is a nested subform)
which are related by “Patient Number†and “Cycleâ€.

There is a control on the child form called “Continuing at end of cycleâ€
which is a listbox with ‘Yes’ and ‘No’ and when the current record in the
sub-form is coded a ‘Yes’ on it, a button labeled ‘DUPLICATE’ is activated.
This button actuates some SQL code which causes the data in the current
cycle’s to get duplicated to the next cycle’s for the same patient.

Here’s the rub: what happens if one or more of the controls in this sub-form
gets edited later on by a user. How would you restrict the ability of the
user to make the change to the first instance where this particular record
was completed for a particular patient AND have the change to the same
control’s data ‘cascade’ downward from that cycle to the next one and the
next one, until the last cycle’s entry for this record was reached for the
patient.

We’re talking about what in clinical trial parlance is referred to as
capturing A.E.s which means ‘Adverse Events’. For any patient on any cycle,
there may be more than one AE and that AE will have a “Descriptionâ€,
“Subtypeâ€, “Onset†date, in addition to “Patient Number†and “Cycleâ€
comprising the PK in the underlying table. Moreover other data such as
“Attribution†(to the drug under study), “Severityâ€, and so forth are coded
for each PK combo. When the data are the same for a PK combo and have been
duplicated you may have a situation where, because it was first observed in
Cycle 3 for this Patient Number and it continued over cycles, it spanned
Cycles 4, 5 and finally ended in Cycle 6. Except for the PK values, the data
recorded is always the same in each instance of this Aes. Moving ahead, let’s
say that the user learns that ‘Severity’ got miscoded and ought to be coded
as a level ‘B’ instead of a level ‘C’. If there were paper records instead of
electronic ones, the user would erase the ‘B’ on each form and write in ‘C’
for this field on AE form implicated. So my question is how does this
capability get folded into the database at hand. My gut tells me we need to
think about freezing the editing process from happening on all duplicates of
the ‘original’ A.E.’s, which in this case would coincide with Cycle 3. Then
I think we would need to have to deploy the DoCmd.GoToRecord command subject
to the record’s being ‘Dirty’ and limiting the ‘cascading’ edits to just
those records for the current record and its ‘clones’ before moving to the
next record.

I hope I’m still making sense at this point, cause I’m a VBA almost-newbie.

Anyone tackled something like this?

 

[John Nurick]

Hi Ted,

If you ever find yourself writing VBA code to "cascade" updates from
record to record it's a good time to step back and check your data
structure against the real-world domain you're modelling. This is
especially important in a healthcare or clinical trials context because
of the ethical and (in many jurisdictions) legal obligations regarding
privacy, data protection, and data integrity. It's even more important
if you're using Jet as the database engine (i.e. data in mdb files)
because of Jet's limited ability to enforce data integrity at the engine
level (in comparison with server database engines such as SQL Server).

Your model has a 1:M relationship between Cycles and Adverse Events
(with the Cycle field in the PK of the Adverse Events table). But from
what you say, it sounds as if (in real life) Patient number P may
experience an adverse event with onset date O, description D and subtype
S - and that this event may continue through the current cycle Cn and
potentially one or more subsequent cycles Cn+1...Cn+m.

IOW a many-to-many relationship between AdverseEvents and Cycles.

Looked at another way, the fact that you have (potentially) several
AdverseEvent records that are (and must be) the same for everything
except the value of the Cycle field suggests that your structure is
insufficiently normalised.

If you modify your structure to reflect this (by removing the Cycle
field from the AdverseEvents table and creating a joining table

AdverseEventsCycles
AdverseEvent * (FK into Adverse Events table)
Cycle * (FK into Cycles table)

you won't need to write code to handle "cascading" updates because there
won't be any, and you will be able to enforce relational integrity at
the engine level. (Strictly speaking I don't think these count as
"cascading" updates; the term normally refers to updating key values in
child tables when the related value in the parent table is updated.
Perhaps "consequential" would be a better word).

 

[Ted]

Greetings John,

And thanks for taking time out to read through my description.

At first as I was designing this part of the mdb, there was no hint of what
we're currently talking about, i.e. the fact that AEs can and do continue
beyond the current cycle (Cn as you referred to it) into the realm of Cn+1,
Cn+2,....and so forth. When a user was pressganged into testing it, she asked
if there were some way to automate the process of carrying over the AE data
from Cn to Cn+1 which brings us to your focus on isufficient normalization
and many to many relationships.

frankly, this part I've excerpted is a little difficult for newbie me to
follow:

Looked at another way, the fact that you have (potentially) several
AdverseEvent records that are (and must be) the same for everything
except the value of the Cycle field suggests that your structure is
insufficiently normalised.

If you modify your structure to reflect this (by removing the Cycle
field from the AdverseEvents table and creating a joining table

AdverseEventsCycles
AdverseEvent * (FK into Adverse Events table)
Cycle * (FK into Cycles table)

I have never gotten into a situation where M:M forces have been quietly at
work and so I'm not quite clear what the above means and how to work with it.

The user currently opens a form which contains a sub-form (see original
posting). The upper main form is bound to a table (as is the sub-form). Main
form's PK is comprised Patient and Cycle; sub-form's of PK, Cycle,
AE_Description, Subtype and Onset and as I think I indicated (see orig'l) the
two are linked parent/child fashion via Patient and Cycle numbers.

I am prepared to try this experiment with your approach once I can get past
the issue of visualizing how to implement it. There appears to be some
positive value from cobbling it into the mdb, however I'm also a little
concerned over what would come of the design of the current pair of
parent/child forms and the reports (which are actually hardcopy "CRF"
printouts of the data entry screens) they generate.

Hope you're still following this and fill in some blanks.

With best regards,

-ted

 

[John Nurick]

[Crossposted to tablesdbdesign because its getting OT for .formscoding]

Greetings John,

And thanks for taking time out to read through my description.

At first as I was designing this part of the mdb, there was no hint of what
we're currently talking about, i.e. the fact that AEs can and do continue
beyond the current cycle (Cn as you referred to it) into the realm of Cn+1,
Cn+2,....and so forth. When a user was pressganged into testing it, she asked
if there were some way to automate the process of carrying over the AE data
from Cn to Cn+1 which brings us to your focus on isufficient normalization
and many to many relationships.

frankly, this part I've excerpted is a little difficult for newbie me to
follow:

One of the fundamental ideas behind normalisation is that data like
this, in which the first three fields are not independent from record to
record,

Customer,Date,OrderNumber,Item,Quantity
Jones,1/2/05,34597,Widgets,25
Jones,1/2/05,34597,Short Circuits,40
Jones,1/2/05,34597,Sky hooks,1
Jones,1/2/05,34597,Monkey wrench nuts,144

should go into two tables like this

Orders
OrderNumber,Customer,Date
34597,Jones,1/2/05
OrderItems
34597,OrderNumber,Item,Quantity
34597,Widgets,25
34597,Short Circuits,40
34597,Sky hooks,1
34597,Monkey wrench nuts,144

Your present Adverse Events table seems to be in the same case.

I have never gotten into a situation where M:M forces have been quietly at
work and so I'm not quite clear what the above means and how to work with it.

Each record in AdverseEventsCycles means that that AdverseEvent (with
associaed Patient, Onset Date) existed/applied/continued in that Cycle.
So if the adverse event is experience through four Cycles, there would
be four records in AdverseEvents Cycles.

Thinking about it now I'm not certain that this is the best model.
Possibly it would be better to modify the AdverseEvents table like this:

AdverseEvents
Patient* FK into patients
OnsetDate*
Description*
SubCategory*
CycleFirstObserved FK into Cycles
CycleLastObserved FK into Cycles

A null value for CycleLastObserved would mean that the AdverseEvent was
still being experienced.

Looking at this, the CycleFirstObserved field looks is if it might be
unnecessary: if you know the OnsetDate can you just look up the Cycle?
(maybe not: I guess adverse effects can show up decades after the trial
has been completed<g>).

Either way, this shows how hard one has to think about data stuctures
when trying to fit complex real-world situations into a relational
framework.

The user currently opens a form which contains a sub-form (see original
posting). The upper main form is bound to a table (as is the sub-form). Main
form's PK is comprised Patient and Cycle; sub-form's of PK, Cycle,
AE_Description, Subtype and Onset and as I think I indicated (see orig'l) the
two are linked parent/child fashion via Patient and Cycle numbers.

Forms and sub-forms don't have primary keys: tables do. I think you
omitted Patient from the list of fields in the AdverseEvents table.

I am prepared to try this experiment with your approach once I can get past
the issue of visualizing how to implement it. There appears to be some
positive value from cobbling it into the mdb, however I'm also a little
concerned over what would come of the design of the current pair of
parent/child forms and the reports (which are actually hardcopy "CRF"
printouts of the data entry screens) they generate.

With a normalised structure you will still be able to generate whatever
reports you need (in fact better normalisation widens the range of
possible reports). You may need to rethink parts of the user interface,
but normally form+subform works well with M:M relationships; check out
the Orders form in the Northwind sample database among others.

 

[Ted]

i don't know if this'll help, but to put some more context into the existing
design, for each combination of Patient Number and Cycle Number a hardcopy
form gets printed-- which means that the top part having things like body
weight among others which don't get repeatedly collected within a cycle
appears atop the form, and the contents of the sub-form appear within the
sub-report associated with the main one. forms having this structure are
endemic to the world of the clinical trial slash pharmaceutical industry and
this mdb is intened to create CRF (case report forms) look alikes for reasons
outside of this discussion.

that having been said, so, while i think i see your point about the

Possibly it would be better to modify the AdverseEvents table like this:

AdverseEvents
Patient* FK into patients
OnsetDate*
Description*
SubCategory*
CycleFirstObserved FK into Cycles
CycleLastObserved FK into Cycles

i don't quite know whether the adjusted structure would represent an
'improvement' given the above constraints.

i do agree i may have used a 'reserved' word (i.e. 'cascade') to modify
'edits' in my first posting, however it seemed like it would convey the sense
of my objective.

so, at this point, given what appears to be the unyielding design of the AE
forms and the need to model this after them, what i may just do is generate a
reminder message to the user which appears when the user attempts to modify a
control on an AE subform record which belongs to the ones that have
duplicates ('Continuing at end of cycle' = 'Yes').

best,

-ted

[Crossposted to tablesdbdesign because its getting OT for .formscoding]

Greetings John,

And thanks for taking time out to read through my description.

At first as I was designing this part of the mdb, there was no hint of what
we're currently talking about, i.e. the fact that AEs can and do continue
beyond the current cycle (Cn as you referred to it) into the realm of Cn+1,
Cn+2,....and so forth. When a user was pressganged into testing it, she asked
if there were some way to automate the process of carrying over the AE data
from Cn to Cn+1 which brings us to your focus on isufficient normalization
and many to many relationships.

frankly, this part I've excerpted is a little difficult for newbie me to
follow:

One of the fundamental ideas behind normalisation is that data like
this, in which the first three fields are not independent from record to
record,

Customer,Date,OrderNumber,Item,Quantity
Jones,1/2/05,34597,Widgets,25
Jones,1/2/05,34597,Short Circuits,40
Jones,1/2/05,34597,Sky hooks,1
Jones,1/2/05,34597,Monkey wrench nuts,144

should go into two tables like this

Orders
OrderNumber,Customer,Date
34597,Jones,1/2/05
OrderItems
34597,OrderNumber,Item,Quantity
34597,Widgets,25
34597,Short Circuits,40
34597,Sky hooks,1
34597,Monkey wrench nuts,144

Your present Adverse Events table seems to be in the same case.

I have never gotten into a situation where M:M forces have been quietly at
work and so I'm not quite clear what the above means and how to work with it.

Each record in AdverseEventsCycles means that that AdverseEvent (with
associaed Patient, Onset Date) existed/applied/continued in that Cycle.
So if the adverse event is experience through four Cycles, there would
be four records in AdverseEvents Cycles.

Thinking about it now I'm not certain that this is the best model.
Possibly it would be better to modify the AdverseEvents table like this:

AdverseEvents
Patient* FK into patients
OnsetDate*
Description*
SubCategory*
CycleFirstObserved FK into Cycles
CycleLastObserved FK into Cycles

A null value for CycleLastObserved would mean that the AdverseEvent was
still being experienced.

Looking at this, the CycleFirstObserved field looks is if it might be
unnecessary: if you know the OnsetDate can you just look up the Cycle?
(maybe not: I guess adverse effects can show up decades after the trial
has been completed<g>).

Either way, this shows how hard one has to think about data stuctures
when trying to fit complex real-world situations into a relational
framework.

The user currently opens a form which contains a sub-form (see original
posting). The upper main form is bound to a table (as is the sub-form). Main
form's PK is comprised Patient and Cycle; sub-form's of PK, Cycle,
AE_Description, Subtype and Onset and as I think I indicated (see orig'l) the
two are linked parent/child fashion via Patient and Cycle numbers.

Forms and sub-forms don't have primary keys: tables do. I think you
omitted Patient from the list of fields in the AdverseEvents table.

I am prepared to try this experiment with your approach once I can get past
the issue of visualizing how to implement it. There appears to be some
positive value from cobbling it into the mdb, however I'm also a little
concerned over what would come of the design of the current pair of
parent/child forms and the reports (which are actually hardcopy "CRF"
printouts of the data entry screens) they generate.

With a normalised structure you will still be able to generate whatever
reports you need (in fact better normalisation widens the range of
possible reports). You may need to rethink parts of the user interface,
but normally form+subform works well with M:M relationships; check out
the Orders form in the Northwind sample database among others.

Hope you're still following this and fill in some blanks.

With best regards,

-ted

 

[John Nurick]

It's usually a mistake to allow a paper form to determine the data
*structure* you use. Data entry forms often benefit from similarity to
familiar paper forms - but there is no obligation for your tables to
mimic them. And when it comes to reporting, provided the database
contains the data in an appropriately normalised structure, it's
possible to build reports and queries that will present it any way you
need.

i don't know if this'll help, but to put some more context into the existing
design, for each combination of Patient Number and Cycle Number a hardcopy
form gets printed-- which means that the top part having things like body
weight among others which don't get repeatedly collected within a cycle
appears atop the form, and the contents of the sub-form appear within the
sub-report associated with the main one. forms having this structure are
endemic to the world of the clinical trial slash pharmaceutical industry and
this mdb is intened to create CRF (case report forms) look alikes for reasons
outside of this discussion.

that having been said, so, while i think i see your point about the

Possibly it would be better to modify the AdverseEvents table like this:

AdverseEvents
Patient* FK into patients
OnsetDate*
Description*
SubCategory*
CycleFirstObserved FK into Cycles
CycleLastObserved FK into Cycles

i don't quite know whether the adjusted structure would represent an
'improvement' given the above constraints.

i do agree i may have used a 'reserved' word (i.e. 'cascade') to modify
'edits' in my first posting, however it seemed like it would convey the sense
of my objective.

so, at this point, given what appears to be the unyielding design of the AE
forms and the need to model this after them, what i may just do is generate a
reminder message to the user which appears when the user attempts to modify a
control on an AE subform record which belongs to the ones that have
duplicates ('Continuing at end of cycle' = 'Yes').

best,

-ted

[Crossposted to tablesdbdesign because its getting OT for .formscoding]

Greetings John,

And thanks for taking time out to read through my description.

At first as I was designing this part of the mdb, there was no hint of what
we're currently talking about, i.e. the fact that AEs can and do continue
beyond the current cycle (Cn as you referred to it) into the realm of Cn+1,
Cn+2,....and so forth. When a user was pressganged into testing it, she asked
if there were some way to automate the process of carrying over the AE data
from Cn to Cn+1 which brings us to your focus on isufficient normalization
and many to many relationships.

frankly, this part I've excerpted is a little difficult for newbie me to
follow:

Looked at another way, the fact that you have (potentially) several
AdverseEvent records that are (and must be) the same for everything
except the value of the Cycle field suggests that your structure is
insufficiently normalised.

One of the fundamental ideas behind normalisation is that data like
this, in which the first three fields are not independent from record to
record,

Customer,Date,OrderNumber,Item,Quantity
Jones,1/2/05,34597,Widgets,25
Jones,1/2/05,34597,Short Circuits,40
Jones,1/2/05,34597,Sky hooks,1
Jones,1/2/05,34597,Monkey wrench nuts,144

should go into two tables like this

Orders
OrderNumber,Customer,Date
34597,Jones,1/2/05
OrderItems
34597,OrderNumber,Item,Quantity
34597,Widgets,25
34597,Short Circuits,40
34597,Sky hooks,1
34597,Monkey wrench nuts,144

Your present Adverse Events table seems to be in the same case.

If you modify your structure to reflect this (by removing the Cycle
field from the AdverseEvents table and creating a joining table

AdverseEventsCycles
AdverseEvent * (FK into Adverse Events table)
Cycle * (FK into Cycles table)

I have never gotten into a situation where M:M forces have been quietly at
work and so I'm not quite clear what the above means and how to work with it.

Each record in AdverseEventsCycles means that that AdverseEvent (with
associaed Patient, Onset Date) existed/applied/continued in that Cycle.
So if the adverse event is experience through four Cycles, there would
be four records in AdverseEvents Cycles.

Thinking about it now I'm not certain that this is the best model.
Possibly it would be better to modify the AdverseEvents table like this:

AdverseEvents
Patient* FK into patients
OnsetDate*
Description*
SubCategory*
CycleFirstObserved FK into Cycles
CycleLastObserved FK into Cycles

A null value for CycleLastObserved would mean that the AdverseEvent was
still being experienced.

Looking at this, the CycleFirstObserved field looks is if it might be
unnecessary: if you know the OnsetDate can you just look up the Cycle?
(maybe not: I guess adverse effects can show up decades after the trial
has been completed<g>).

Either way, this shows how hard one has to think about data stuctures
when trying to fit complex real-world situations into a relational
framework.

The user currently opens a form which contains a sub-form (see original
posting). The upper main form is bound to a table (as is the sub-form). Main
form's PK is comprised Patient and Cycle; sub-form's of PK, Cycle,
AE_Description, Subtype and Onset and as I think I indicated (see orig'l) the
two are linked parent/child fashion via Patient and Cycle numbers.

Forms and sub-forms don't have primary keys: tables do. I think you
omitted Patient from the list of fields in the AdverseEvents table.

I am prepared to try this experiment with your approach once I can get past
the issue of visualizing how to implement it. There appears to be some
positive value from cobbling it into the mdb, however I'm also a little
concerned over what would come of the design of the current pair of
parent/child forms and the reports (which are actually hardcopy "CRF"
printouts of the data entry screens) they generate.

With a normalised structure you will still be able to generate whatever
reports you need (in fact better normalisation widens the range of
possible reports). You may need to rethink parts of the user interface,
but normally form+subform works well with M:M relationships; check out
the Orders form in the Northwind sample database among others.

Hope you're still following this and fill in some blanks.

With best regards,

-ted




:

Hi Ted,

If you ever find yourself writing VBA code to "cascade" updates from
record to record it's a good time to step back and check your data
structure against the real-world domain you're modelling. This is
especially important in a healthcare or clinical trials context because
of the ethical and (in many jurisdictions) legal obligations regarding
privacy, data protection, and data integrity. It's even more important
if you're using Jet as the database engine (i.e. data in mdb files)
because of Jet's limited ability to enforce data integrity at the engine
level (in comparison with server database engines such as SQL Server).

Your model has a 1:M relationship between Cycles and Adverse Events
(with the Cycle field in the PK of the Adverse Events table). But from
what you say, it sounds as if (in real life) Patient number P may
experience an adverse event with onset date O, description D and subtype
S - and that this event may continue through the current cycle Cn and
potentially one or more subsequent cycles Cn+1...Cn+m.

IOW a many-to-many relationship between AdverseEvents and Cycles.

Looked at another way, the fact that you have (potentially) several
AdverseEvent records that are (and must be) the same for everything
except the value of the Cycle field suggests that your structure is
insufficiently normalised.

If you modify your structure to reflect this (by removing the Cycle
field from the AdverseEvents table and creating a joining table

AdverseEventsCycles
AdverseEvent * (FK into Adverse Events table)
Cycle * (FK into Cycles table)

you won't need to write code to handle "cascading" updates because there
won't be any, and you will be able to enforce relational integrity at
the engine level. (Strictly speaking I don't think these count as
"cascading" updates; the term normally refers to updating key values in
child tables when the related value in the parent table is updated.
Perhaps "consequential" would be a better word).

I am using A2K and have as my objective dynamically updating sub-form records
which have a certain relationship when one or more controls on them gets
modified.

My application has two forms, we’ll call “Treatment and Toxicity” (the
parent) and “Adverse Events (child)” (the child --child is a nested subform)
which are related by “Patient Number” and “Cycle”.

There is a control on the child form called “Continuing at end of cycle”
which is a listbox with ‘Yes’ and ‘No’ and when the current record in the
sub-form is coded a ‘Yes’ on it, a button labeled ‘DUPLICATE’ is activated.
This button actuates some SQL code which causes the data in the current
cycle’s to get duplicated to the next cycle’s for the same patient.

Here’s the rub: what happens if one or more of the controls in this sub-form
gets edited later on by a user. How would you restrict the ability of the
user to make the change to the first instance where this particular record
was completed for a particular patient AND have the change to the same
control’s data ‘cascade’ downward from that cycle to the next one and the
next one, until the last cycle’s entry for this record was reached for the
patient.

We’re talking about what in clinical trial parlance is referred to as
capturing A.E.s which means ‘Adverse Events’. For any patient on any cycle,
there may be more than one AE and that AE will have a “Description”,
“Subtype”, “Onset” date, in addition to “Patient Number” and “Cycle”
comprising the PK in the underlying table. Moreover other data such as
“Attribution” (to the drug under study), “Severity”, and so forth are coded
for each PK combo. When the data are the same for a PK combo and have been
duplicated you may have a situation where, because it was first observed in
Cycle 3 for this Patient Number and it continued over cycles, it spanned
Cycles 4, 5 and finally ended in Cycle 6. Except for the PK values, the data
recorded is always the same in each instance of this Aes. Moving ahead, let’s
say that the user learns that ‘Severity’ got miscoded and ought to be coded
as a level ‘B’ instead of a level ‘C’. If there were paper records instead of
electronic ones, the user would erase the ‘B’ on each form and write in ‘C’
for this field on AE form implicated. So my question is how does this
capability get folded into the database at hand. My gut tells me we need to
think about freezing the editing process from happening on all duplicates of
the ‘original’ A.E.’s, which in this case would coincide with Cycle 3. Then
I think we would need to have to deploy the DoCmd.GoToRecord command subject
to the record’s being ‘Dirty’ and limiting the ‘cascading’ edits to just
those records for the current record and its ‘clones’ before moving to the
next record.

I hope I’m still making sense at this point, cause I’m a VBA almost-newbie.

Anyone tackled something like this?

 

[Ted]

and i would tend to support your statements except for the fact i can't see
how your approach would work w/in the context of this reporting process.

best,

-ted

It's usually a mistake to allow a paper form to determine the data
*structure* you use. Data entry forms often benefit from similarity to
familiar paper forms - but there is no obligation for your tables to
mimic them. And when it comes to reporting, provided the database
contains the data in an appropriately normalised structure, it's
possible to build reports and queries that will present it any way you
need.

i don't know if this'll help, but to put some more context into the existing
design, for each combination of Patient Number and Cycle Number a hardcopy
form gets printed-- which means that the top part having things like body
weight among others which don't get repeatedly collected within a cycle
appears atop the form, and the contents of the sub-form appear within the
sub-report associated with the main one. forms having this structure are
endemic to the world of the clinical trial slash pharmaceutical industry and
this mdb is intened to create CRF (case report forms) look alikes for reasons
outside of this discussion.

that having been said, so, while i think i see your point about the

Possibly it would be better to modify the AdverseEvents table like this:

AdverseEvents
Patient* FK into patients
OnsetDate*
Description*
SubCategory*
CycleFirstObserved FK into Cycles
CycleLastObserved FK into Cycles

i don't quite know whether the adjusted structure would represent an
'improvement' given the above constraints.

i do agree i may have used a 'reserved' word (i.e. 'cascade') to modify
'edits' in my first posting, however it seemed like it would convey the sense
of my objective.

so, at this point, given what appears to be the unyielding design of the AE
forms and the need to model this after them, what i may just do is generate a
reminder message to the user which appears when the user attempts to modify a
control on an AE subform record which belongs to the ones that have
duplicates ('Continuing at end of cycle' = 'Yes').

best,

-ted

[Crossposted to tablesdbdesign because its getting OT for .formscoding]

Greetings John,

And thanks for taking time out to read through my description.

At first as I was designing this part of the mdb, there was no hint of what
we're currently talking about, i.e. the fact that AEs can and do continue
beyond the current cycle (Cn as you referred to it) into the realm of Cn+1,
Cn+2,....and so forth. When a user was pressganged into testing it, she asked
if there were some way to automate the process of carrying over the AE data
from Cn to Cn+1 which brings us to your focus on isufficient normalization
and many to many relationships.

frankly, this part I've excerpted is a little difficult for newbie me to
follow:

Looked at another way, the fact that you have (potentially) several
AdverseEvent records that are (and must be) the same for everything
except the value of the Cycle field suggests that your structure is
insufficiently normalised.

One of the fundamental ideas behind normalisation is that data like
this, in which the first three fields are not independent from record to
record,

Customer,Date,OrderNumber,Item,Quantity
Jones,1/2/05,34597,Widgets,25
Jones,1/2/05,34597,Short Circuits,40
Jones,1/2/05,34597,Sky hooks,1
Jones,1/2/05,34597,Monkey wrench nuts,144

should go into two tables like this

Orders
OrderNumber,Customer,Date
34597,Jones,1/2/05
OrderItems
34597,OrderNumber,Item,Quantity
34597,Widgets,25
34597,Short Circuits,40
34597,Sky hooks,1
34597,Monkey wrench nuts,144

Your present Adverse Events table seems to be in the same case.

If you modify your structure to reflect this (by removing the Cycle
field from the AdverseEvents table and creating a joining table

AdverseEventsCycles
AdverseEvent * (FK into Adverse Events table)
Cycle * (FK into Cycles table)

I have never gotten into a situation where M:M forces have been quietly at
work and so I'm not quite clear what the above means and how to work with it.

Each record in AdverseEventsCycles means that that AdverseEvent (with
associaed Patient, Onset Date) existed/applied/continued in that Cycle.
So if the adverse event is experience through four Cycles, there would
be four records in AdverseEvents Cycles.

Thinking about it now I'm not certain that this is the best model.
Possibly it would be better to modify the AdverseEvents table like this:

AdverseEvents
Patient* FK into patients
OnsetDate*
Description*
SubCategory*
CycleFirstObserved FK into Cycles
CycleLastObserved FK into Cycles

A null value for CycleLastObserved would mean that the AdverseEvent was
still being experienced.

Looking at this, the CycleFirstObserved field looks is if it might be
unnecessary: if you know the OnsetDate can you just look up the Cycle?
(maybe not: I guess adverse effects can show up decades after the trial
has been completed<g>).

Either way, this shows how hard one has to think about data stuctures
when trying to fit complex real-world situations into a relational
framework.

The user currently opens a form which contains a sub-form (see original
posting). The upper main form is bound to a table (as is the sub-form). Main
form's PK is comprised Patient and Cycle; sub-form's of PK, Cycle,
AE_Description, Subtype and Onset and as I think I indicated (see orig'l) the
two are linked parent/child fashion via Patient and Cycle numbers.

Forms and sub-forms don't have primary keys: tables do. I think you
omitted Patient from the list of fields in the AdverseEvents table.

I am prepared to try this experiment with your approach once I can get past
the issue of visualizing how to implement it. There appears to be some
positive value from cobbling it into the mdb, however I'm also a little
concerned over what would come of the design of the current pair of
parent/child forms and the reports (which are actually hardcopy "CRF"
printouts of the data entry screens) they generate.

With a normalised structure you will still be able to generate whatever
reports you need (in fact better normalisation widens the range of
possible reports). You may need to rethink parts of the user interface,
but normally form+subform works well with M:M relationships; check out
the Orders form in the Northwind sample database among others.



Hope you're still following this and fill in some blanks.

With best regards,

-ted




:

Hi Ted,

If you ever find yourself writing VBA code to "cascade" updates from
record to record it's a good time to step back and check your data
structure against the real-world domain you're modelling. This is
especially important in a healthcare or clinical trials context because
of the ethical and (in many jurisdictions) legal obligations regarding
privacy, data protection, and data integrity. It's even more important
if you're using Jet as the database engine (i.e. data in mdb files)
because of Jet's limited ability to enforce data integrity at the engine
level (in comparison with server database engines such as SQL Server).

Your model has a 1:M relationship between Cycles and Adverse Events
(with the Cycle field in the PK of the Adverse Events table). But from
what you say, it sounds as if (in real life) Patient number P may
experience an adverse event with onset date O, description D and subtype
S - and that this event may continue through the current cycle Cn and
potentially one or more subsequent cycles Cn+1...Cn+m.

IOW a many-to-many relationship between AdverseEvents and Cycles.

Looked at another way, the fact that you have (potentially) several
AdverseEvent records that are (and must be) the same for everything
except the value of the Cycle field suggests that your structure is
insufficiently normalised.

If you modify your structure to reflect this (by removing the Cycle
field from the AdverseEvents table and creating a joining table

AdverseEventsCycles
AdverseEvent * (FK into Adverse Events table)
Cycle * (FK into Cycles table)

you won't need to write code to handle "cascading" updates because there
won't be any, and you will be able to enforce relational integrity at
the engine level. (Strictly speaking I don't think these count as
"cascading" updates; the term normally refers to updating key values in
child tables when the related value in the parent table is updated.
Perhaps "consequential" would be a better word).

I am using A2K and have as my objective dynamically updating sub-form records
which have a certain relationship when one or more controls on them gets
modified.

My application has two forms, we’ll call “Treatment and Toxicity†(the
parent) and “Adverse Events (child)†(the child --child is a nested subform)
which are related by “Patient Number†and “Cycleâ€.

There is a control on the child form called “Continuing at end of cycleâ€
which is a listbox with ‘Yes’ and ‘No’ and when the current record in the
sub-form is coded a ‘Yes’ on it, a button labeled ‘DUPLICATE’ is activated.
This button actuates some SQL code which causes the data in the current
cycle’s to get duplicated to the next cycle’s for the same patient.

Here’s the rub: what happens if one or more of the controls in this sub-form
gets edited later on by a user. How would you restrict the ability of the
user to make the change to the first instance where this particular record
was completed for a particular patient AND have the change to the same
control’s data ‘cascade’ downward from that cycle to the next one and the
next one, until the last cycle’s entry for this record was reached for the
patient.

We’re talking about what in clinical trial parlance is referred to as
capturing A.E.s which means ‘Adverse Events’. For any patient on any cycle,
there may be more than one AE and that AE will have a “Descriptionâ€,
“Subtypeâ€, “Onset†date, in addition to “Patient Number†and “Cycleâ€
comprising the PK in the underlying table. Moreover other data such as
“Attribution†(to the drug under study), “Severityâ€, and so forth are coded
for each PK combo. When the data are the same for a PK combo and have been
duplicated you may have a situation where, because it was first observed in
Cycle 3 for this Patient Number and it continued over cycles, it spanned
Cycles 4, 5 and finally ended in Cycle 6. Except for the PK values, the data
recorded is always the same in each instance of this Aes. Moving ahead, let’s
say that the user learns that ‘Severity’ got miscoded and ought to be coded
as a level ‘B’ instead of a level ‘C’. If there were paper records instead of
electronic ones, the user would erase the ‘B’ on each form and write in ‘C’
for this field on AE form implicated. So my question is how does this
capability get folded into the database at hand. My gut tells me we need to
think about freezing the editing process from happening on all duplicates of
the ‘original’ A.E.’s, which in this case would coincide with Cycle 3. Then
I think we would need to have to deploy the DoCmd.GoToRecord command subject
to the record’s being ‘Dirty’ and limiting the ‘cascading’ edits to just
those records for the current record and its ‘clones’ before moving to the
next record.

I hope I’m still making sense at this point, cause I’m a VBA almost-newbie.

Anyone tackled something like this?