Why doesn't this code work for all records

[Jeff via AccessMonster.com]

The code below works fine for records at the beginning of my data set but
when I run it from say the last record in my data set it returns to the
first record in the data set. Creating a break point and stepping into the
code works fine for all records. I'm stumped on this one. Any help is much
appreciated.

Dim IDCheck As Integer
IDCheck = Me.[ID]

If (Me.Dirty) Then
Me.Dirty = False
End If

Me.Requery

With Me.RecordsetClone
.Find "[ID]=" & IDCheck
If Not .EOF Then Me.Bookmark = .Bookmark
End With

 

[Vadim Rapp]

Hello Jeff:
You wrote in conference microsoft.public.access.adp.sqlserver on Mon, 09
May 2005 13:21:29 GMT:

JvA> The code below works fine for records at the beginning of my data set
JvA> but when I run it from say the last record in my data set it returns
JvA> to the first record in the data set. Creating a break point and
JvA> stepping into the code works fine for all records. I'm stumped on this
JvA> one. Any help is much appreciated.

JvA> Dim IDCheck As Integer
JvA> IDCheck = Me.[ID]

JvA> If (Me.Dirty) Then
JvA> Me.Dirty = False
JvA> End If

JvA> Me.Requery

JvA> With Me.RecordsetClone
JvA> .Find "[ID]=" & IDCheck
JvA> If Not .EOF Then Me.Bookmark = .Bookmark
JvA> End With

I could not reproduce it - in the trivial form that I created, it always
returned to the same record. From your description though, it seems to be
related to the direction of the Find.

That said, your code is using some things that I would recommend to avoid.

1. dirty indeed can be set, but I would rather consider it as a bug in
Access. Dirty indicates if there were changes to the current record, so by
nature it should be read-only property. In any case, it's not required,
since Requery resets it properly.

2. I've seen numerous cases when touching recordset and even recordsetclone
in code resulted in crashes in Access - not immediately, but soon.

I would rewrite your code like this:

painting=false
b = Bookmark
Requery
on error resume next
Bookmark = b
painting=true



Vadim Rapp

 

[Jeff via AccessMonster.com]

Thanks Vadim,

I got it working using similar code with bookmarks but I'm still confused
on why it only happens on certain records. When I create a break point and
step through the code it works fine.

 

[Vadim Rapp]

Hello Jeff:
You wrote in conference microsoft.public.access.adp.sqlserver on Tue, 10
May 2005 01:46:26 GMT:

JvA> I'm still confused on why it only happens on certain records. When I
JvA> create a break point and step through the code it works fine.

Perhaps it depends on the place where it is - an event? could it be that the
event is re-entered during the execution? I would put some debug.print's.

Vadim

 

[Sylvain Lafontaine]

There has been many reports on this newsgroup with problems related to the
use of the RecordSetClone function with ADP2002 running on WinXP with SP2
and with ADP2003 on Win2003 (can't remember if it was with or without SP1) .

Could you tell us what versions of ADP and Windows are you using?