changing value of 1 control based on changes in another

[Ted]

i've done a bit of reading in this forum and concluded that using an
'OnChange' event to handle this is not advisable. what i read led me to
believe that the 'BeforeUpdate' update was the way. what i want to happen is
allow for any modifications to an 'On Study Date' field to affect the value
computed in another control which displays the date of a follow-up visit.

more to the point, the code i put into the 'On-Study Date' control was

Private Sub On_Study_Date_BeforeUpdate(Cancel As Integer)
Dim Future_Visit As Date
If Not IsNull(Me.IRB_) Then Me.Future_Visit.Value = DLookup("FollowUp",
"Query4")
End Sub


i don't know how much in the way of gory details are needed to puzzle out
why (when i make a change to the date in 'On-Study Date') it doesn't work (as
in cause the computed value of Future_Visit to occur but i wll fill in any
relevant blanks. oh, yes, i found that going to another record and then
returning to the one bearing the changed 'On-Study Date' DID result in an
updated value of the 'Future_Visit' but i'll be darned if i understood all
that.

any thoughts (bear in mind i consider myselfl to be a relative newbie to all
this).

tia,

-ted

 

[Ted]

in the time since submitting this posting i did a little testing and found
(remember i'm a newbie at this) that if i commented out the assignment
statement in the vba code and added one that read something like


Me.Future_Visit.Value = "#01/01/1900"

that the event which suggested that there's something possibly going on in
Query4 which i submit for the interested reader below:

SELECT S.[Last Name], S.[First Name], S.[IRB#], S.[Study #], S.[Seq#],
S.Months, S.[On-Study Date], S.Schedule, S.[F/U Status], S.FollowUp
FROM [Sort By Month] AS S INNER JOIN Query3 AS Q ON (S.FollowUp=Q.NextVisit)
AND (S.[Last Name]=Q.[Last Name])
WHERE (((S.[Last Name])=[Forms]![Screening Log]![Last Name]));

i going to go out on a limb and guess that the change made by the user's not
yet been saved and hence does not get 'carried over' and used by Query4! i
tried creating another query definition (Query5) which replaced S.[On-Study
Date] with what i thought would be a representation of its value on the form,
i.e. [Forms]![Screening Log]![On-Study Date], and using

If Not IsNull(Me.IRB_) Then Me.Future_Visit.Value = DLookup("FollowUp",
"Query5")

but that didn't do it either. for the time being, i'm stuck for ideas. any
thoughts?