recordset.bookmark problem

[Seth Schrock]

I have the following subroutine that I'm using to bookmark the current record, do a requery of the form, and then go back to the record that I bookmarked. Here is my code:

Private Sub FormRefresh()
Dim varBookmark As Variant
 
With Me.sfrmCrimes.Form
    varBookmark = .Recordset.Bookmark
    .Requery
    If IsEmpty(varBookmark) Then
        MsgBox "Bookmark is empty"
    Else
        .Recordset.Bookmark = varBookmark
    End If
End With
 
End Sub

When it runs, it stops on line #10 and says
Run-time error '3129':
Not a valid bookmark.

If I select "Debug", line #10 is highlighted. If I then drag it up to line #7 and then step through the code using F8, then the code runs fine. This defies all troubleshooting methods that I have ever heard of, so I have no idea how to test this. I initially put in the If/Then statement to test varBookmark as I wanted to see if it wasn't getting passed the bookmark for some reason. However, I never get the "Bookmark is empty" message. What am I doing wrong?

This is the first time that I have ever used the Recordset.Bookmark property. Here is the link that I was trying to follow when I created this: MSDN Recordset.Bookmark Property

[TheSmileyCoder]

If you read the link you posted:

MSDN However, you can't use bookmarks from different Recordset objects interchangeably, even if they were created by using the same object or the same SQL statement.

Consider the bookmark as you would a "real" life bookmark, and the recordset as you would a real life book. Now when you open a recordset its like buying a book. You can place a bookmark in the book (recordset) and return to the bookmark later. But opening a new recordset is like getting a new book. Opening a new recordset with the same sql (Such as when you requery) is like getting a new copy of the same book. Problem is you can't use the bookmark, cause its stuck in the old/first book.

So in short, you cannot use the bookmark in that way. If the records have a unique ID (And they should!!!!) then you can use that instead. Lets assume your primary key for the record is simply called PK_Record and is numeric.

Private Sub FormRefresh()
dim lngRecordID as long
 
With Me.sfrmCrimes.Form
    lngRecordID = .lngRecordID
    .Requery 
    .RecordSet.FindFirst "lngRecordID=" & lngRecordID 
 
End With
 
End Sub

That is the simplest version of the code, to get more advanced you could add in checks for whether the record is found (what if another user deleted it in the meantime?), or what if you start by standing on a empty(new) record?, however I can't advice on this since the details are missing.

[Seth Schrock]

I didn't catch that a requery would fall into the category of being created by using the same object or the same SQL statement. Thank-you for pointing this out.

And your suggestion worked perfectly. The only check I added was to make sure that sfrmCrimes was not on a new record.

[TheSmileyCoder]

Happy to be of assistance. I remember it as not too long ago I had the same/similar learning experience with using bookmarks. :)

Best of Luck with your project
TheSmileyCoder

[ADezii]

Your original concept will actually work:

Dim varBookmark As Variant
 
With Me.sfrmCrimes.Form
  varBookmark = .Recordset.Bookmark
 
  'Requery the Sub-Form Control, NOT the underlying Form itself
  Me.sfrmCrimes.Requery
 
  If IsEmpty(varBookmark) Then
    MsgBox "Bookmark is empty"
  Else
    .Recordset.Bookmark = varBookmark
  End If
End With
 

[Seth Schrock]

Perfect ADezii! I just tried it and I like it much better. I always saw the record jump when using the .Recordset.FindFirst method and using the bookmark had no jump at all. Thanks so much.

[ADezii]

@Seth Schrock
Yopu are quite welcome.

[NeoPa]

ADezii:
'Requery the Sub-Form Control, NOT the underlying Form itself

Nice. This question has given me an idea as to how I can improve some code I expect to be working on again shortly.

I'm pretty sure this also means (and I'm highlighting this because it seems important to understand rather than as a criticism of Smiley's attempt to be helpful) that a .Requery() does not cause the Recordset object to be considered to be a separate Recordset from before the .Requery().

[Seth Schrock]

What then would be the reason for my initial code to not work? I assumed that this proved that Smiley was correct and that a requery of the control didn't requery the form's recordsource (allowing the bookmark to work) whereas a requery of the form inside the subform control did cause the form's recordsource to be redone (causing the bookmark to fail). As this is the only difference in the two pieces of code, I'm at a loss for what could be the cause of the original to fail if that is not it.

[NeoPa]

I'm not sure Seth. I may be wrong - hence the wording of my post. I too, want that to be clarified. I must admit though, that the idea of a .Requery() call meaning that a recordset is no longer the same recordset seems logically quite wrong to me. The recordset is not being recreated. merely requeried/refreshed. Refresh is the term I want to use, but that has a specific meaning with regards to recordsets in Access.

If Smiley is correct, then it's important that point is understood. Whatever the answer, it should really be understood rather than guessed at is my view.

[zmbd]

:)
Here's something I've done - and very often
.
(Seth, this is an example of one of those things that Neopa pointed out that could be handled directly by the form within the Access structure; however, I have my reasons ;-) )

The form this code is in is setup much like the one in given in the example database Many-to-Many-to-Many relationship problem wherein the form is bound to a record set, direct record manipulation is prohibited, and I have a set of unbound controls in the footer for new record creation.

The reason behind this setup is that I have to have a confirmation that the information entered is correct as once the record is created, it cannot be changed as a lot number is created before being passed to this part of the code. The lot numbers can only be assigned once and have to have a strict tracking.... no mistakes, no omissions, or the paperwork nightmare never ends!

So, to the point which happens at line 12 and then 16 thru 19:

'<<<Leading Code Omitted>>>
'Create new entry into the tbl_inventory and refresh the form
    '
    'Setup for the record set
    Set zrs = zdb.OpenRecordset("tbl_inventory", dbOpenDynaset)
    '
    'Pull the information from the form and the new id
    With zrs
        .AddNew
'<Sorry, removed this section of the code, just updates the fields.>
        .Update
        .Bookmark = .LastModified
    End With
    '
    'requery the form and move to the last modified record
    With Me
        .Requery
        .Bookmark = zrs.Bookmark
    End With
    '
    'Close the current recordset,
    zrs.Close
    Set zrs = Nothing'
'<<<Ending Code Omitted>>>

SO, I take a look at the bookmark from the VBA's copy of the book (line 11) and tell the form to turn to that page in its copy of the book (lines 15 thru 18).

The point being that if we think of these as three ring binders, then when several people are working, there are several three ring binders open and pages being added to these binders - all of these binders are of course just working copies of the underlying table (over simplified of course). As the user needs to be able to find the record just added to the underlying table once the form is refreshed, and 8 other people might have just added a record too, I thought it nice to just bookmark the VBA copy and then transfer that bookmark over to the Form's copy after being updated... something like calling a friend and telling them that you're on page 8 of the same book you're both reading.

[Stewart Ross]

I found an interesting problem with the use of a bookmark property in the way suggested by the earlier posts in this thread. Although this was not in the context of a subform, the general principle was the same: the form's current bookmark property was stored (as a result of an After Update event in my case), a requery is done, and the bookmark is restored to locate the same record as was previously in focus.

The problem occurred in the context of a department form in which the name of the department is editable, and is ordered in alphabetical order in the underlying query on which the form was based. If the edit undertaken changes the relative order of the record that was edited then restoring the former bookmark after the form requery restores focus to the wrong record. Reason for this is that the bookmark is referring to an absolute position in the recordset, but if the relative position of the wanted record has changed this invalidates the absolute position previously stored in the bookmark.

I resolved this by going back to the approach shown in the MS examples and also advocated by Smiley - after the requery setting a recordset to the form's recordsetclone, using FindFirst to find the record concerned, then setting the bookmark property of the form to the bookmark of record found in the cloned recordset.

In short, if an edit has taken place which changes the relative order of the recordset concerned then restoring the pre-edit bookmark after a requery may result in the bookmark locating a different record than was originally set. The recordsetclone/find the record and rebookmark approach does not suffer from this problem.

I have not tested what may happen in a multi-user environment but by inference if a user adds a new record which could change the existing sort order of the recordset used by the form then the same problem with restoring a pre-edit bookmark is likely to result.

-Stewart

[NeoPa]

Stewart:
The recordsetclone/find the record and rebookmark approach does not suffer from this problem.

I don't doubt this point Stewart, but I would point out that the beauty of the whole Bookmark concept is that it obviates the need for doing another find through an index. Getting around the problem, in such a way that loses this benefit, seems disappointing at best.

That said, if all you say is true (and I'm not so stupid that I would need to be convinced of that again.), it may still be a necessary evil :-(