Select Case and SQL

[Ted]

This is how I’ve drafted my AfterUpdate VBA on my A2K form. What I’m trying
to ‘trap’ for is the fact that when the user initially comes to a record on
the form, the form has a certain value (which I guess would be represented by
the OldValue property of the Me.Frame1 option group) which Vba needs to heed
before proceding and acting on the new (I guess ‘Case 1’, ‘Case 2’, etc)
value the user’s having selected one of the 7 radio buttons equals. So, for
example, when the new value is between a Case 1 and a Case 4 and the
previous (OldValue) was between 5 and 7, then we want to delete the record
corresponding with the matching identifying information. Or, when the
previous (OldValue) was either 2,3,4,6,7 and the new Case selected is a 5
then we want to append a record to another table. I don't how important or
not this is, but the variable (contorl source) behind the Frame1 is called
"Outcome_" and is used in the SQL code below. One of the things that is not
well known to me is the relation between the selection of a radio button in
this options group and the interplay between the control source ("Outcome_")
and the "OldValue" property. The other thing I notice is that I need a value
for the control source called "Off Study Date" before actuating the macro
that does the appending but the way this has gotten written doesn't wait for
the user to actually enter it.

Private Sub Frame1_AfterUpdate()
Dim Response As Long
Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus
If Me.Frame1.OldValue = 5 Or Me.Frame1.OldValue = 6 _
Or Me.Frame1.OldValue = 7 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case 2
Me.REgisteredDAte.SetFocus
If Me.Frame1.OldValue = 5 Or Me.Frame1.OldValue = 6 _
Or Me.Frame1.OldValue = 7 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case 3
Me.OnStudyDate.SetFocus
If Me.Frame1.OldValue = 5 Or Me.Frame1.OldValue = 6 _
Or Me.Frame1.OldValue = 7 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case 4
Me.TXEndedDate.SetFocus
If Me.Frame1.OldValue = 5 Or Me.Frame1.OldValue = 6 _
Or Me.Frame1.OldValue = 7 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case 5
Me.OffStudyDate.SetFocus
If Me.Frame1.OldValue = 2 Or Me.Frame1.OldValue = 3 Or
Me.Frame1.OldValue = 4 _
Or Me.Frame1.OldValue = 6 Or Me.Frame1.OldValue = 7 Then
DoCmd.RunMacro ("Append Off Study Pxs--Edit Form")
Case 6
Me.LTFUDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Update Off
Study Pxs--Edit Form") Else Results = MsgBox("You have coded this Patient as
either Dead or LTFU. First code this Patient as being Off Study!!!", vbOKOnly
+ vbCritical, "Alert!")
Case 7
Me.DateDth.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Update Off
Study Pxs--Edit Form") Else Results = MsgBox("You have coded this Patient as
either Dead or LTFU. First code this Patient as being Off Study!!!", vbOKOnly
+ vbCritical, "Alert!")
Case Else
End Select

End Sub


The SQL code below represents the design of the Append query behind the
macro for the select Case 5 scenario. I’m new at this, so I’m not
understanding how the user’s behavior and the intended actions I’m wanting to
implement mesh together/work.



INSERT INTO [Patients on Follow-Up] ( Dummy, [Study#], [Reg#], [IRB Number],
MR_Number, [Pt Initials], [On-Study Date], Site, [Px Status] )
SELECT 1 AS Dummy, [Screening Log].SequenceNum, [Screening Log].[Sponsor ID
Nbr], [Screening Log].[IRB Number], [Screening Log].MR_Number, Left([First
Name],1) & Left([Last Name],1) AS [Pt Initials], [Screening
Log].OffStudyDate, [Screening Log].Campus AS Site, lkpStatus.Status AS [Px
Status]
FROM lkpStatus INNER JOIN [Screening Log] ON lkpStatus.Code = [Screening
Log].Outcome_
WHERE ((([Screening Log].[IRB Number])=[Forms]![Screening Log (Edit
Only)].[IRB Number]) AND (([Screening Log].MR_Number)=[Forms]![Screening Log
(Edit Only)].[MR Number]) AND (([Screening
Log].OffStudyDate)=[Forms]![Screening Log (Edit Only)].[OffStudyDate]) AND
(([Screening Log].Outcome_) In (5)));