Query in A@K that uses value of modified control's on form

[Ted]

When I alter a date in one control (onstudydate) on the form in my A2K mdb I
want to compute the value of another date (futurevisit) in another date
control on the same form.

In my afterupdate event for onstudydate I have the following VBA code

Private Sub On_Study_Date_AfterUpdate()
Dim Future_Visit As Date
Dim On_Study_Date As Date

If Me.Dirty Then
If Not IsNull(Me.IRB_) Then
Me.Future_Visit.Enabled = True
Me.Future_Visit.Locked = False
Me.Future_Visit.SetFocus
Me.Future_Visit.Value = DLookup("FollowUp", "Query5")
Me.On_Study_Date.SetFocus
Me.Future_Visit.Enabled = False
Me.Future_Visit.Locked = True
' Me.Future_Visit.Value = "01/01/1900"
End If
End If
End Sub

And, to flesh it out a little further, Query5 is written as under:

SELECT S.[Last Name], S.[First Name], S.[IRB#], S.[Study #], S.[Seq#],
S.Months, [Forms]![Screening Log]![On-Study Date] AS [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]));


A variation of this SQL code (Query4) works well when used with VBA in the
form’s oncurrent event property:

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

Where Query4’s written as follows:

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

Although scrolling from one record to the next seems to generate appropriate
values of futurevisit’s when onstudydate is already entered, when the user
enters/modifies the value of onstudydate the futurevisit control goes blank

My guess is that I’ve gone about as far as I can w/o waving the old white
flag. I think the idea of using

[Forms]![Screening Log]![On-Study Date] AS [On-Study Date]

isn’t working in Query5 as I’d envisaged, judging from the fact that when I
test Query5, “On-Study Date†is blank! The ‘idea’ was to ‘pass’ the newly
entered value of ‘On-Study Date’s to the Query5 so that it could be used to
compute Future Visit date’s.

I would like this get off the ground and to cut down the amount of time
chasing down possible leads via the inscrutable a2k help documentation MIS
installed on my desktop, so if someone out there would like to share their
thoughts, that’d be really great.