Error 3021 when trying to store bookmark

[Guest]

I am hitting an unexpected problem with the following code in a subform. It
works fine for some main form records but the storing of the bookmark causes
Error 3021 (No current record) for other records.


Set rst = Me.RecordsetClone
With rst
If Me.RecordsetClone.RecordCount = 0 Then
GoTo MyLabel_999
End If

If Me.NewRecord = True Then
Me.Bookmark = varBookmark
GoTo Exit_Form_Current
Else
'Following line sometimes triggers Error 3021 - 'No current
record' varBookmark = .Bookmark
End If
End With

 

[Guest]

Oops. Mis-hit some keys and posted while still editing my text!

Here is what I meant to say:

I am hitting an unexpected problem with the following code in a subform. It
works fine for some main form records but the storing of the bookmark causes
Error 3021 (No Current Record) for other records. The puzzling point is that
the failing main form record DOES have subform records. I stepped through the
code up to the failing line and confirmed that the .Recordcount value was 2.
So why the No Current Record error?????


Set rst = Me.RecordsetClone
With rst
If Me.RecordsetClone.RecordCount = 0 Then
GoTo MyLabel_999
End If

If Me.NewRecord = True Then
Me.Bookmark = varBookmark
GoTo Exit_Form_Current
Else
'Following line sometimes triggers Error 3021
varBookmark = .Bookmark
End If
End With

 

[Allen Browne]

David, Access reloads the subform each time the main form moves to another
record. That will invalidate the bookmark, so attempting to store it and
reuse it later will be unsuccessful for subforms.

Additionally, your code tests whether the form is at a new record, and also
that there are records, but it does not test whether the recordsetclone is
at BOF or EOF. You cannot rely on it to be where ever it was last time you
were working with it.

 

[David Anderson via AccessMonster.com]

Allen, thanks for clarifying the root of my problem. I did indeed have some
code that checked .EOF but it didn't seem to correctly flag the end of the
subform's recordsetclone. That's why I changed the code to test Me.NewRecord
instead. Don't Me.NewRecord and .EOF mean the same thing for a recordset that
allows additions?

What I actually want to do is to force the subform back to the last saved
record if the user scrolls (with the mouse wheel) to the new record position
at the end of the recordset. There are reasons why I want to allow additions
to the subform data, but I want to prevent this opportunity happening via
simple scrolling.

Any suggestions on how to achieve this if bookmarks don't work on subforms?

 

[Guest]

Actually, on thinking through this more carefully, Allen's point is not
really relevant to my situation. My problem arises when I am scrolling
through subform records - not main form records. The subform will therefore
not be reloaded during this time and my original query still stands - why do
I get a 'No Current Record' error when simply storing the current bookmark?

 

[Marshall Barton]

I am hitting an unexpected problem with the following code in a subform. It
works fine for some main form records but the storing of the bookmark causes
Error 3021 (No current record) for other records.


Set rst = Me.RecordsetClone
With rst
If Me.RecordsetClone.RecordCount = 0 Then
GoTo MyLabel_999
End If

If Me.NewRecord = True Then
Me.Bookmark = varBookmark
GoTo Exit_Form_Current
Else
'Following line sometimes triggers Error 3021 - 'No current
record' varBookmark = .Bookmark
End If
End With

Allen's remarks may be too subtle and include more than you
need if the main form is not really involved, but the key is
that you do not know where the RecordsetClone's current
record is positioned, especially after some other action in
the subform (or main form) takes place. Note that the
RecordsetClone's current record is independent of its form's
current record.

You are referencing the RecordsetClone's .Bookmark without
ever checking to see if the RecordsetClone's current record
is valid. I do not understand what you hope to accomplish
by saving the bookmark of an unspecified record, so this may
be off base, but maybe you should be saving the record's PK
value and (using FindFirst) locate the desired record using
that.

 

[Allen Browne]

The RecordsetClone has a pointer that is independent of the the form.
Testing the NewRecord property of the form tells you nothing about whether
the RecordsetClone is at BOF or EOF or has a current record.

 

[Guest]

Marshall, I must be getting old. I think you gave me very similar advice ref
another problem with bookmarks a few weeks ago! I seem to keep expecting more
from bookmarks than they are able to deliver. Once again, using a PK-based
argument for .Findfirst (as you recommend) proves to be a reliable method to
ensure I am at the correct record in the clone recordset.

 

[Marshall Barton]

Gee, was that you David? I don't remember back a couple of
weeks. Are you sure it was me? ;-)

Old? I don't want to hear about old. I've already got too
much old ;-)

Glad to hear that you got it working.