SQL append query with A2K

From: Ted (Ted_at_discussions.microsoft.com)
Date: 02/25/05 

Date: Fri, 25 Feb 2005 13:03:02 -0800

[note i inadvertently posted this to Access Forms :-( ]

the following is my append query at this writing

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)));

it replaces some VBA code following this line

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

now here's the QUESTION: how can i stuff the info from this line in the
immediately above VBA into my SQL query?

![Updates] = "On " & Now() & " , " & LAS_GetUserName() & " duplicated this
patient's Cycle #" & Forms![Treatment and Toxicity].[Current Cycle Number] &
" record."

everything to the right of the "=" sign would ideally appear in the Updates
field whenever a new record were appended using the SQL query at top.

hope this makes sense to some guru out there?

 
 
 
   

Quantcast