G
Guest
i am using a2k and a relative newbie. with that said, i have this SQL query
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_X] 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]));
the part i think is the one of concern is as follows:
[Forms]![Screening Log]![On-Study Date] AS [On-Study Date]
because i am trying to select the value of a control from an open form
("Screening Log") in a BeforeUpdate event which uses the following VBA code i
tried to cobble together:
Private Sub On_Study_Date_BeforeUpdate(Cancel As Integer)
Dim Future_Visit As Date
If Me.Dirty Then
If Not IsNull(Me.IRB_) Then
Me.Future_Visit.Value = DLookup("FollowUp", "Query5")
' Me.Future_Visit.Value = "01/01/1900"
End If
End If
End Sub
what happens when i run it is pretty much nothing, meaning the value of
Me.Future_Vist is/becomes null (devoid of information) when i update
On_Study_Date. the field i commented out however has the desired effect of
entering jan 1st, 1900 into the control when i comment out the line above it,
so i am kind of forced to conclude there's something going on in my SQL code.
my thinking was that once the user entered a new/updated value of On Study
Date in the Screening Log form that that would be available to Query 5 and
that Query5 would then use that value to resolve
my OnCurrent event has the following VBA code
Private Sub Form_Current()
Dim Future_Visit As Date
If Not IsNull(Me.IRB_) Then Me.Future_Visit.Value = DLookup("FollowUp",
"Query4")
End Sub
which uses the Query4 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]));
which works every time the user scrolls to another record and returns to the
recently updated one......so, ultimately, OnCurrent is handling the issue of
the 'missing value' of Future Visit, however, the user might feel
uncomfortable with the 'void' created by the recently made update and i'd
like to workaround it (using the Before Update event).
any thoughts?
-ted
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_X] 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]));
the part i think is the one of concern is as follows:
[Forms]![Screening Log]![On-Study Date] AS [On-Study Date]
because i am trying to select the value of a control from an open form
("Screening Log") in a BeforeUpdate event which uses the following VBA code i
tried to cobble together:
Private Sub On_Study_Date_BeforeUpdate(Cancel As Integer)
Dim Future_Visit As Date
If Me.Dirty Then
If Not IsNull(Me.IRB_) Then
Me.Future_Visit.Value = DLookup("FollowUp", "Query5")
' Me.Future_Visit.Value = "01/01/1900"
End If
End If
End Sub
what happens when i run it is pretty much nothing, meaning the value of
Me.Future_Vist is/becomes null (devoid of information) when i update
On_Study_Date. the field i commented out however has the desired effect of
entering jan 1st, 1900 into the control when i comment out the line above it,
so i am kind of forced to conclude there's something going on in my SQL code.
my thinking was that once the user entered a new/updated value of On Study
Date in the Screening Log form that that would be available to Query 5 and
that Query5 would then use that value to resolve
my OnCurrent event has the following VBA code
Private Sub Form_Current()
Dim Future_Visit As Date
If Not IsNull(Me.IRB_) Then Me.Future_Visit.Value = DLookup("FollowUp",
"Query4")
End Sub
which uses the Query4 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]));
which works every time the user scrolls to another record and returns to the
recently updated one......so, ultimately, OnCurrent is handling the issue of
the 'missing value' of Future Visit, however, the user might feel
uncomfortable with the 'void' created by the recently made update and i'd
like to workaround it (using the Before Update event).
any thoughts?
-ted