Invalid bookmark error

[Lorref]

I have a main Form with two subforms and these two subforms are setup in a master/detail scenario. First subform (lets call this SubFormA) contain Order records and the second subform (SubFormB) contain the Order Detail records. If I click on a Order record in SubFormA, then the Order Detail records will be shown in SubFormB. I am using the Order/Detail paradigm for simplicity sake.

The problem I have been tearing my hair off, is that I want SubFormA to be requeried (not refreshed, due some calculated fields in the form datasource) , if I update a record in SubFormB. The requery part works flawless, but I want to reselect the Order record the user had selected, because a requery will jump to 1st record by default.

I am trying to achieve this with Bookmarks and I am receiving "Not a valid bookmark" error. I am using the "After Update" event in SubFormB to call the procedure RefreshVenueEstimate (see code) to refresh SubFormA.

Sub RefreshVenueEstimate()
 
    Dim rst As DAO.Recordset
    Dim VenueMaterialEstimateID As Long
 
    VenueMaterialEstimateID = Me.Parent.Child_Venue_Material_Estimates!id
 
    Me.Parent.Child_Venue_Material_Estimates.Form.Requery
 
    Set rst = Me.Parent.Child_Venue_Material_Estimates.Form.RecordsetClone
    rst.FindFirst "id = " & VenueMaterialEstimateID
    If Not rst.NoMatch Then
        Me.Parent.Child_Venue_Material_Estimates.Form.Bookmark = rst.Bookmark
    End If
    rst.Close
    Set rst = Nothing
 
End Sub
 

Any help is very much appreciated.

[NeoPa]

Nowhere in your code does it save the .Bookmark of the record you wish to return to. A Bookmark is a string value (containing binary data) that identifies a record uniquely. Setting rst.Bookmark to the saved value returns the current record pointer to the record it was at when it was saved.

For more see Context-Sensitive Help using Bookmark.

[Lorref]

Thanks, but I am not following your response. I have scoured the web for this common problem and it seems to me that recordset cloning is the best way to solve this. Just some references that I read:
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
http://www.everythingaccess.com/tuto...form-is-opened

Am I misinterpreting these samples? Any input is very welcome!

[Lorref]

NeoPa, I worked out your suggestion, using the following code in my form.

 
    Dim varBookmark As Variant
 
    If Me.Recordset.Bookmarkable Then
        varBookmark = Me.Bookmark
        Me.Bookmark = varBookmark
    End If
 

I don't do a record delete, or change the primary key value, just update a record. But when this codes executes (After Update event) it still is throwing the invalid bookmark error, when I set the bookmark! WTF? I am saving the bookmark, do nothing, set the bookmark and to get an error. I am lost!

[NeoPa]

You don't say whether the current record actually has a value at the point the code is run. Is the event you are using control-level or form-level? What happens if you try this same code on an existing record?

[Lorref]

There are existing records that I read from a table, so yes, I am changing existing records. And I run this at the "After Update" event on the Form level.

Some update on this. The form Record Source has a query with a sort clause in the query. I removed the sort clause from the query and my Bookmark error doesn't show any more. However, setting the bookmark does nothing, after requery it still stays at the first record.

NeoPa, do you know an alternative to this weird problem

[NeoPa]

I've seen nothing that indicates to me that the problem is weird. I've seen nothing from you yet that I would expect to work.

The bookmark should be saved before you leave the record (so probably not in the form's AfterUpdate event procedure), and then set back when you want it. So far, when you're doing things the right way, I've seen nothing go wrong. The only problem I'm aware of seems to be in your understanding of the issues. That's not a problem in itself, but it doesn't help to interpret that as an Access problem ;-)