On Current event property's impact upon report's currency

[Guest]

my a2k report uses as its source data a table with a field we'll call
'FollowUp' which is a date field. that field is created in a form (the user
may or may not have had occasion to use the same day the report gets
generated) via the OnCurrent event property of the data entry form via the
following VBA code

Private Sub Form_Current()
Dim Future_Visit As Date
Dim Months As Integer
Dim Answer As String
If Not IsNull(Me.RegNumber) And Not IsNull(Me.PtInitials) And Not
IsNull(Me.StudyNumber) Then
Me.Future_Visit = DLookup("FollowUp", "Query5")
Me.Months = DLookup("Months", "Query5")
End If
End Sub

along with another field called 'Months'.

now, w/o boring you with all the gory details, 'the thing' as they say is
that Query5 is made from a bunch of queries which have other queries nested
in them and the value of the 'FollowUp' field gets tested against the current
date (>= Date() ).

i tried this out when i noticed that one of the values for the "FollowUp"
field in the report i was testing by printing it was "1/13/06" , a value
which ought to have been converted to "7/13/06" had the user used the data
entry form and scrolled through the record relevant record which corresponded
with that value. so, i opened the data entry form and scrolled through the
record and saw the record get updated via the OnCurrent event coding, reran
the report and 'lo the correct value got printed.

so, i guess my question is: if the user is not interested in having to
manually scroll through all the records of the data entry form to ensure that
each and every record is going to have the proper value of 'FollowUp' but
just wants to run a report, how do we insure that the underlying table has
the most representative values of FollowUp and Months?

any ideas would be really appreciated.

 

[Pat Hartman\(MVP\)]

The follow up date should be calculated via a query. The date stored in the
record should be LastVisit. Every day, you would run a query when the
database opens, and calculate the NextVisit date. The query would select
all the records where the NextVisit date fell into a particular range.

 

[Guest]

hi pat,

would you be interested in a non-terse elucidation of the problem? i can
provide the gory details (if not tonight then tomorrow). i 'think' there are
some dimensions to this that deserve reckoning with you haven't anticipated.
just to make certain...

-ted

 

[Pat Hartman\(MVP\)]

An alternative would be to calculate and store the "next" date when the
"current" visit is recorded.
It is not correct to update this data just because someone viewed a record.
What if they viewed it by accident? What if they didn't view it at all?