Re: Writing Executable VBA Statements

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Marshall Barton (marshbarton_at_wowway.com)
Date: 02/18/05 

Date: Thu, 17 Feb 2005 18:16:00 -0600

Ted wrote:
>the story continues to continue as i think of some facets of this process
>that i dhtink might've been glossed over in the quest to create the magic
>button...
>
>INSERT INTO [Adverse Events (child)]
>SELECT T1.[Patient Number] AS [Patient Number], T1.Cycle+1 AS Cycle, T1.[AE
>Description] AS [AE Description], T1.Subtype AS Subtype, T1.Onset AS Onset,
>T1.Resolved AS Resolved, T1.[Continuing as of] AS [Continuing as of],
>T1.Grade AS Grade, T1.Attribution AS Attribution, T1.Serious AS Serious,
>T1.Action AS [Action], T1.Outcome AS Outcome, T1.DLT AS DLT, T1.[AER Filed]
>AS [AER Filed], "No" AS ContinuingEndCycle, T1.Updates AS Updates
>FROM [Adverse Events (child)] AS T1 LEFT JOIN [Adverse Events (child)] AS T2
>ON (T1.Onset=T2.Onset) AND (T1.Subtype=T2.Subtype) AND (T1.[AE
>Description]=T2.[AE Description]) AND (T1.[Patient Number]=T2.[Patient
>Number]) AND (T1.Cycle+1=T2.Cycle)
>WHERE (((T1.[Patient Number]=[Forms]![Treatment and Toxicity].[Patient
>Number]) And ((T1.ContinuingEndCycle)="Yes") And ((T2.[Patient Number]) Is
>Null)));
>
>
>here's the code (above) as it stands in the query. so, i guess my question
>has to do with the fact that there's the possibility the user might go to one
>of the duplicate aes (or possibly even the cycle where the first one that
>gets subsequently duplicated) and modify a value of one of the fields that
>are being cloned in the next cycle. e.g. lets say that for a particular
>patient id, an ae occurs for the first time in cycle 2 and that it's later
>duplicated into cycles 3 and then maybe even 4 whereby it comes to a halt and
>continues no further. then let's say that the user realizes something was not
>coded quite correctly in cycle 2 and modifies its value. user hits the
>duplicate button....question: does the ae get duplicated to cycle 3. if not,
>then is it because cycle 3 and 4 records for this ae exist? does the sql ON
>clause need to be expanded to include every parameter on the record?

Yo, Ted, you really need to take a little time out on these
additions and make sure what you have is integrated into the
form, that it works properly, AND that you have a gut level
understanding of what it does and how it does it.

The query we've been working on all this time will only add
new records, it can not, nor would I want it to do any more
than that. As far as I know, that query is done, Done, DONE
;-)

Editing existing records and propagating changes to other
records is a whole 'nother operation that should be
addressed within it's own context. The first thing to do is
nail down what is supposed to happen under all kinds of
various scenarios. Once that is done (reviewed and approved
by the users and ...), then you can think about all the
logic involved in dealing with whatever the operations turn
out to be. Not until you have the requirements and design
details all laid out (on paper, not just in your head), then
you can worry about how you're going to go about
implementing the design. 

-- 
Marsh
MVP [MS Access]
Quantcast