2105 says: "You can't go the specified record"

[Guest]

when using this vba code in my main form (a2k) and i modify the control's
date value to test it, a screen appears bearing the annoying error message
above. on the other hand, the field to get the calculated value (please refer
to the code below) on the form gets updated, so i guess there are two options
available: figure out what's behind the error and fix it -OR- figure out some
way to somehow suppress the messages since they seem to present only a
nuisance parameter in the scheme of things (unless i'm maybe
mischaracterizing them):


Private Sub On_Study_Date_AfterUpdate()
Dim Future_Visit As Date
Dim On_Study_Date 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
If Me.Dirty Then
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious
End If
End Sub

any thoughts on this anybody?

-ted

 

[Allen Browne]

Instead of moving to the next record and back again, try:
Me.Dirty = False

 

[Guest]

hi allen,

can you please be just a tad more specific. what would you have the code
looking like (not a total newbie, just not the genius you folks are).

 

[Allen Browne]

Replace these 2 lines in your code:
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious
with this one:
Me.Dirty = False

 

[Guest]

thought i'd experiment on this a bit:

so instead of having it like this (below)

we went to

If Me.Dirty Then
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious
Me.Dirty = False
End If
End Sub

which doesn't result in the 2105 error message. On the other hand, the
computed fields are not calculated until i manually scroll to another record
and then back to the affected one.

is this what you had in mind?

-ted

 

[Guest]

i'm sorry allen, i actually wrote that wrong, what i really tried was per
your suggestion:


If Me.Dirty Then
Me.Dirty = False
End If
End Sub

but the results were as i reported.

-ted

 

[Allen Browne]

To force Access to update the calculated controls:
Me.Recalc

 

[Guest]

still no cigar using the code below, allen:

Private Sub On_Study_Date_AfterUpdate()
Dim Future_Visit As Date
Dim On_Study_Date 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
If Me.Dirty Then
' DoCmd.GoToRecord , , acNext
' DoCmd.GoToRecord , , acPrevious
Me.Dirty = False
Me.Recalc
End If
End Sub

when i scroll away and then return to it, the update results (which may be
the result of my OnCurrent Event property's vba below

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

-ted

 

[Allen Browne]

Ted, you NEVER assign a value to a bound control in Form_Current.

If Future_Visit and Months are unbound controls you can update them with:
Call Form_Current()

 

[Guest]

hi allen,

the source behind my form 'Screening Log' _is_ a table and both _bound_
controls (Me.Future_Visit and Me.Months) are getting there values from (to
this newbie) this arcane assemblage of queries and the DLookup function you
see in both the event properties (although it appears at the moment that it's
only working in the "On Current" event). there is a business necessity which
is not evident in any of this and that is that the value of the
'Future_Visit' changes over time as it is contingent on being the first
occurence of a date which is 3,6,9,12,18,24,36,48,72.....240 months following
the value of the current, "Date()", date. so in reviewing the records, the
user would definitely want to know that the FutureVisit were the one that
accorded with the formula.

when you say "you NEVER assign a value to a bound control in Form_Current" i
wonder if you'd still say that given the above constraints. perhaps i'm about
to say something really naive/foolhardy/{your choice}/etc/etc, but it
_works_.

my problem is that when the user finds need to modify OnStudy date the
change in the FutureVisit and Months doesn't happen unless the user scrolls
away and then back to the affected record -- which realy isn't a show
stopper, but it'd be a little 'slicker' to have it take place on the fly.

your thoughts?

-ted

 

[Allen Browne]

Ted, you may be aware that one of the basic rules of data normalization is
that you do not store dependent data. If Future_Visit and Months are
dependent on other things, it makes perfect sense to calculate them when
needed. It makes no sense to store them in a table (other than say a temp
table needed for a complex report, but certainly not for a form.) Then
updating the stored value of the calculated field that should not exist in
the table merely on the chance that someone visits the record in the form
(so triggering the Form_Current for that record) seems crazy and
inconsistent.

That's in addition to the actual reasons I explained that you do not dirty
every record you visit: concurrency issues, spurious records, confusing
error messages, unnecessary writes, increased network traffic, etc.

 

[Guest]

allan, thanks for the post-graduate course. yes, i do seem to recollect there
being something about striving to avoid data dependencies in your table but i
find myself wondering how one would display the dependent data (such as the
future_visit and months parameters) on the same form as the other information
corresponding to the same individual, that is unless you're asking me to
infer that a query joining the biographical data AND the dependent data would
comprise the form's "record source"?

-ted

 

[Allen Browne]

It might be possible to add the other table/query to the RecordSource query
for your form and still have an updatable form.

Or it might be possible to use DLookup() or some other function call in the
Control Source of the text box to show the calculated values on your form.

Hope I didn't sound too strong on that, but IME, it is important to get this
right.