Hi,
I’m back on this thread again…..
When I developed my concept database (mdb) file I tended to look at what
changes were happening at the level of the user’s perspective (i.e. reviewing
changes expected appearing on the form itself) and not going through the VBA
code one step at a time. As such, I was somewhat surprised when I decided to
see what would happen if I were to try to ‘split’ the database into an FE/BE
configuration and port the BE onto my shared drive. The throughput seemed
almost glacial. I’ve not done that before and I claim to be a newbie at all
of this but my ambitions drive me on to probe the ‘mystery’ and hopefully put
some ‘oomph’ into the user’s experience. Having said that much, I think it’s
necessary to know a bit more about the pieces of this puzzle, so let’s look
at my On Current code (below):
Private Sub Form_Current()
Dim Future_Visit As Date
If Not IsNull(Me.IRB_) Then
Me.Future_Visit = DLookup("FollowUp", "Query4", "[Forms]![Screening
Log]![F/U Status] = 'Alive'")
Me.Months = DLookup("Months", "Query4", "[Forms]![Screening Log]![F/U
Status] = 'Alive'")
End If
End Sub
Which I originally created in order to generate the values of Future_Visit
and Months by simply scrolling through the records in the database (since
there are quite a few with the necessary ingredients already input to get
this up and running.
Query4 SQLwise reads:
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 “Sort By Month†reads:
SELECT [Patients on F/U].[Last Name], [Patients on F/U].[First Name],
[Patients on F/U].[IRB#], [Patients on F/U].[Study #], [Patients on
F/U].[Seq#], [Formula Table].Months, [Patients on F/U].[On-Study Date],
[Patients on F/U].Schedule, [Patients on F/U].[F/U Status],
IIf(DatePart('w',DateAdd("m",[Months],[On-Study
Date]))=7,DateAdd("m",[Months],[On-Study
Date])-1,IIf(DatePart('w',DateAdd("m",[Months],[On-Study
Date]))=1,DateAdd("m",[Months],[On-Study
Date])+1,DateAdd("m",[Months],[On-Study Date]))) AS FollowUp
FROM [Formula Table] INNER JOIN [Patients on F/U] ON [Formula Table].[Dummy
Number] = [Patients on F/U].Dummy
WHERE (((IIf(DatePart('w',DateAdd("m",[Months],[On-Study
Date]))=7,DateAdd("m",[Months],[On-Study
Date])-1,IIf(DatePart('w',DateAdd("m",[Months],[On-Study
Date]))=1,DateAdd("m",[Months],[On-Study
Date])+1,DateAdd("m",[Months],[On-Study Date]))))>=Date()))
ORDER BY [Patients on F/U].[Last Name],
IIf(DatePart('w',DateAdd("m",[Months],[On-Study
Date]))=7,DateAdd("m",[Months],[On-Study
Date])-1,IIf(DatePart('w',DateAdd("m",[Months],[On-Study
Date]))=1,DateAdd("m",[Months],[On-Study
Date])+1,DateAdd("m",[Months],[On-Study Date])));
And Query3 reads:
SELECT [Sort By Month].[Last Name], Min([Sort By Month].FollowUp) AS NextVisit
FROM [Sort By Month]
GROUP BY [Sort By Month].[Last Name];
As I think I wrote earlier, after creating the above, it occurred to me that
users would actually be entering and/or possibly even modifying the value(s)
of the ‘On-Study Date’ control and that would mean having to adjust the value
of ‘Future Visit’ and ‘Months’ to reflect that fact, so in the After Update
Event Property of the ‘On Study Date’ control’s I wrote the following VBA to
handle user input modifications to this control:
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 = DLookup("FollowUp", "Query5 B", "[Forms]![Screening
Log]![F/U Status] = 'Alive'")
Me.Months = DLookup("Months", "Query5 B", "[Forms]![Screening Log]![F/U
Status] = 'Alive'")
Me.Schedule.SetFocus
DoCmd.GoToRecord , , acNext <<<<<
DoCmd.GoToRecord , , acPrevious <<<<<
End If
End If
End Sub
Where Query 5B reads:
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 B] AS S INNER JOIN [Query3 B] AS Q ON (S.[Last
Name]=Q.[Last Name]) AND (S.FollowUp=Q.NextVisit)
WHERE (((S.[Last Name])=[Forms]![Screening Log]![Last Name]));
And ‘Sort By Month B’ reads:
SELECT [Patients on F/U].[Last Name], [Patients on F/U].[First Name],
[Patients on F/U].[IRB#], [Patients on F/U].[Study #], [Patients on
F/U].[Seq#], [Formula Table].[Months], [Forms]![Screening Log]![On-Study
Date] AS [On-Study Date], [Patients on F/U].[Schedule], [Patients on
F/U].[F/U Status], IIf(DatePart('w',DateAdd("m",[Months],[On-Study
Date]))=7,DateAdd("m",[Months],[On-Study
Date])-1,IIf(DatePart('w',DateAdd("m",[Months],[On-Study
Date]))=1,DateAdd("m",[Months],[On-Study
Date])+1,DateAdd("m",[Months],[On-Study Date]))) AS FollowUp
FROM [Formula Table] INNER JOIN [Patients on F/U] ON [Formula Table].[Dummy
Number]=[Patients on F/U].[Dummy]
WHERE (((IIf(DatePart('w',DateAdd("m",[Months],[On-Study
Date]))=7,DateAdd("m",[Months],[On-Study
Date])-1,IIf(DatePart('w',DateAdd("m",[Months],[On-Study
Date]))=1,DateAdd("m",[Months],[On-Study
Date])+1,DateAdd("m",[Months],[On-Study Date]))))>=Date()))
ORDER BY [Patients on F/U].[Last Name],
IIf(DatePart('w',DateAdd("m",[Months],[On-Study
Date]))=7,DateAdd("m",[Months],[On-Study
Date])-1,IIf(DatePart('w',DateAdd("m",[Months],[On-Study
Date]))=1,DateAdd("m",[Months],[On-Study
Date])+1,DateAdd("m",[Months],[On-Study Date])));
And lastly, Query 3B reads:
SELECT [Sort By Month B].[Last Name], Min([Sort By Month B].FollowUp) AS
NextVisit
FROM [Sort By Month B]
GROUP BY [Sort By Month B].[Last Name];
I wrote earlier about ‘Requerying’ ‘because’ I got the feeling that w/o that
my After Update VBA was not working as I wanted it to and that inserting that
instruction into the VBA put the 1st record in the mdb into view which would
not be what the user would likely be expecting – (s)he would anticipate that
simply adjusting the OnStudy Date would have the effect of changing the value
of the Future Visit for that same ‘record’. So I kleverly thought I would
insert the two move commands we see:
DoCmd.GoToRecord , , acNext <<<<<
DoCmd.GoToRecord , , acPrevious <<<<<
And while this seems to work just fine on the unsplit mdb, performance slows
dramatically on the split version. I decided to try using some breakpoints I
learned about in my code and tried various omissions of the code elements in
the AfterUpdate VBA and the like and I now find that when I modify On Study
Date, I can compute the desired Future Visit and the Months values using just
the Dlookup functions in the On Current event propery’s VBA code…. provided I
keep the pair of GoToRecord instructions in the After Update Event Propery.
If correct, I kind of wish that I ‘d gone through the step-at-a-time
diagnostic beforehand, but as they say you learn from experience. But it
seems as if there’s got to be something around a little ‘slicker’ for this.
For one thing, after the first user to try the kicked up version goes/scrolls
through every record, it doesn’t seem like the OnCurrent Dlookup functions
need to run every time you’re on the Current Record – just when there’s been
a change in the On Study (using the GoToRecord Docmd’s). Secondly, the GoTos
have something vaguely to do with needing to get the data into the table
underlying the form in order to make the values available to the Queries. Is
my theory correct?
-Ted
tina said:
the find action is a method of the Recordset, as
CurrentDb.Recordset.FindFirst "PKFieldName = " _
& VariableName
if the primary key (PK) field is Text data type, rather than numeric, the
syntax is
CurrentDb.Recordset.FindFirst "PKFieldName = '" _
& VariableName & "'"
hth
Ted said:
what i 'decided' to do in the interim up to reading your reply was to remove
the 'requery' and use docmd to move the record to the 'next' and then to the
'previous' which nets out to end up on the same record. it's kind of kluggee
(speeling) but it seems to be working. this may be the case because of the
code in my 'OnCurrent' event property which this is invoking (yes?), but i'd
really like to try your approach (below)
btw, another "find the record again" option is to save the current record's
primary key value in a variable, do the requery, and then Find the record
again based on the primary key variable.
i noticed there ddin't seem to be a docmd find record option that did
exactly that....can you fill in some of the missing details?
tina said:
if you requery the form, the focus *will* move to the first record. first,
make sure that a requery is necessary to achieve whatever your aim is. if it
is, then about the best you can do is to bookmark the record before the
requery command, and then write additional code to return to it afer the
requery. i don't recall that i've ever used Bookmark, so i've no details to
give you. but if you search VBA Help and perhaps search these newsgroups,
you should find more information.
btw, another "find the record again" option is to save the current record's
primary key value in a variable, do the requery, and then Find the record
again based on the primary key variable.
hth
hi tina,
it's 'basically' working as i intended for it, but i can't figure out how
to
suppress the fact that it moves to the 1st record in the table underlying
the
form when i modify the onstudydate and hit the 'tab' button.
any thoughts? i can post the vba (i've added a few more bells and whistles
to this), some of them use the 'Requery' feature.
-ted
:
well, if the SortByMonth query is where the future_visit date is being
calculated, and if you want that calculation to be based on the
onstudydate
entered in the form, then you need to refer to the form control *in that
query*. pulling in the form control's value in Query5 is not affecting
the
future_visit date calculation at all.
in query SortByMonth, it looks like you can replace each reference to
"[On-Study-Date]" in the IIf() functions, with [Forms]![Screening
Log]![On-Study Date], and add criteria to select only the one patient
you're
concerned with. and that raises another point: you're currently
filtering
Query5 by matching the patient's last name with [Forms]![Screening
Log]![Last Name]. but is it absolutely impossible that you will never
have
two patients with the same last name in the recordset returned by the
query
before the last name criteria is applied? can you set a criteria on a
primary/foreign key field that uniquely identifies each patient,
instead? or
if the LastName field is part of a combination primary key, is it
appropriate to set criteria on all the fields in the key, to ensure that
the
correct patient data is returned?
hth
i'm glad to have read your comments tina and will tlry to put some
more
flesh
on this to hopefully put it into sharper focus.
originally, i had not considered what would happen to futurevisit when
the
operator entered the onstudydate. onstudydate has 'always' been a
factor
in
the database and i recently decided to add _this_ ability to compute
the
date
of the person's futurevisit. there is a table having the following a
constant
('1') and a number corresponding with how many months in the future
the
next
visit is relative to the onstudydate (1,3,6,9,12,......240). this is
called
the Formula Table and is joined with the individual's datatable which
has
a
similar constant, lastname, onstudy date control in this testbed
database
using this 'hairy' SQL code
SELECT [Patients on F/U].[Last Name], [Patients on F/U].[First Name],
[Patients on F/U].[IRB#], [Patients on F/U].[Study #], [Patients on
F/U].[Seq#], [Formula Table].Months, [Patients on F/U].[On-Study
Date],
[Patients on F/U].Schedule, [Patients on F/U].[F/U Status],
IIf(DatePart('w',DateAdd("m",[Months],[On-Study
Date]))=7,DateAdd("m",[Months],[On-Study
Date])-1,IIf(DatePart('w',DateAdd("m",[Months],[On-Study
Date]))=1,DateAdd("m",[Months],[On-Study
Date])+1,DateAdd("m",[Months],[On-Study Date]))) AS FollowUp
FROM [Formula Table] INNER JOIN [Patients on F/U] ON [Formula
Table].[Dummy
Number] = [Patients on F/U].Dummy
WHERE (((IIf(DatePart('w',DateAdd("m",[Months],[On-Study
Date]))=7,DateAdd("m",[Months],[On-Study
Date])-1,IIf(DatePart('w',DateAdd("m",[Months],[On-Study
Date]))=1,DateAdd("m",[Months],[On-Study
Date])+1,DateAdd("m",[Months],[On-Study Date]))))>=Date()))
ORDER BY [Patients on F/U].[Last Name],
IIf(DatePart('w',DateAdd("m",[Months],[On-Study
Date]))=7,DateAdd("m",[Months],[On-Study
Date])-1,IIf(DatePart('w',DateAdd("m",[Months],[On-Study
Date]))=1,DateAdd("m",[Months],[On-Study
Date])+1,DateAdd("m",[Months],[On-Study Date])));
the above is the 'Sort By Month' query. Query3 is as written below:
SELECT [Last Name], Min([FollowUp]) AS NextVisit
FROM [Sort By Month]
GROUP BY [Last Name];
and acts to pick up the earliest followup visit from Sort By Month ---
resulting in one record per person.
as i reflect on the details of Query5's percursors, i am compelled to
believe that the computation of the futurevisit (which got handled in
Sort
By
Month) would require the value of [Forms]![Screening Log]![Last Name]
..
am i
correct? if not that, then where would the 'updated' value the user's
entering into the form be used. if so, then would it involve a global
replacement wherever[On-Study Date] appears in Sort By Month.
thanks for all the bandwidth, btw.
:
well, as i said before, the on-study date from the form is not used
in
the
query to calculate the future_visit date, or to filter the records
to
return
a single future_visit date. so why do you need to include it in the
query?
in fact, since the WHERE clause of the query stipulates that the
S.FollowUp
field must equal the Q.NextVisit field and the S.LastName field must
equal
the Q.LastName field, the DLookup() function in your code is
essentially
pulling a value that could be identified in Query3 with the WHERE
clause
Query3.[Last Name] = [Forms]![Screening Log]![Last Name]
can you explain the purpose of using Query5 in the DLookup()
function,
instead of using Query3? would you get the correct return value if
you
tried
the following, as
Me!Future_Visit = DLookup("NextVisit", "Query3", _
"[Last Name] = '" & [Forms]![Screening Log]![Last Name] _
& "'" )
(the above DLookup() criteria argument assumes that the [Last Name]
field is
a Text data type.)
also, and btw, you don't need to enable, unlock, and set focus to a
control
in a form, when you're setting the value of the control in VBA. you
also
don't need to explicitly refer to the Value property, since that's
the
default property of the control. all you need is
If Me.Dirty Then
If Not IsNull(Me!IRB_) Then
Me!Future_Visit = DLookup("FollowUp", "Query5")
End If
End If
hth
first off, thanks for the interest!
in the interim, i've dug out some heavy reading material on a2k
development
and been reading about 'recordsets'....
but, i'm not sure i understand all your concerns in the response
you
post.
future_visit is not computed in this particular; it is
computed in
