Options Group, AfterUpdate Event, OldValue Property and ControlSou

[Ted]

i'm using a2k and i've posted variations of this theme in recent days but
just seem to be getting stuck deeper in things as the plot thickens.....

in broad sweeping terms ( ), when my user is looking at his choices
from amongst the 7 radion buttons in the Frame1 options group, there is an
interest in launching certain macros (which have either append, delete or
update queries attached to them) depending upon a) the radio button selected
and b) the value of the radio button before it was modified by the user. i'm
new to this aspect of a2k, so my impression is that while the user is sitting
on a record, every time he/she selects one of the radio buttons in the Frame1
options group the following happens 1) the value of the options group's
control source i dubbed 'Outcome_' changes and 2, the OldValue property is
updated to reflect the previous value of the radio button's. i don't think
that this is really how vba is set up so if you can help straighthen me out,
i'd appreciate it. i'm attaching some of the AfterUpdate followed by the SQL
version of the append query that the Case 5 macro launches to help put things
into focus.

Private Sub Frame1_AfterUpdate()
Dim Response As Long
Select Case Frame1.Value
Case 1
.....
Case 2
....
Case 3
.....
Case 4
....
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

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

 

[Dirk Goldgar]

i'm using a2k and i've posted variations of this theme in recent days
but just seem to be getting stuck deeper in things as the plot
thickens.....

in broad sweeping terms ( ), when my user is looking at his
choices from amongst the 7 radion buttons in the Frame1 options
group, there is an interest in launching certain macros (which have
either append, delete or update queries attached to them) depending
upon a) the radio button selected and b) the value of the radio
button before it was modified by the user. i'm new to this aspect of
a2k, so my impression is that while the user is sitting on a record,
every time he/she selects one of the radio buttons in the Frame1
options group the following happens 1) the value of the options
group's control source i dubbed 'Outcome_' changes and 2, the
OldValue property is updated to reflect the previous value of the
radio button's. i don't think that this is really how vba is set up
so if you can help straighthen me out, i'd appreciate it.

You're right, that's not how the OldValue property works. The OldValue
property holds whatever value the field held when the record was
initially loaded into the form, up until the moment the record is saved.
That means that if your Frame1 is bound to a field, and that field has a
value of 1, for example, when the form moves to a particular record,
then Frame1.OldValue will be 1 when the record is loaded *and will
remain 1 until the record is saved*, no matter how many times you change
the value of Frame1 before you save the record.

Further, if Frame1 is not bound to a field, its OldValue property is
always the same as its Value property, as there's no underlying record
to pull the OldValue property from.

 

[Ted]

hi dirk,

well i guess that did answer my posting, but what i'm left with is basically
an impossible objective. am i right about this as well?

-ted

 

[Dirk Goldgar]

hi dirk,

well i guess that did answer my posting, but what i'm left with is
basically an impossible objective. am i right about this as well?

No, not at all. But you can't do it using the OldValue property;
you'll have to keep track of the control's previous values yourself. As
I understand it, you want to know and take an action every time the
option group's value is updated by the user, regardless of how many
times the user changes the value of the control, whether the user saves
the current record with that value or not. And the action taken each
time depends on what the value was before the user changed it -- each
time -- as well as what the value is now.

If my understanding is correct, what you need to do is declare a
*module-level* variable which will always hold the value of the control
(before it was changed). Set the variable in the form's Current event,
and as the last thing you do in the control's AfterUpdate event. Refer
to this variable instead of the control's OldValue property, when you
want to know what the value "used to be".

Here are the relevant bits of code:

'----- start of example code for form's module -----
Option Compare Database
Option Explicit

Dim mvarOldValue As Variant


Private Sub Form_Current()

mvarOldValue = Me!Frame1

End Sub

Private Sub Frame1_AfterUpdate()

' ... code that refers to Frame1's current value
' and to mvarOldValue ...

Select Case Frame1

' ... whatever ...

End Select

' Now that we've taken whatever action we wanted based
' on the old and new values, update the "old value".

mvarOldValue = Me!Frame1

End Sub
'----- end of example code -----

 

[Ted]

hi dirk,

based on my reading of your interpretation, you're definitely on target with
what i'm aiming to do in this options group.

i will try to penetrate the mechanics of your suggested approach today and
respond 2u with feedback.

i'm really grateful for your insightful suggestions!!

with kindest regards,

-ted

 

[Ted]

hi dirk,

i've (hopefully) faithfully implemented your ideas into this and, by golly,
it looks as though they're doing it

thanks once again!!

-ted

 

[Dirk Goldgar]

hi dirk,

i've (hopefully) faithfully implemented your ideas into this and, by
golly, it looks as though they're doing it

thanks once again!!

Woohoo! You're welcome.