By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,728 Members | 2,490 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,728 IT Pros & Developers. It's quick & easy.

Recordset problem due to corruption?

P: n/a
Hi all,

I'm seeing a weird problem I'm thinking might be due to corruption.
What do you think?

Here is the relevant code, which is in a standard module, called from
subfrmDetail's Form_AfterUpdate event:
Dim rs As DAO.Recordset
Dim varBookmark As Variant
Set rs = Forms!frmMain!subfrmDetail.Form.RecordsetClone
rs.FindFirst "JobDetailID=123"
If Not rs.NoMatch Then
varBookmark = rs.Bookmark

The error 3021 - No Current Record occurs because rs.Bookmark is not
set as it normally would. Code like:
Debug.Print rs.Fields(0).Value
fails with the same error.
When FindFirst succeeds (as evidenced by NoMatch being false), then
typically the recordpointer is on the found record. Not in this case:
RecordCount=1, AbsolutePosition=-1 (sic!), BOF=False, EOF=False

Only when I add:
rs.MoveNext
then the recordpointer is where I would expect it, and rs.Bookmark etc
code works just fine.
RecordCount=1, AbsolutePosition=0, BOF=False, EOF=False
This happens in Access2000 and Access 2003 (on same machine).

-Tom.

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Tom van Stiphout <no*************@cox.net> wrote in
news:7k********************************@4ax.com:
Here is the relevant code, which is in a standard module, called
from subfrmDetail's Form_AfterUpdate event:
Dim rs As DAO.Recordset
Dim varBookmark As Variant
Set rs = Forms!frmMain!subfrmDetail.Form.RecordsetClone
rs.FindFirst "JobDetailID=123"
If Not rs.NoMatch Then
varBookmark = rs.Bookmark


ARGH! I don't understand why people write code like this.

First, don't define a recordset variable, use a WITH block.

Second, never store bookmark values in variables. They don't mean
anything outside their immediate context.

Bookmarks are not data. They are ephemeral pointers to locations in
memory structures about which you know abslutely nothing, and
seemingly tiny little actions can invalidate them.

Recode as this:

With Forms!frmMain!subfrmDetail.Form.RecordsetClone
.FindFirst "JobDetailID=123"
If Not .NoMatch Then
[use .Bookmark directly -- do not store it
in a variable]
, , ,

End If
End With

See if this makes a difference.

It may not solve your problem, but it will make for better code.

One last thing: you don't say how you know you're on the wrong
record. You say the absolute position is not what you'd expect, but
you don't really know much about the contents of the recordsetclone.
What is the primary key value of the record that is current after
the .FindFirst? If *that's* not the one you expect, then, something
definitely is wrong, yes.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #2

P: n/a
On Mon, 22 Aug 2005 15:05:41 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

I tried your "With ..." code, and to my big surprise, IT WORKED!!!
I was thinking: what's the difference: an implicit recordset variable
in a With block, or an explicit one in a "Set rs" approach. But at
least in this instance, there IS a difference, and your approach is
the better one. I wish I would understand why.

The bookmark line was not exactly as the real code is. It was a
simplification. Perhaps I should have written:
Forms!MainForm.Form!SomeOtherSubform.Bookmark = rs.Bookmark

I know I'm on the wrong record because:
* Accessing rs.Bookmark causes error 3021
* Accessing rs.Fields(0).Value causes error 3021
* rs.AbsolutePosition = -1
* rs.MoveNext gets me to the expected record (where JobDetailID=123)
This JobDetailID is the PK in the underlying recordset.

Thanks,

-Tom.

Tom van Stiphout <no*************@cox.net> wrote in
news:7k********************************@4ax.com :
Here is the relevant code, which is in a standard module, called
from subfrmDetail's Form_AfterUpdate event:
Dim rs As DAO.Recordset
Dim varBookmark As Variant
Set rs = Forms!frmMain!subfrmDetail.Form.RecordsetClone
rs.FindFirst "JobDetailID=123"
If Not rs.NoMatch Then
varBookmark = rs.Bookmark


ARGH! I don't understand why people write code like this.

First, don't define a recordset variable, use a WITH block.

Second, never store bookmark values in variables. They don't mean
anything outside their immediate context.

Bookmarks are not data. They are ephemeral pointers to locations in
memory structures about which you know abslutely nothing, and
seemingly tiny little actions can invalidate them.

Recode as this:

With Forms!frmMain!subfrmDetail.Form.RecordsetClone
.FindFirst "JobDetailID=123"
If Not .NoMatch Then
[use .Bookmark directly -- do not store it
in a variable]
, , ,

End If
End With

See if this makes a difference.

It may not solve your problem, but it will make for better code.

One last thing: you don't say how you know you're on the wrong
record. You say the absolute position is not what you'd expect, but
you don't really know much about the contents of the recordsetclone.
What is the primary key value of the record that is current after
the .FindFirst? If *that's* not the one you expect, then, something
definitely is wrong, yes.


Nov 13 '05 #3

P: n/a
On Mon, 22 Aug 2005 20:02:47 -0700, Tom van Stiphout
<no*************@cox.net> wrote:

David,
I went back in the archives of CDMA and found your posts advocating
the use of the With block with recordsetclones, rather than the "Set
rs" approach. If I understand correctly, your points are that it's
cleaner (implicit cleanup at end-of-block) and uses less code.
I'll grant you that.
But is it DIFFERENT?
In my example it obviously is, but I was posing the question if this
might have been due to corruption.

Thanks,

-Tom.

On Mon, 22 Aug 2005 15:05:41 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

I tried your "With ..." code, and to my big surprise, IT WORKED!!!
I was thinking: what's the difference: an implicit recordset variable
in a With block, or an explicit one in a "Set rs" approach. But at
least in this instance, there IS a difference, and your approach is
the better one. I wish I would understand why.

The bookmark line was not exactly as the real code is. It was a
simplification. Perhaps I should have written:
Forms!MainForm.Form!SomeOtherSubform.Bookmark = rs.Bookmark

I know I'm on the wrong record because:
* Accessing rs.Bookmark causes error 3021
* Accessing rs.Fields(0).Value causes error 3021
* rs.AbsolutePosition = -1
* rs.MoveNext gets me to the expected record (where JobDetailID=123)
This JobDetailID is the PK in the underlying recordset.

Thanks,

-Tom.

Tom van Stiphout <no*************@cox.net> wrote in
news:7k********************************@4ax.co m:
Here is the relevant code, which is in a standard module, called
from subfrmDetail's Form_AfterUpdate event:
Dim rs As DAO.Recordset
Dim varBookmark As Variant
Set rs = Forms!frmMain!subfrmDetail.Form.RecordsetClone
rs.FindFirst "JobDetailID=123"
If Not rs.NoMatch Then
varBookmark = rs.Bookmark


ARGH! I don't understand why people write code like this.

First, don't define a recordset variable, use a WITH block.

Second, never store bookmark values in variables. They don't mean
anything outside their immediate context.

Bookmarks are not data. They are ephemeral pointers to locations in
memory structures about which you know abslutely nothing, and
seemingly tiny little actions can invalidate them.

Recode as this:

With Forms!frmMain!subfrmDetail.Form.RecordsetClone
.FindFirst "JobDetailID=123"
If Not .NoMatch Then
[use .Bookmark directly -- do not store it
in a variable]
, , ,

End If
End With

See if this makes a difference.

It may not solve your problem, but it will make for better code.

One last thing: you don't say how you know you're on the wrong
record. You say the absolute position is not what you'd expect, but
you don't really know much about the contents of the recordsetclone.
What is the primary key value of the record that is current after
the .FindFirst? If *that's* not the one you expect, then, something
definitely is wrong, yes.


Nov 13 '05 #4

P: n/a
Tom van Stiphout <no*************@cox.net> wrote in
news:pv********************************@4ax.com:
On Mon, 22 Aug 2005 15:05:41 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

I tried your "With ..." code, and to my big surprise, IT WORKED!!!
I was thinking: what's the difference: an implicit recordset
variable in a With block, or an explicit one in a "Set rs"
approach. But at least in this instance, there IS a difference,
and your approach is the better one. I wish I would understand
why.
Well, I have always been suspicious of creating a pointer to an
existing object, because the pointer is not the same as the object
itself, and that's what you're doing with Set rs = Me.RecordsetClone
(or whichever one you're using). When you use the WITH structure,
you're operating not on a pointer, but directly on the object
itself.

WHile it may seem like only a semantic difference, it clearly is a
difference, and your experience suggests to me that the recordset
pointer has data structures associated with it that aren't the same
as the original object.
The bookmark line was not exactly as the real code is. It was a
simplification. Perhaps I should have written:
Forms!MainForm.Form!SomeOtherSubform.Bookmark = rs.Bookmark


I have never seen a justification for storing a bookmark, and
knowing how they work, I'd never do so -- they are clearly too
volatile to be storable.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #5

P: n/a
Tom van Stiphout <no*************@cox.net> wrote in
news:b8********************************@4ax.com:
I went back in the archives of CDMA and found your posts
advocating the use of the With block with recordsetclones, rather
than the "Set rs" approach. If I understand correctly, your points
are that it's cleaner (implicit cleanup at end-of-block) and uses
less code. I'll grant you that.
But is it DIFFERENT?
In my example it obviously is, but I was posing the question if
this might have been due to corruption.


I never knew for a fact that it was different, but, as I said in my
other reply, I was always suspicious of creating a separate
recordset pointer to an object that already existed. It always
seemed to me that it would be creating some kind of copy, rather
than directly operating on the original object.

But I didn't know this for a fact, it was just a gut feeling that it
wasn't the right way to do things.

I feel the same way about form variables, and try not to use them
except where necessary. Again, I'll use a WITH block in preference
to setting a form variable precisely because I'm never quite sure
what I'm manipulating in the case of the form variable.

But it's all voodoo -- I have no real explanations of why I believe
this way, which is why I only ever cite the code structure arguments
when making the case.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.