run-time error '2108'

[Ted]

not all that certain which forum would be most relevant for this posting, but
i'll just post it here and see what happens to bring it closer to getting a
resolution.

as the heading suggest, this is to do in whole/part to the said error
associated with the following code (i as a relative newbie am tackling):

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.SetFocus
Me.Future_Visit.Value = DLookup("FollowUp", "Query5")
Me.On_Study_Date.SetFocus
' Me.Future_Visit.Value = "01/01/1900"
End If
End If
End Sub


the SQL in Query5 looks like this:

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.[Last Name]=Q.[Last
Name]) AND (S.FollowUp=Q.NextVisit)
WHERE (((S.[Last Name])=[Forms]![Screening Log]![Last Name]));


in words, i have created a miniature of my production database with a few
salient controls to test out an improvement. the new functionality will
generate a 'follow-up' visit date once the 'onstudy date' is entered anew or
modified by the user;

there is some vba i place in the form's currentevent condition to handle the
case where the user scrolls through the mdb and the follow-up date needs
refreshing using the following:

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 is

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.[Last Name]=Q.[Last
Name]) AND (S.FollowUp=Q.NextVisit)
WHERE (((S.[Last Name])=[Forms]![Screening Log]![Last Name]));

which works just fine.

i next decided to tackle the case where the user modifies the onstudy date
and sees that the previous date in followup has gone blank by placing the vba
in the beforeupdate event and modifying query4 so that it would use the newly
typed value of onstudydate and that's where things began to get a bit
'escherian'.

for some reason i got the impression that i needed to use the setfocus
command in order to get the newly entered value of the onstudy date into the
Query5 code so that it could use it to compute the newer value and autofill
the followup control. that seems to have triggered this 2108 error message of
a2k's.

i know that i can workaround all this by using a conditional format which
turns the field with the followup date red when the onstudy date's modified
(once the user moved away from the record and returned to it again the saved
record would display the correct folloup date) but i like the idea of the new
date's appearing as the user is working on the active record. is there a way
out of this recursive occlusion?

 

[Klatuu]

The error description should give you a clue:

You must save the field before you execute the GoToControl action, the
GoToControl method, or the SetFocus method. You tried to move the focus to
another control using the SetFocus method, GoToControl action, or the
GoToControl method. Set the macro or method to the AfterUpdate property
instead of the BeforeUpdate property so it saves the field before changing
the focus.

not all that certain which forum would be most relevant for this posting, but
i'll just post it here and see what happens to bring it closer to getting a
resolution.

as the heading suggest, this is to do in whole/part to the said error
associated with the following code (i as a relative newbie am tackling):

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.SetFocus
Me.Future_Visit.Value = DLookup("FollowUp", "Query5")
Me.On_Study_Date.SetFocus
' Me.Future_Visit.Value = "01/01/1900"
End If
End If
End Sub


the SQL in Query5 looks like this:

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.[Last Name]=Q.[Last
Name]) AND (S.FollowUp=Q.NextVisit)
WHERE (((S.[Last Name])=[Forms]![Screening Log]![Last Name]));


in words, i have created a miniature of my production database with a few
salient controls to test out an improvement. the new functionality will
generate a 'follow-up' visit date once the 'onstudy date' is entered anew or
modified by the user;

there is some vba i place in the form's currentevent condition to handle the
case where the user scrolls through the mdb and the follow-up date needs
refreshing using the following:

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 is

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.[Last Name]=Q.[Last
Name]) AND (S.FollowUp=Q.NextVisit)
WHERE (((S.[Last Name])=[Forms]![Screening Log]![Last Name]));

which works just fine.

i next decided to tackle the case where the user modifies the onstudy date
and sees that the previous date in followup has gone blank by placing the vba
in the beforeupdate event and modifying query4 so that it would use the newly
typed value of onstudydate and that's where things began to get a bit
'escherian'.

for some reason i got the impression that i needed to use the setfocus
command in order to get the newly entered value of the onstudy date into the
Query5 code so that it could use it to compute the newer value and autofill
the followup control. that seems to have triggered this 2108 error message of
a2k's.

i know that i can workaround all this by using a conditional format which
turns the field with the followup date red when the onstudy date's modified
(once the user moved away from the record and returned to it again the saved
record would display the correct folloup date) but i like the idea of the new
date's appearing as the user is working on the active record. is there a way
out of this recursive occlusion?

 

[Ted]

it did, but even using your afterupdate approach

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

Private Sub On_Study_Date_AfterUpdate()
Dim Future_Visit As Date
If Me.Dirty Then
If Not IsNull(Me.IRB_) Then
Me.Future_Visit.SetFocus
Me.Future_Visit.Value = DLookup("FollowUp", "Query5")
Me.On_Study_Date.SetFocus
' Me.Future_Visit.Value = "01/01/1900"
End If
End If
End Sub

it still seems not to update the future_visit's value :-(

The error description should give you a clue:

You must save the field before you execute the GoToControl action, the
GoToControl method, or the SetFocus method. You tried to move the focus to
another control using the SetFocus method, GoToControl action, or the
GoToControl method. Set the macro or method to the AfterUpdate property
instead of the BeforeUpdate property so it saves the field before changing
the focus.

not all that certain which forum would be most relevant for this posting, but
i'll just post it here and see what happens to bring it closer to getting a
resolution.

as the heading suggest, this is to do in whole/part to the said error
associated with the following code (i as a relative newbie am tackling):

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.SetFocus
Me.Future_Visit.Value = DLookup("FollowUp", "Query5")
Me.On_Study_Date.SetFocus
' Me.Future_Visit.Value = "01/01/1900"
End If
End If
End Sub


the SQL in Query5 looks like this:

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.[Last Name]=Q.[Last
Name]) AND (S.FollowUp=Q.NextVisit)
WHERE (((S.[Last Name])=[Forms]![Screening Log]![Last Name]));


in words, i have created a miniature of my production database with a few
salient controls to test out an improvement. the new functionality will
generate a 'follow-up' visit date once the 'onstudy date' is entered anew or
modified by the user;

there is some vba i place in the form's currentevent condition to handle the
case where the user scrolls through the mdb and the follow-up date needs
refreshing using the following:

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 is

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.[Last Name]=Q.[Last
Name]) AND (S.FollowUp=Q.NextVisit)
WHERE (((S.[Last Name])=[Forms]![Screening Log]![Last Name]));

which works just fine.

i next decided to tackle the case where the user modifies the onstudy date
and sees that the previous date in followup has gone blank by placing the vba
in the beforeupdate event and modifying query4 so that it would use the newly
typed value of onstudydate and that's where things began to get a bit
'escherian'.

for some reason i got the impression that i needed to use the setfocus
command in order to get the newly entered value of the onstudy date into the
Query5 code so that it could use it to compute the newer value and autofill
the followup control. that seems to have triggered this 2108 error message of
a2k's.

i know that i can workaround all this by using a conditional format which
turns the field with the followup date red when the onstudy date's modified
(once the user moved away from the record and returned to it again the saved
record would display the correct folloup date) but i like the idea of the new
date's appearing as the user is working on the active record. is there a way
out of this recursive occlusion?

 

[Klatuu]

Are your DLookups returning anything? The syntax does not look right. Also,
I may have responded too soon with my last post. It is too late to put data
in a control in the form After Update.

I would suggest you tet the DLookups to see what they are returning and I
would think about moving the code to the controls you are trying to load
rather than using the Form level.

it did, but even using your afterupdate approach

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

Private Sub On_Study_Date_AfterUpdate()
Dim Future_Visit As Date
If Me.Dirty Then
If Not IsNull(Me.IRB_) Then
Me.Future_Visit.SetFocus
Me.Future_Visit.Value = DLookup("FollowUp", "Query5")
Me.On_Study_Date.SetFocus
' Me.Future_Visit.Value = "01/01/1900"
End If
End If
End Sub

it still seems not to update the future_visit's value :-(

The error description should give you a clue:

You must save the field before you execute the GoToControl action, the
GoToControl method, or the SetFocus method. You tried to move the focus to
another control using the SetFocus method, GoToControl action, or the
GoToControl method. Set the macro or method to the AfterUpdate property
instead of the BeforeUpdate property so it saves the field before changing
the focus.

not all that certain which forum would be most relevant for this posting, but
i'll just post it here and see what happens to bring it closer to getting a
resolution.

as the heading suggest, this is to do in whole/part to the said error
associated with the following code (i as a relative newbie am tackling):

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.SetFocus
Me.Future_Visit.Value = DLookup("FollowUp", "Query5")
Me.On_Study_Date.SetFocus
' Me.Future_Visit.Value = "01/01/1900"
End If
End If
End Sub


the SQL in Query5 looks like this:

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.[Last Name]=Q.[Last
Name]) AND (S.FollowUp=Q.NextVisit)
WHERE (((S.[Last Name])=[Forms]![Screening Log]![Last Name]));


in words, i have created a miniature of my production database with a few
salient controls to test out an improvement. the new functionality will
generate a 'follow-up' visit date once the 'onstudy date' is entered anew or
modified by the user;

there is some vba i place in the form's currentevent condition to handle the
case where the user scrolls through the mdb and the follow-up date needs
refreshing using the following:

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 is

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.[Last Name]=Q.[Last
Name]) AND (S.FollowUp=Q.NextVisit)
WHERE (((S.[Last Name])=[Forms]![Screening Log]![Last Name]));

which works just fine.

i next decided to tackle the case where the user modifies the onstudy date
and sees that the previous date in followup has gone blank by placing the vba
in the beforeupdate event and modifying query4 so that it would use the newly
typed value of onstudydate and that's where things began to get a bit
'escherian'.

for some reason i got the impression that i needed to use the setfocus
command in order to get the newly entered value of the onstudy date into the
Query5 code so that it could use it to compute the newer value and autofill
the followup control. that seems to have triggered this 2108 error message of
a2k's.

i know that i can workaround all this by using a conditional format which
turns the field with the followup date red when the onstudy date's modified
(once the user moved away from the record and returned to it again the saved
record would display the correct folloup date) but i like the idea of the new
date's appearing as the user is working on the active record. is there a way
out of this recursive occlusion?

 

[Ted]

my query4 is working just fine on the oncurrent event of the form level! my
query5 is returning null values (which as i began to say i could 'workaround'
by deploying a conditional format instruction). query5 was written so as to
use the newly entered value of the onstudydate.

i'm a little confused by this pair of sentences below, klatuu. regarding
moving the code tot he controls you are trying to load, i am using the
afterupdate of the onstudydate control and not the form's afterupdate. but if
it is too late to put data in a control in the form after update, then what's
left?

Are your DLookups returning anything? The syntax does not look right. Also,
I may have responded too soon with my last post. It is too late to put data
in a control in the form After Update.

I would suggest you tet the DLookups to see what they are returning and I
would think about moving the code to the controls you are trying to load
rather than using the Form level.

it did, but even using your afterupdate approach

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

Private Sub On_Study_Date_AfterUpdate()
Dim Future_Visit As Date
If Me.Dirty Then
If Not IsNull(Me.IRB_) Then
Me.Future_Visit.SetFocus
Me.Future_Visit.Value = DLookup("FollowUp", "Query5")
Me.On_Study_Date.SetFocus
' Me.Future_Visit.Value = "01/01/1900"
End If
End If
End Sub

it still seems not to update the future_visit's value :-(

The error description should give you a clue:

You must save the field before you execute the GoToControl action, the
GoToControl method, or the SetFocus method. You tried to move the focus to
another control using the SetFocus method, GoToControl action, or the
GoToControl method. Set the macro or method to the AfterUpdate property
instead of the BeforeUpdate property so it saves the field before changing
the focus.

:

not all that certain which forum would be most relevant for this posting, but
i'll just post it here and see what happens to bring it closer to getting a
resolution.

as the heading suggest, this is to do in whole/part to the said error
associated with the following code (i as a relative newbie am tackling):

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.SetFocus
Me.Future_Visit.Value = DLookup("FollowUp", "Query5")
Me.On_Study_Date.SetFocus
' Me.Future_Visit.Value = "01/01/1900"
End If
End If
End Sub


the SQL in Query5 looks like this:

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.[Last Name]=Q.[Last
Name]) AND (S.FollowUp=Q.NextVisit)
WHERE (((S.[Last Name])=[Forms]![Screening Log]![Last Name]));


in words, i have created a miniature of my production database with a few
salient controls to test out an improvement. the new functionality will
generate a 'follow-up' visit date once the 'onstudy date' is entered anew or
modified by the user;

there is some vba i place in the form's currentevent condition to handle the
case where the user scrolls through the mdb and the follow-up date needs
refreshing using the following:

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 is

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.[Last Name]=Q.[Last
Name]) AND (S.FollowUp=Q.NextVisit)
WHERE (((S.[Last Name])=[Forms]![Screening Log]![Last Name]));

which works just fine.

i next decided to tackle the case where the user modifies the onstudy date
and sees that the previous date in followup has gone blank by placing the vba
in the beforeupdate event and modifying query4 so that it would use the newly
typed value of onstudydate and that's where things began to get a bit
'escherian'.

for some reason i got the impression that i needed to use the setfocus
command in order to get the newly entered value of the onstudy date into the
Query5 code so that it could use it to compute the newer value and autofill
the followup control. that seems to have triggered this 2108 error message of
a2k's.

i know that i can workaround all this by using a conditional format which
turns the field with the followup date red when the onstudy date's modified
(once the user moved away from the record and returned to it again the saved
record would display the correct folloup date) but i like the idea of the new
date's appearing as the user is working on the active record. is there a way
out of this recursive occlusion?

 

[Ted]

the query4 has been working a-ok all along (on the form's oncurrent event
form). i re-wrote query5 to try to get at what i am talking about 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.[Last Name] = Q.[Last
Name]) AND (S.FollowUp = Q.NextVisit)
WHERE (((S.[Last Name])=[Forms]![Screening Log]![Last Name]) AND
((S.[On-Study Date])=[Forms]![Screening Log]![On-StudyDate]));


so that now we're using two criteria, the old criteria the lastname on the
form equal the one in the S table AND that the on-studydate in the form be
the same as the one in the table. this still doesn't work and correct me i am
wrong but it's because the date in the underlying table does not equal the
one the user has just changed it to be.

the vba code is pretty much as before:
Option Compare Database
Private Sub Form_Current() ' < -- NOTE THIS WORKS FINE
Dim Future_Visit As Date
If Not IsNull(Me.IRB_) Then Me.Future_Visit.Value = DLookup("FollowUp",
"Query4")
End Sub

Private Sub On_Study_Date_AfterUpdate() ' <--- note this is on CONTROL not
FORM
Dim Future_Visit As Date
If Me.Dirty Then
If Not IsNull(Me.IRB_) Then
Me.Future_Visit.SetFocus
Me.Future_Visit.Value = DLookup("FollowUp", "Query5")
Me.On_Study_Date.SetFocus
' Me.Future_Visit.Value = "01/01/1900"
End If
End If
End Sub

Are your DLookups returning anything? The syntax does not look right. Also,
I may have responded too soon with my last post. It is too late to put data
in a control in the form After Update.

I would suggest you tet the DLookups to see what they are returning and I
would think about moving the code to the controls you are trying to load
rather than using the Form level.

it did, but even using your afterupdate approach

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

Private Sub On_Study_Date_AfterUpdate()
Dim Future_Visit As Date
If Me.Dirty Then
If Not IsNull(Me.IRB_) Then
Me.Future_Visit.SetFocus
Me.Future_Visit.Value = DLookup("FollowUp", "Query5")
Me.On_Study_Date.SetFocus
' Me.Future_Visit.Value = "01/01/1900"
End If
End If
End Sub

it still seems not to update the future_visit's value :-(

The error description should give you a clue:

You must save the field before you execute the GoToControl action, the
GoToControl method, or the SetFocus method. You tried to move the focus to
another control using the SetFocus method, GoToControl action, or the
GoToControl method. Set the macro or method to the AfterUpdate property
instead of the BeforeUpdate property so it saves the field before changing
the focus.

:

not all that certain which forum would be most relevant for this posting, but
i'll just post it here and see what happens to bring it closer to getting a
resolution.

as the heading suggest, this is to do in whole/part to the said error
associated with the following code (i as a relative newbie am tackling):

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.SetFocus
Me.Future_Visit.Value = DLookup("FollowUp", "Query5")
Me.On_Study_Date.SetFocus
' Me.Future_Visit.Value = "01/01/1900"
End If
End If
End Sub


the SQL in Query5 looks like this:

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.[Last Name]=Q.[Last
Name]) AND (S.FollowUp=Q.NextVisit)
WHERE (((S.[Last Name])=[Forms]![Screening Log]![Last Name]));


in words, i have created a miniature of my production database with a few
salient controls to test out an improvement. the new functionality will
generate a 'follow-up' visit date once the 'onstudy date' is entered anew or
modified by the user;

there is some vba i place in the form's currentevent condition to handle the
case where the user scrolls through the mdb and the follow-up date needs
refreshing using the following:

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 is

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.[Last Name]=Q.[Last
Name]) AND (S.FollowUp=Q.NextVisit)
WHERE (((S.[Last Name])=[Forms]![Screening Log]![Last Name]));

which works just fine.

i next decided to tackle the case where the user modifies the onstudy date
and sees that the previous date in followup has gone blank by placing the vba
in the beforeupdate event and modifying query4 so that it would use the newly
typed value of onstudydate and that's where things began to get a bit
'escherian'.

for some reason i got the impression that i needed to use the setfocus
command in order to get the newly entered value of the onstudy date into the
Query5 code so that it could use it to compute the newer value and autofill
the followup control. that seems to have triggered this 2108 error message of
a2k's.

i know that i can workaround all this by using a conditional format which
turns the field with the followup date red when the onstudy date's modified
(once the user moved away from the record and returned to it again the saved
record would display the correct folloup date) but i like the idea of the new
date's appearing as the user is working on the active record. is there a way
out of this recursive occlusion?

 

[Klatuu]

The problem I am having is that I don't understand the way you are using the
DLookup function. The syntax is different from anything I have every seen,
so I don't know that I can help you.

the query4 has been working a-ok all along (on the form's oncurrent event
form). i re-wrote query5 to try to get at what i am talking about 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.[Last Name] = Q.[Last
Name]) AND (S.FollowUp = Q.NextVisit)
WHERE (((S.[Last Name])=[Forms]![Screening Log]![Last Name]) AND
((S.[On-Study Date])=[Forms]![Screening Log]![On-StudyDate]));


so that now we're using two criteria, the old criteria the lastname on the
form equal the one in the S table AND that the on-studydate in the form be
the same as the one in the table. this still doesn't work and correct me i am
wrong but it's because the date in the underlying table does not equal the
one the user has just changed it to be.

the vba code is pretty much as before:
Option Compare Database
Private Sub Form_Current() ' < -- NOTE THIS WORKS FINE
Dim Future_Visit As Date
If Not IsNull(Me.IRB_) Then Me.Future_Visit.Value = DLookup("FollowUp",
"Query4")
End Sub

Private Sub On_Study_Date_AfterUpdate() ' <--- note this is on CONTROL not
FORM
Dim Future_Visit As Date
If Me.Dirty Then
If Not IsNull(Me.IRB_) Then
Me.Future_Visit.SetFocus
Me.Future_Visit.Value = DLookup("FollowUp", "Query5")
Me.On_Study_Date.SetFocus
' Me.Future_Visit.Value = "01/01/1900"
End If
End If
End Sub

Are your DLookups returning anything? The syntax does not look right. Also,
I may have responded too soon with my last post. It is too late to put data
in a control in the form After Update.

I would suggest you tet the DLookups to see what they are returning and I
would think about moving the code to the controls you are trying to load
rather than using the Form level.

it did, but even using your afterupdate approach

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

Private Sub On_Study_Date_AfterUpdate()
Dim Future_Visit As Date
If Me.Dirty Then
If Not IsNull(Me.IRB_) Then
Me.Future_Visit.SetFocus
Me.Future_Visit.Value = DLookup("FollowUp", "Query5")
Me.On_Study_Date.SetFocus
' Me.Future_Visit.Value = "01/01/1900"
End If
End If
End Sub

it still seems not to update the future_visit's value :-(



:

The error description should give you a clue:

You must save the field before you execute the GoToControl action, the
GoToControl method, or the SetFocus method. You tried to move the focus to
another control using the SetFocus method, GoToControl action, or the
GoToControl method. Set the macro or method to the AfterUpdate property
instead of the BeforeUpdate property so it saves the field before changing
the focus.

:

not all that certain which forum would be most relevant for this posting, but
i'll just post it here and see what happens to bring it closer to getting a
resolution.

as the heading suggest, this is to do in whole/part to the said error
associated with the following code (i as a relative newbie am tackling):

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.SetFocus
Me.Future_Visit.Value = DLookup("FollowUp", "Query5")
Me.On_Study_Date.SetFocus
' Me.Future_Visit.Value = "01/01/1900"
End If
End If
End Sub


the SQL in Query5 looks like this:

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.[Last Name]=Q.[Last
Name]) AND (S.FollowUp=Q.NextVisit)
WHERE (((S.[Last Name])=[Forms]![Screening Log]![Last Name]));


in words, i have created a miniature of my production database with a few
salient controls to test out an improvement. the new functionality will
generate a 'follow-up' visit date once the 'onstudy date' is entered anew or
modified by the user;

there is some vba i place in the form's currentevent condition to handle the
case where the user scrolls through the mdb and the follow-up date needs
refreshing using the following:

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 is

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.[Last Name]=Q.[Last
Name]) AND (S.FollowUp=Q.NextVisit)
WHERE (((S.[Last Name])=[Forms]![Screening Log]![Last Name]));

which works just fine.

i next decided to tackle the case where the user modifies the onstudy date
and sees that the previous date in followup has gone blank by placing the vba
in the beforeupdate event and modifying query4 so that it would use the newly
typed value of onstudydate and that's where things began to get a bit
'escherian'.

for some reason i got the impression that i needed to use the setfocus
command in order to get the newly entered value of the onstudy date into the
Query5 code so that it could use it to compute the newer value and autofill
the followup control. that seems to have triggered this 2108 error message of
a2k's.

i know that i can workaround all this by using a conditional format which
turns the field with the followup date red when the onstudy date's modified
(once the user moved away from the record and returned to it again the saved
record would display the correct folloup date) but i like the idea of the new
date's appearing as the user is working on the active record. is there a way
out of this recursive occlusion?