Solved Requery underlying form, but retain bookmark

[Ken Sheridan]

That seems to me to be textbook 'unreliable'.

No, it's merely fortuitous, in the same way that whether it's raining or not when the hedgehog visits my garden is fortuitous.

 

[The_Doc_Man]

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.

If you have no idea what is in a bookmark, here is a partial answer.

Recordset.Bookmark property (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

In the "Remarks" section, the last sentence is incredibly important.

The value of the Bookmark property isn't the same as a record number.

When you change the recordset, even by requerying it with the same WHERE conditions, remember that at least in some cases, you are sharing that DB and therefore, potentially sharing the recordset. If someone else updates the recordset and the (Options setting) auto-refresh time passes OR you run a refresh of the recordset yourself, that recordset might not have the same layout as it previously had, and that means the bookmarks from the previous "OpenRecordset" operation are no longer valid. The fact that you even FIND a record using an obsolete bookmark is purely lucky. If you do that, I would advise buying a lottery ticket.

 

[pdanes]

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.

It's completely different. The hedgehog has NOTHING to do with the weather - it's just a random simultaneous occurrence. The bookmark is being used as intended - to locate a record. Just that due to a lack of understanding of all the pertinent circumstances, it is being used in an improper manner.

Obviously such use is not valid, and I never claimed it is. It clearly is not. It happens to work sometimes, and that, through testing, leads people (including me) to draw the erroneous conclusion that I was using it properly. I now know better.

 

[pdanes]

No, it's merely fortuitous, in the same way that whether it's raining or not when the hedgehog visits my garden is fortuitous.

Fortuitous means lucky, fortunate. This is NOT fortuitous - the fact that it happens to work sometimes leads people to coding errors, which are sometimes not discovered for quite a while, because it happens to give correct results when they're testing.

I don't know why you think having a hedgehog in your garden is fortuitous, unless you happen to like hedgehogs. In any case, you did nothing to try making it appear - it just happened.

The bookmark is a deliberate attempt to make something happen. Sometimes it works, sometimes not. That is not fortuitous, that is an unreliable response to an intentional attempt at manipulation. The fact that it sometimes works is not at all fortuitous. Quite the opposite, since it leads people to create mechanisms that later turn out to not function properly, and causes extra work subsequently locating the fault. This thread is proof of that - I'm obviously not the only one who has fallen victim to this. It would be far better if a Requery command did something that RELIABLY made all previous bookmarks instantly invalid.

 

[pdanes]

If you have no idea what is in a bookmark, here is a partial answer.

Recordset.Bookmark property (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

In the "Remarks" section, the last sentence is incredibly important.



When you change the recordset, even by requerying it with the same WHERE conditions, remember that at least in some cases, you are sharing that DB and therefore, potentially sharing the recordset. If someone else updates the recordset and the (Options setting) auto-refresh time passes OR you run a refresh of the recordset yourself, that recordset might not have the same layout as it previously had, and that means the bookmarks from the previous "OpenRecordset" operation are no longer valid. The fact that you even FIND a record using an obsolete bookmark is purely lucky. If you do that, I would advise buying a lottery ticket.

Thank you - you so often provide useful information on a topic while others (including me, sadly - sigh) argue over pointless trivialities.

I think I'll pass on the lottery ticket, though. I'm satisfied that I now know when and how to use bookmarks properly.

 

[pdanes]

If you have no idea what is in a bookmark, here is a partial answer.

Recordset.Bookmark property (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

In the "Remarks" section, the last sentence is incredibly important.



When you change the recordset, even by requerying it with the same WHERE conditions, remember that at least in some cases, you are sharing that DB and therefore, potentially sharing the recordset. If someone else updates the recordset and the (Options setting) auto-refresh time passes OR you run a refresh of the recordset yourself, that recordset might not have the same layout as it previously had, and that means the bookmarks from the previous "OpenRecordset" operation are no longer valid. The fact that you even FIND a record using an obsolete bookmark is purely lucky. If you do that, I would advise buying a lottery ticket.

This only explains how and when to use a bookmark, though. I still have no idea what info a bookmark actually contains. A two-byte string cannot contain much, although I don't know what happens if the recordset contains a very large number of records. I've only used bookmarks on small recordset. Maybe it gets longer when the recordcount gets larger, which would make sense why it's a string variable. I suppose I could code up some experiments with a binary dump of what's in the string at various times. If I do, and find out something useful, I'll post back here.

 

[xavier.batlle]

If you want to experiment with Bookmarks, perhaps you find useful this function which converts a bookmark into a printable hexadecimal value:
https://www.access-programmers.co.uk/forums/threads/printing-recordset-bookmark-values.331452/

 

[pdanes]

If you want to experiment with Bookmarks, perhaps you find useful this function which converts a bookmark into a printable hexadecimal value:
https://www.access-programmers.co.uk/forums/threads/printing-recordset-bookmark-values.331452/

Thank you.

 

[RonPaii]

A two-byte string cannot contain much, although I don't know what happens if the recordset contains a very large number of

This only explains how and when to use a bookmark, though. I still have no idea what info a bookmark actually contains. A two-byte string cannot contain much, although I don't know what happens if the recordset contains a very large number of records. I've only used bookmarks on small recordset. Maybe it gets longer when the recordcount gets larger, which would make sense why it's a string variable. I suppose I could code up some experiments with a binary dump of what's in the string at various times. If I do, and find out something useful, I'll post back here.

Two Bytes can hold a pointer to a 2^16 = 65,536 address space.
Two AscII2 characters can hold a pointer to 2^32 = 4,294,967,296 address space.

 

[pdanes]

Two Bytes can hold a pointer to a 2^16 = 65,536 address space.
Two AscII2 characters can hold a pointer to 2^32 = 4,294,967,296 address space.

Yes, I know. I've been in the field of computers for over 50 years - I know what bytes can hold. But it can't hold 64K or 4G of 'address space', whatever you mean by that. It can only hold a NUMBER up to that big. It may be a number that means something specific in a recordset object, it may be an offset into a buffer, or it may be something entirely different, something that the Access design team dreamed up and makes no seeming sense to anyone else.

As I wrote, I have no idea what info a bookmark actually contains, and have found nothing that explains it. I only observe that it cannot contain very much info, since it is small.

 

[RonPaii]

Yes, I know. I've been in the field of computers for over 50 years - I know what bytes can hold. But it can't hold 64K or 4G of 'address space', whatever you mean by that. It can only hold a NUMBER up to that big. It may be a number that means something specific in a recordset object, it may be an offset into a buffer, or it may be something entirely different, something that the Access design team dreamed up and makes no seeming sense to anyone else.

As I wrote, I have no idea what info a bookmark actually contains, and have found nothing that explains it. I only observe that it cannot contain very much info, since it is small.

I does not have to hold the data only a pointer to that data. According to Copilot it its stored in a byte array, and like a memory pointer to a memory location, it stores the location of a record in the record set. Unlike a memory pointer, it is not globally meaningful and does not directly reference physical storage.

 

[Ken Sheridan]

Fortuitous means lucky, fortunate.

'As the OED entry shown below indicates, that's only a modern tendency, which is not supported by its etymology. I'm using it in it's original meaning, derived from the Latin fors (chance):

fortuitous /0fɔ:ˈtju:ɪtəs/ adjective. M17.
[ORIGIN Latin fortuitus, from forte by chance, from fors chance, luck + -ous.]

That is due to or produced by chance; accidental, casual. Now also, happening by a lucky chance, fortunate.

A. Brink His presence is not fortuitous. He has a role to play. F. Popcorn Was it a master plan or just fortuitous timing?

– NOTE: The traditional, etymological meaning is ‘happening by chance’. In modern English, however, fortuitous tends to be used
to refer to fortunate outcomes, and has become more or less a synonym for ‘lucky’ or ‘fortunate’.

fortuitously adverb M17. fortuitousness noun the quality of being fortuitous, accident, chance M17.

As the replies from others here have made clear, a bookmark's pointing to the same row as it did before a requery is fortuitous, so your original contention that 'Sometimes a bookmark DOES still work after a requery' is incorrect. The important word here is 'work'.

I'm surprised that it apparently was your original practice to use bookmarks incorrectly. I recall that my very first question in the old Compuserve Access forum back in 1997 arose from my first and only attempt to use a bookmark incorrectly before and after a requery. I was immediately corrected by John Vinson, who was then the Chief Sysop in the forum, when I found it didn't work. The delay before your damascene moment was extremely fortuitous.

 

[pdanes]

'As the OED entry shown below indicates, that's only a modern tendency, which is not supported by its etymology. I'm using it in it's original meaning, derived from the Latin fors (chance):

I use words as they are used in common usage today. If it was used some other way in the past, that may be interesting, but it's not really pertinent.

As the replies from others here have made clear, a bookmark's pointing to the same row as it did before a requery is fortuitous, so your original contention that 'Sometimes a bookmark DOES still work after a requery' is incorrect. The important word here is 'work'.

It works, because it gives the results that I expected it to give - retrieving a record to which I saved a reference. If it does what I want, it works. If that's merely an accident of faulty design, fine, bad choice of implementation on my part, but it still did what I wanted, so it worked.

I'm surprised that it apparently was your original practice to use bookmarks incorrectly. I recall that my very first question in the old Compuserve Access forum back in 1997 arose from my first and only attempt to use a bookmark incorrectly before and after a requery. I was immediately corrected by John Vinson, who was then the Chief Sysop in the forum, when I found it didn't work. The delay before your damascene moment was extremely fortuitous.

Well, I learned what a bookmark was, I tried it and it did what I wanted, so I didn't investigate further, because I had solved my problem. I didn't discuss it with anyone. And it was not fortuitous - it was bad luck that it worked, because it meant more work for me further down the road. If it had crapped out immediately, as bookmarks SHOULD ALWAYS, if an operation has rendered them invalid, I would not have wasted time developing and deploying such code.

 

[Ken Sheridan]

I use words as they are used in common usage today.

You don't seem to have noticed the word also in the OED definition. But, enough time has been wasted on this. I'm no longer monitoring this thread.