ODD Microsoft access form issue

[pdono]

I am having a very odd error in a shared database today.

There is a basic search button on aform which siimply uses the Access field search box to find an account number.

When the item is not found, I am reciving a invalid bookmark error instead of the usually unable
to find the selected item default message.

I copy the controls in a new form and no longer received the error. However, I am curious on what would cause a bookmark error when trying to search a recordset.
Thanks!

 

[RickSpr]

An invalid bookmark error should occur only when you attempt to set a Recordset's or Form's Bookmark property using a value that came from a different recordset. The actual value of a Bookmark property is specifically undefined, so it's impossible to say whether a given bookmark can be used to refer to a record in a recordset other than the one it was obtained from. The only guarantee we're given is that it will be valid in its original recordset, the one it was obtained from.

It's important to distinguish the term 'recordset' as I'm using it here (lower case) from a Recordset object. A 'recordset' is an actual set of records that has been realized by opening a Recordset object. But the two are not the same; you can call the Recordset object's Close method, at which time the 'recordset' (the set of records) will no longer exist, but the Recordset object still exists. Then, you can execute the object's Requery method to open a different 'recordset'--still the same Recordset object, but a different 'recordset' and (possibly) a different set of records.

The actual bookmark values are determined when the 'recordset' is created, which happens when the OpenRecordset or Requery method is called. (Remember, too, that calling the form's Requery method will cause the form to call its Recordset object's Requery method.) If you close a Recordset object and immediately open it with the same SQL statement or query name, you are creating a new 'recordset'. Microsoft doesn't guarantee that the bookmarks will come out the same when you do this.

In order to be sure that the search will produce valid bookmarks, it must be performed on the open 'recordset' that the form is currently using. To do this, you use the form's RecordsetClone property. This property returns a new Recordset object that refers to the same 'recordset' as the form is using. The main difference is that the new object has its own independent 'current record' pointer. This lets you look through the records in the (already opened) 'recordset' for the data you want, without changing the record being displayed on the form.

I've been assuming that you aren't using RecordsetClone in your search routine. If, in fact, you are using it, it's hard to say why the bookmark would be invalid. One thought that comes to mind is that the form's 'recordset' has gotten out of sync with the search routine's. This might happen if you only used RecordsetClone initially, say when you opened the form, but you later applied or removed a filter or changed the sort order of the form. These actions call the form's Requery method, which creates a new 'recordset', but the Recordset object obtained earlier from RecordsetClone might still be referring to the original 'recordset'. To fix this, wait until you do the actual search to use RecordsetClone. Then you'll always get the current 'recordset' to use for searching.

On the other hand, if you are using a separate OpenRecordset call in your search routine, as I had been assuming, you're sitting on a time bomb. It will eventually fail. You need to switch to using RecordsetClone. Rick Sprague