Solved Requery underlying form, but retain bookmark

[HackSlash]

Glad to help I only signed up to point that out.

Often what we think we are doing is not actually what we told the system to do and it always pays off to take a second to stop and examine our actual tradjectory vs intended direction.

Thanks @Xyloz! I am one of those people who had a convoluted way of refreshing my form after I wanted to remove an item from a step. I am trying your simple solution and testing is going well. I appreciate the explanation that we want to refresh the data and not the form. Makes sense.

 

[Petr Danes]

Glad to help I only signed up to point that out.

Often what we think we are doing is not actually what we told the system to do and it always pays off to take a second to stop and examine our actual tradjectory vs intended direction.

I've had this problem as well - trying to bookmark a spot, requery, then return to that spot. I had the requery on the form level, so based on your advice here, I tried dropping it to the recordset level, and no change. It still barfs with an 'Invalid bookmark' error message when I try to return to that previously saved bookmark.

Dim bkm$
With sfSynonymika.Form.Recordset
    If .RecordCount > 0 Then
        bkm = .Bookmark
        .Requery
        .Bookmark = bkm
    End If
End With

Any other ideas?

 

[MajP]

Bookmarks are recordset specific pointers so if you requery those bookmarks are invalid. So that error is expected and guaranteed. I did not read the entire 3 year old thread but assume this is fully covered.

On easy way is to first save the primary key of the current record, then requery, then use a findfirst and move back to that value

Dim PK as long
PK = me.somePKField
with sfSynonymika.Form.Recordset
   .requery
   .findfirst "SomePkField = " & PK
End With

However if you simply requery the

 

[MajP]

Now I reread the thread and of course this was covered, and in fact it was me.
I would not resurrect this thread because this is one of the stupidest threads I have ever read. The OP seemed to be trolling.

 

[Petr Danes]

Now I reread the thread and of course this was covered, and in fact it was me.
I would not resurrect this thread because this is one of the stupidest threads I have ever read. The OP seemed to be trolling.

I wondered. It seemed like a reasonable thought at first - requery just a recordset instead of an entire form, but it seeems to be as you say - requery blows away bookmarks. Not always though, which is how this code often makes it into production. Sometimes a bookmark DOES still work after a requery - apparently the new bookmarks sometimes happen to match an old one. I have deployed code that contains bookmark references, and sometimes it was weeks down the road before someone started complaining. I've gotten much more careful about where I use them. Part of the problem is that I have no idea what info a bookmark actually contains, although it is clearly not as sophisticated as a file hash. But using a unique identifier and a .Find is clearly the way to go, if you have a Requery in the process.

 

[MajP]

Not always though, which is how this code often makes it into production. Sometimes a bookmark DOES still work after a requery - apparently the new bookmarks sometimes happen to match an old one.

That is not a true statement, bookmarks can never match again. A Bookmark is an internal pointer that uniquely identifies a specific row within that exact INSTANCE of a recordset. Kind of like a memory address or a row handle. It is not a value you can interpret or reuse across different recordsets. It cannot be reused because it points to a place that no longer exists after a requery. If you think it worked and then it did not, you just did not expose that code.

 

[Ken Sheridan]

The following is the code, similar to that posted by MajP earlier, which I use in a form in which the rows are returned in transaction date order, which requeries the form and moves the record pointer to the updated/inserted record, which is now in its correct ordinal position:

Private Sub Form_AfterUpdate()

    Dim lngID As Long
    
    lngID = Me.TransactionID
    Me.Requery
        
    With Me.RecordsetClone
        .FindFirst "TransactionID = " & lngID
        If Not .NoMatch Then
            Me.Bookmark = .Bookmark
        End If
    End With
    
End Sub

 

[Gasman]

I *thought* that if you requeried the form's recordset, and NOT the form, then the record pointer remained where it was?

 

[MajP]

The following is the code, similar to that posted by MajP earlier, which I use in a form in which the rows are returned in transaction date order, which requeries the form and moves the record pointer to the updated/inserted record, which is now in its correct ordinal position:

Private Sub Form_AfterUpdate()

    Dim lngID As Long
   
    lngID = Me.TransactionID
    Me.Requery
       
    With Me.RecordsetClone
        .FindFirst "TransactionID = " & lngID
        If Not .NoMatch Then
            Me.Bookmark = .Bookmark
        End If
    End With
   
End Sub

I see people use that a lot. Since that seems more complicated and since you posted it I assume there is some kind of advantage. I have no idea what that is. What is the advantage of that over this?

Private Sub Form_AfterUpdate()
    Dim lngID As Long
    lngID = Me.TransactionID
    Me.Requery
    me.recordset.FindFirst "TransactionID = " & lngID
End Sub

 

[MajP]

I *thought* that if you requeried the form's recordset, and NOT the form, then the record pointer remained where it was?

My understanding is that it may work but it is not guaranteed. I have no idea why, so I asked Chat. Now I am not sure how much of this is truly accurate but here is the answer so take it for what it is worth.


Short answer: it can work, but it’s not inherently “safe” unless you handle a few important caveats.
In Microsoft Access, requerying a form’s recordset instead of the form does reduce UI disruption (no repaint/flicker, no re‑execution of form events), but it does not automatically guarantee that you’ll stay on the same record.

---

What actually happens when you requery​

Me.Requery​

• Re-runs the form’s record source
• Repositions to the first record
• Fires form events (Current, Load, Open depending on context)
• Causes visible refresh

Me.Recordset.Requery​

• Requeries the underlying recordset only
• Does not repaint the form
• Fires fewer events
• BUTAccess may still:
  • Drop the current record
  • Reorder records
  • Invalidate bookmarks

So while Recordset.Requery is less disruptive, it does not preserve position by itself.

---

Why staying on the current record is not guaranteed​

Requerying (either way) means:

• The recordset is discarded and rebuilt
• Bookmarks may become invalid
• Sorting and filtering may change
• Records could disappear (due to WHERE clause changes)

If anything about the recordset changes, Access may not be able to return to the same row.

When​

You can often get away with it if:

• The recordset order does not change
• No records are added/removed
• The WHERE clause remains identical
• You’re refreshing calculated fields or lookup values only

But that’s implicit behavior, not guaranteed behavior.

 

[Ken Sheridan]

What is the advantage of that over this?

None that I can think of. It's just the way I was taught to do it back in the last century.

 

[MajP]

None that I can think of. It's just the way I was taught to do it back in the last century.

Actually that keyed me to the reason. The reason you still see it is because at one time it was the only method. Prior to AC 2000 there was only a recordsetclone and no recordset property. It was synched via the forms bookmark. So that was the required way to do it and has become habit.