vba code needed to duplicate edits across sub-form records

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?



Manage Your Profile

although slightly confused by the description (sounds like it couldn't be helped!!) you can always lock a record by adding and extra field to the AE table that is a Yes/No and call it lock. that way, in a form you can write some code in the current event that checks the locked field and disables the controls.

As far as the other problem.... hmmm... bit of bad db design in the sense that if you are going to be repeating data then you have not normalised the database correctly and will take up unnecessary space. It sound like you can have multiple AE records per person and then multiple records (cycles) per AE record. if that is the case i would suggest that you create a separate table that stores cycles and is linked to the AE id. That way, it wil be easier to isolate the data that needs to be changed if an incorrect value has been entered at some stage for a single AE.

Thanks!