Recordset Question

[rkl122]

Hello all. Please consider the following code (from which I have removed statements I believe to be irrelevant to my question. The code appears in a combo AfterUpdate event on a main form. The cbo is used to select a record in order to display related records in the subform. The code sets the subform to the record that had been current when leaving the prior session.):

Dim varID As Variant     
    
    Dim strDelim As String
    Dim rst As DAO.Recordset
    Dim db As DAO.Database
    Set db = CurrentDb()

    
    strDelim = """"          
     varID = DLookup("fldValue", "tblInterSessionPlacemark", "[fldVariable] = 'fldShotIDLast'")
  
     [COLOR=Teal]   [COLOR=RoyalBlue]Set rst = Form!ctlsbfShotList.Form.RecordsetClone[/COLOR][/COLOR]
  '    [COLOR=Red] Set rst = db.OpenRecordset("qrysbfShotList")[/COLOR]
        
        With rst
          
        .MoveLast      'To ensure recordset is filled.
                
                .FindFirst "[ShotNumber] & [ShotNumSuffix] = " & strDelim & varID & strDelim
                 If Not .NoMatch Then          
                
                 Me!ctlsbfShotList.Form.Bookmark = .Bookmark [COLOR=DarkOrange] '<===[/COLOR]
            End If
        End With
    rst.Close
    Set rst = Nothing

When run as written, it works as expected. There is no error. However if the line in blue is replaced by the line in red, then the bookmark assignment (indicated by the arrow) throws a 3159 error "not a valid bookmark."

My question is why? The query in the red 'Set rst' statement is the same as the recordsource for the form referenced in the blue 'Set rst' statement. I realize the blue statement uses a recordsetclone, but aren't the two objects functionally equivalent ?

I'm trying to deepen my understanding of the recordset object and ways to use it. Thanks for any insight,

-Ron

 

[CJ_London]

openrecordset requires SQL rather than reference to a query

try using

Set rst = db.OpenRecordset("Select * FROM qrysbfShotList")

 

[rkl122]

Thanks, but that doesn't work. Actually, the developers guide
(forum won't let me post link) says: "The source can be a table name, a query name, or an SQL statement that returns records."

-Ron

openrecordset requires SQL rather than reference to a query

try using

Set rst = db.OpenRecordset("Select * FROM qrysbfShotList")

 

[Galaxiom]

Bookmarks are only valid for the same recordset or its clones. You cannot compare bookmarks from differnt recordsets even if they are coming from the same data and hold identical records in the same order. You will need to use a key.

 

[rkl122]

Bookmarks are only valid for the same recordset or its clones. You cannot compare bookmarks from differnt recordsets even if they are coming from the same data and hold identical records in the same order.

That wasn't intuitively obvious to my noobyness. Thank you!

You will need to use a key.

I guess you mean extract the primary key from the record found by .findfirst and then use it with DoCmd.FindRecord? So far, I'm getting an error with that, even after making sure the subform control has focus. Will keep trying.

For anyone interested, the reason I want to explore the db.openrecordset route is that the other one, while it works going from design to form view, doesn't work when opening the form fresh unless I put a pause - using a third party routine - before working with the recordset. The pause is not necessary if there's a breakpoint in there. It's as if, during normal use, the code gets ahead of the formation of the recordset. Since I don't understand what's causing that, I'd rather use a more robust technique.

-Ron