moving a form's recordset 1

[biot023]

Is there a way in which I can dictate which record in a table is displayed on a form?
Maybe by assigning the form or a property of the form a value?

Any help gratefully appreciated.

douglas If it don't make you laugh, it ain't true.

 

[RickSpr]

To shift a form to a specific record, you use its Bookmark property. However, Bookmark takes a special value that you can't just calculate, you have to get it from a Recordset object that is already positioned to the desired record.

Let's suppose that you have the key of the record you want. I'll assume for demonstration purposes that the key is a Long Integer value from an Autonumber field. In the table, this key column is named RecNbr.

The following code within the form's code module will position it to the desired record:

    Public Sub MoveToRecord(KeyValue As Long)
        Dim rst As DAO.Recordset

        Set rst = Me.RecordsetClone
        rst.FindFirst "RecNbr=" & CStr(KeyValue)
        If Not rst.NoMatch Then _
            Me.Bookmark = rst.Bookmark
        rst.Close
        Set rst = Nothing
    End Sub

The beauty of this procedure is that it can even be called from outside the form, say if you want to position this form to a record selected on another form. All you need is the record key, or even some other value that uniquely identifies the desired record. From the outside, you just call this as a method of your form. For example,

    Forms![MyForm].MoveToRecord lngRecordKey

Another way to use it is to pass the key to the form in the OpenArgs parameter of the OpenForm method. In this case, your form's Open event procedure would call MoveToRecord as follows:

    Private Sub Form_Open(Cancel As Integer)
        If Not IsNull(Me.OpenArgs) Then _
            MoveToRecord CLng(Me.OpenArgs)
    End Sub

A clarification: I said you have to get the Bookmark property value from another Recordset object, but you can't just use any Recordset object, it has to be one you created with the RecordsetClone method of the form. This is because RecordsetClone makes you a copy of the form's own Recordset, which guarantees that the bookmarks will be compatible.

In Access 2000, you don't need RecordsetClone. The form's Recordset object is directly exposed as its Recordset property, and you can execute its methods directly:

    Me.Recordset.FindFirst "RecNbr=" & CStr(lngRecordKey)

However, you would still want to put this in the MoveToRecord procedure, if you want to be able to reposition the form from outside its own code module. If you don't need to do that, you can just put the FindFirst call into the Open event procedure or wherever. Rick Sprague

 

[biot023]

Man, that's fantastic - cheers!
Best response to a question I've read, & it totally solves my problem (I do need to set the recordset from another form).
Thanks again, man.

Douglas If it don't make you laugh, it ain't true.