SQL query question

[Ted]

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

 

[George Nicholson]

Me.Future_Visit.Value = DLookup("FollowUp", "Query5")

DLookup is simply returning the first value for Followup that it finds in
Query 5. Is that what you intend? Since I don't know if Query5 returns
multiple or single records, it's hard to judge. DLookup has a third
argument which allows you to specify a Criteria. Maybe DLookup("NextVisit",
"Query3", "[Last Name] = '" & [Forms]![Screening Log]![Last Name] & "'")
would be better (and negate the need for query5 entirely)?

Query5 would then use that value to resolve

How is it using that value to resolve anything? As far as I can tell it is
simply grabbing the value from the form, but it does nothing except grab it.

I think your issue is that Dlookup is returning zip but I don't think
On-StudyDate has anything to do with why that is the case. Having said
that, you might reconsider moving your code from the control's BEFORE update
event anyway. AFAIK, the control won't officially have a "new" value until
the AFTER update event.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

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

 

[Ted]

part of my concern was the belief that the user after having modified the
value of OnStudyDate (at some potential time in the future) would see a blank
field where there had been a real date earliet (the futurevisit control) and
wonder 'doh? what happened? there was a date there before and now there
isn't? before having a chance to scroll to another record and return to the
same one to see that all was well with the world (owing to the aegis of the
OnCurrent VBA code), so i wanted to display the modified value of the
futurevisit. i hope this is making my intention clearer.

regarding the grabbing of the date in the SQL code....given my newbie
knowledge of the whole process, i assumed that once the user updated the
value of the OnStudyDate that the code would require using the newly captured
value from the Screening Log form (the assumption i'm making is that failing
to use the "Form!" syntax would force the code to utilize the previous (prior
to being modified) OnStudyDate and thus miss the point of all this entirely).
perhaps the 'old' version is the same as the 'updated' version in the table
by the time the Query5 gets activated (but i assumed that since it was being
used in a 'Before Update' event that that was not the case).

your response is appreciated but i don't see how it helps me all that much.

Me.Future_Visit.Value = DLookup("FollowUp", "Query5")

DLookup is simply returning the first value for Followup that it finds in
Query 5. Is that what you intend? Since I don't know if Query5 returns
multiple or single records, it's hard to judge. DLookup has a third
argument which allows you to specify a Criteria. Maybe DLookup("NextVisit",
"Query3", "[Last Name] = '" & [Forms]![Screening Log]![Last Name] & "'")
would be better (and negate the need for query5 entirely)?

Query5 would then use that value to resolve

How is it using that value to resolve anything? As far as I can tell it is
simply grabbing the value from the form, but it does nothing except grab it.

I think your issue is that Dlookup is returning zip but I don't think
On-StudyDate has anything to do with why that is the case. Having said
that, you might reconsider moving your code from the control's BEFORE update
event anyway. AFAIK, the control won't officially have a "new" value until
the AFTER update event.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

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