"Not a valid Bookmark" problem

[Essendon]

I have a datasheet, and upon the user double clicking in one of the fields of a record, the code is meant to open a form (based on the same underlying table), and open at the record that the user clicked in.

** START CODE

Dim db As Database
Set db = CurrentDb

Dim rst As Recordset
Set rst = db.OpenRecordset("Customers", dbOpenDynaset)

rst.FindFirst "[ID] = " & Me.ID

If rst.NoMatch = False Then
DoCmd.OpenForm "Form View"
Forms![Commercial Record View].Bookmark = rst.Bookmark
rst.Close
Exit Sub

End If

rst.Close

Exit Sub

*** END CODE

When the above code runs, I keep getting a "Not a valid bookmark" error, except if I click in the very first record in the datasheet. In that case, it opens the form up with no errors returned.

Could someone please shed some light on this for me..

Thanks very much

Peter

 

[charityg]

Try:

Dim stDocName As String
Dim stLinkCriteria As String
Dim db As Database
Set db = CurrentDb

Dim rst As Recordset
Set rst = db.OpenRecordset("Customers", dbOpenDynaset)

rst.FindFirst "[ID] = " & Me.ID
If rst.nomatch=False then
stDocName = "YourFormtoOpen"

stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
rst.close

Edit: after reading your post again, you shouldn't need to check if the ID was found, because if the form is based on the same table as the datasheet then obviously the same record must exist. Instead use:
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "YourFormtoOpen"

stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Essendon]

Thanks for the reply.

Your code is the same as what my code used to be like. The problem is however that it will filter out all the other records and just show 1 0f 1 record in the form view. I want it to open in form view at the specific record amongst all the records in the table.

 

[charityg]

Then try:


Dim rst As Recordset


DoCmd.OpenForm DoCmd.OpenForm "YourFormtoOpen"
set rst=forms![YourFormtoOpen].RecordsetClone
rst.findfirst "[ID]=" & Me!ID
if rst.nomatch=false then
forms![YourFormtoOpen].Bookmark=rst.Bookmark
end if
rst.close


Normally if you want to seach for a record in recordset and then bookmark a form, you need to do the searching in a recordset clone of the form you intend to bookmark.

 

[Essendon]

Charity...sweet charity.
Thanks very much for your help!