Recordset, bookmark and FindFirst (1 Viewer)

[logik]

Hello,
I am using Findfirst to move the current focus to a record identified by an ID. The ID is my primary key, type autonumber.

I have my record set, I know the ID but I am confused by the syntax of Findfirst and the subsequent use of bookmarks. I am calling this from the OnActivate event

-
Dim rst As Object
Set rst = Me.RecordsetClone
rst.FindFirst "[ID] = " & [TempVars]![TempID]

If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If

I have no compilations errors, the NoMatch test passes but I always end up on the first entry of my record set; not with the ID I supplied

 

[arnelgp]

check first if your recordset is Bookmarkable.

Dim varBookMark As Variant
set rst = Me.RecordsetClone
If rst.Bookmarkable then
rst.FindFirst "[ID] = " & [TempVars]![TempID]

If Not rst.NoMatch Then
varBookmark = rst.Bookmark
Me.BookMark = varBookmark
End If
End If

 

[logik]

Thanks. The recordset is bookmarkable. I have applied the test above to check and used the varBookmark as you suggest. This starts empty but is not updated even though the line is executed. Bookmark seems to be a structure rather than a number.

 

[arnelgp]

Activate event doesnt fire on subform.

 

[logik]

My OnActivate is definitely called as I have a breakpoint in it. I have a dataset form, a button opens a single record form to edit or add a new one. This form is closed and, as I have to do a requery to ensure the new record is visible in the dataset form, I lose my original position. I am using findfirst and bookmark to try and get back to where I was.
The alternative is to know whether the record is new or just edited but have found no way to do this.

 

[Minty]

Why not just pass the new id back to the original form from the sub form?
(Have I missed the point here)

 

[logik]

I'm sure you haven't missed the point ☺

Yes I have the ID but what to do with it? Are you thinking Gotorecord in onactivate?

 

[Minty]

That was sort of where I was heading. Just do a find record on the original form, if you open the data entry form in modal fashion you can do the find in the calling code, as it will wait for the data entry form to be closed.

 

[MarkK]

I wouldn't do this OnActivate with a global TempVar. Do it directly so consumers who want to find a record can do so explicitly, and at any time they want during the lifetime of the form. Consider . . .

Public Sub GoToID(ID as long)
   With Me.RecordsetClone
      .FindFirst "ID = " & ID
      If Not .NoMatch Then Me.Bookmark = .Bookmark
   End With
End If

. . . so rather than set a TempVar and open the form, Open the form and call it's public GoToID method, passing in the ID you want to find. I have a routine like this on almost every bound form.

 

[arnelgp]

when you do the requery all bookmarks are destroyed and re-created a new one.

 

[logik]

I have this working now. One of the problems was using Gotorecord using the automatically generated ID. This is not on my form and gave an error. Took a while to figure that out. TempVars also helped with the logic.