Return to original record after requery 1

[billheath]

I am trying to return to the original record after a requery. I have tried everything I know and am running out of ideas.I'm sure you guys will have no problem. My latest attempt is as follows:

varBookmark = me.stopCount
me.requery
Set rs = Me.RecordsetClone
rs.FindFirst "StopCount = " & varBookmark
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If


Me.Stopcount is the only totally unique field. rs.findfirst never finds a match and skips to the end if. Any suggestions would be appreciated. I have searched through the existing threads and tried several suggestions but I just can't get them to work.

Thanks

 

[CautionMP]

billheath,
This is the ADO version but I believe it's the same for DAO.

...
varBookmark = Me.Recordset.Bookmark
Me.Requery
Me.Recordset.Bookmark = varBookmark
...

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[formerTexan]

Have you placed a breakpoint in your code and stepped through it to see what value is being passed to varBookmark? Alternatively you can use a msgbox or debug.print.

Otherwise the code looks workable.

Cheers, Bill

 

[billheath]

I can't get it to work either way. I am passing varBookmark to debug.print and it is passing the data correctly

 

[billheath]

Apparently, I am not getting bookmarks assigned. When I try to capture the rs.bookmark, it is displaying ' . I seem to remember something that you have to do after a requery to set bookmarks. I've looked in Help but am coming up empty.
Thanks for your help.

 

[TheAceMan1]

How are ya billheath . . .

Assuming the form has has a primarykey or unique identifier field try:

[blue]   variablename = Me!UniqueIdentifierName
   Me.requery
   Me.Recordset.FindFirst "[UniqueIdentifierName] =" & variablename[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[formerTexan]

Greetings Bill,

The code is good, so we will look for a problem elsewhere. vise your code to something along the lines of the following. Note that I took your original sample and just added 2 debug.print lines.

Dim rs As DAO.Recordset
Dim varBookmark As Variant
    varBookmark = Me.stopCount
    Me.Requery
Debug.Print "Before " & Me.stopCount
    Set rs = Me.RecordsetClone
    rs.FindFirst "stopCount = " & varBookmark
        If Not rs.NoMatch Then
            Me.Bookmark = rs.Bookmark
Debug.Print "After " & Me.stopCount
        End If
    rs.Close
    Set rs = Nothing

Then come back and let us know what was printed.

A comment on bookmarks. They are methods, meaning they carry out an action. So you won't assign anything to them and they won't return a value.

Cheers, Bill

 

[billheath]

The "Before statement is giving the correct answer. The after is not printing because the "if not rs.nomatch statement" thinks there is no match. Thanks

 

[billheath]

Thanks every one for your help. I found the answer. I forget to format StopCount as time (##) before running the findfirst statement. Dummy me!!

Dim rs As DAO.Recordset
Dim varBookmark As Variant
varBookmark = Me.stopCount
varBookmark = "#" & varBookmark & "#"
Me.Requery
Set rs = Me.RecordsetClone
rs.FindFirst "stopCount = " & varBookmark
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If
rs.Close
Set rs = Nothing

sorry for the confusion!