Solved Requery underlying form, but retain bookmark (2 Viewers)

[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.

 

[The_Doc_Man]

Thanks for the reminder, @MajP. I had a copy of Access 2.0 by 1995, but I didn't get deeper into VBA and manipulating properties until after Ac2000 came out. I DO know that as time has passed, newer versions of Access have exposed more things in the baseline properties lists, a lot of them formatting-related - but some other properties, too.

 

[Ken Sheridan]

So that was the required way to do it and has become habit.

Exactly, but now this old dog has learnt a new trick.

 

[pdanes]

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.

You have a habit of telling me that I am not seeing what I clearly am seeing. You are wrong - I have single-stepped through code that does a requery and the bookmark DID work afterwards. Sometimes.

Again, I have no idea what is in a bookmark, but since it is only a two-character string, it cannot be a very sophisticated construct, and it is not a big surprise that it would sometimes match.

 

[MajP]

You have a habit of telling me that I am not seeing what I clearly am seeing. You are wrong - I have single-stepped through code that does a requery and the bookmark DID work afterwards. Sometimes.

Again, I have no idea what is in a bookmark, but since it is only a two-character string, it cannot be a very sophisticated construct, and it is not a big surprise that it would sometimes match.

What is your point? Great, you have code that sometimes works. What you going to do with that? That is the worst kind of bug, one that fails sometimes. Really hard to debug those types of errors.
Read post 50 under specific conditions where it could work. But as everyone pointed out this is completely unreliable. Simply save the ID ahead of time and move back after the query.

 

[pdanes]

What is your point? Great, you have code that sometimes works. What you going to do with that? That is the worst kind of bug, one that fails sometimes. Really hard to debug those types of errors.
Read post 50 under specific conditions where it could work. But as everyone pointed out this is completely unreliable. Simply save the ID ahead of time and move back after the query.

I don't know that I would call it a point. More like an observation, that you often write, "You're not seeing that.", when I very clearly AM seeing that. It's not helpful.

And yes, I understand that that it's not reliable, and I have abandoned it, as I also wrote, back in #45.

 

[Ken Sheridan]

And yes, I understand that that it's not reliable, and I have abandoned it, as I also wrote, back in #45.

It's not a matter of reliability. A hedgehog visits my garden from time to time. For the first nine visits it is always raining when it does so. I might therefore conclude that the hedgehog only visits my garden when it's raining. On its tenth visit it's not raining. This shows that my earlier conclusion was wrong, which is not the same as saying it was unreliable. The latter would indicate that there is some kind of causal relationship between the hedgehog visiting my garden and its raining, but this occasionally can fail. In reality there is no causal relationship whatsoever, my initial conclusion was simply wrong.

The above is a case of the exception proving the rule. This saying is often misunderstood. Prove in this case means test, not confirm the validity of the rule. The exception does the exact opposite in fact, it confirms the invalidity of the rule. Or, as I was taught as an undergraduate, 'when theory and practice conflict, suspect the former'.

 

[pdanes]

It's not a matter of reliability. A hedgehog visits my garden from time to time. For the first nine visits it is always raining when it does so. I might therefore conclude that the hedgehog only visits my garden when it's raining. On its tenth visit it's not raining. This shows that my earlier conclusion was wrong, which is not the same as saying it was unreliable. The latter would indicate that there is some kind of causal relationship between the hedgehog visiting my garden and its raining, but this occasionally can fail. In reality there is no causal relationship whatsoever, my initial conclusion was simply wrong.

The above is a case of the exception proving the rule. This saying is often misunderstood. Prove in this case means test, not confirm the validity of the rule. The exception does the exact opposite in fact, it confirms the invalidity of the rule. Or, as I was taught as an undergraduate, 'when theory and practice conflict, suspect the former'.

Seems a bit of a quibble to me, but okay. A bookmark, though, DOES point to something, or at least tries to. It's not a completely unrelated event. Unlike your hedgehog example, it does have something to do with what you are trying to retrieve. It's just that sometimes it gives you the results you want and sometimes it doesn't. That seems to me to be textbook 'unreliable'.

 

[RonPaii]

Seems a bit of a quibble to me, but okay. A bookmark, though, DOES point to something, or at least tries to. It's not a completely unrelated event. Unlike your hedgehog example, it does have something to do with what you are trying to retrieve. It's just that sometimes it gives you the results you want and sometimes it doesn't. That seems to me to be textbook 'unreliable'.

I agree with Ken and it's not a quibble.
The bookmark contains a value, lets call it a pointer. When the record set is recreated, memory for the 1st record set is released and the new record set is placed into memory which may or may not occupy the original memory. Now if the bookmark is a pointer it just may point to a row in the new record set. To think that somehow using the old bookmark is valid because sometimes it works is no different from the hedgehog observations.