SQL append query using A2K

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?

Hi:

One idea is to make this a parameter query and invoke from code and pass
parameters via code.
Some articles on this --

http://support.microsoft.com/kb/q142938/

http://www.tek-tips.com/faqs.cfm?fid=2205

Regards,

Naresh Nichani
Microsoft Access MVP




"Ted" wrote in message
...
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?

hi,

what i did was rewrite

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

into

"On " & Now() & " , " & LAS_GetUserName() & " duplicated this
patient's Cycle #" & T1.Cycle

which was used to replace "T1.Updates" in my SQL code.

based on some limited testing of mine i've done, that did the trick.

thanks for the bandwidth,

-ted






"Naresh Nichani MVP" wrote:

Hi:

One idea is to make this a parameter query and invoke from code and pass
parameters via code.
Some articles on this --

http://support.microsoft.com/kb/q142938/

http://www.tek-tips.com/faqs.cfm?fid=2205

Regards,

Naresh Nichani
Microsoft Access MVP




"Ted" wrote in message
...
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?