VBA: bookmark form to match linked table after search?

[rickpaulos]

Win 7 pro 64 - Access 2010 32bit.

I have an Access .mdb database with linked tables (to a SQL server).
I'm trying to program a button in the MainForm to do a search in one of the tables on the server.
I'm starting simple, just searching on 1 field for now.

I have a Mainform with a subform (RosterSubForm).
The linked table dbo_roster is bound to RosterSubForm

I'm searching the linked table to find the desired record and that works using FindNext.
But the problem I'm having is getting the data in the subform to sync to the newly found record.

I keep getting an error " can't find the field 'RosterSubForm' referred to in your expression"

How do I reference the bookmark property in a sub form?
Do I need to SetFocus to the subform first?

the MS help example shows the bookmark property. (me.bookmark = rst.bookmark) but their example, they only have the mainfom.

MsgBox Me.name returns "Mainform" in my case.

I found a web page on how to refer to forms and subforms and I've tried nearly every combo with no success.


my code:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("dbo_Roster")

rst.FindNext "GraduationClass='1973'"
msgbox rst(0)

If rst.NoMatch Then
MsgBox "1973 Not found"
Else
MsgBox rst(0).Value ' show found record number
Me!RosterSubForm.Form.Bookmark = rst.Bookmark '<<<<<<<<<<<<<< error here
End If

[jonh]

https://msdn.microsoft.com/en-us/library/office/ff823084%28v=office.15%29.aspx
"If you use the Clone method to create a copy of a Recordset object, the Bookmark property settings for the original and the duplicate Recordset objects are identical and can be used interchangeably. However, you can't use bookmarks from different Recordset objects interchangeably, even if they were created by using the same object or the same SQL statement."

Forms already have both a recordset and a clone so you don't need to create either.


With RosterSubForm.Form
.FilterOn = False
.RecordsetClone.MoveFirst
.RecordsetClone.FindNext "GraduationClass='1973'"
If .RecordsetClone.NoMatch Then
MsgBox "1973 Not found"
Else
.Recordset.Bookmark = .RecordsetClone.Bookmark
End If
End With