bookmarking problem with recordsetclone

[Ecron]

I have been trouble with a function that i coded to requery a subform and keeping the same record position.

My function is as follows:

Public Function Search_AND_Requery(search As String, frm As subform, nPK As Integer)
    frm.Requery
    
    With frm.Form.RecordsetClone
        .FindFirst search & " = " & nPK
        If Not .NoMatch Then
            frm.Form.bookmark = frm.Form.RecordsetClone.bookmark
        End If
    End With
    
End Function

Now. on the line where it says "frm.Form.bookmark = frm.Form.RecordsetClone.bookmark" it generates the error 2001 which is the canceled previous operation error.

If i hit debug and then just resume the code it works.

Also, something interesting i figured out is that if i put just a blank messagebox before setting the bookmark then everything works out.

Any ideas or help would be lovely!

Thanks

 

[MarkK]

Your code actually creates two clones. One is created at your With block.
Your problem line does not reference the same clone against which you've run your find operation. Your problem line creates a whole new clone.
Try...

With frm.Form.RecordsetClone
  .FindFirst search & " = " & nPK
  If Not .NoMatch Then frm.Form.bookmark = .bookmark
End With

I avoid the bookmark issue altogether as follows...

With frm.Form.Recordset
  .FindFirst search & " = " & nPK
  If .NoMatch Then MsgBox "Not Found"
End With

 

[Ecron]

Hello.

Firstly, I would like to thank you very much for your aide and advice. I notice now that there are two recordsetclones and was completely unaware of that.

After editing the code to match your first example, the problem still arose and instead of highlighting the entire line it just highlighted beginning with frm.Form.bookmark and to the end. I'm assuming it has to do with my frm variable because it is rather... ambiguous, in a way.

Then, I went to try the second example.

Now, the way that I have the form set up. There are some text boxes and whatnot and then a continuous subform with a bunch of entries within it. What I was setting up was a way for the user to just click on of these continuous subform entries and it would automatically select that entry to it's parent form.

The problem with the second example is that the record is not selected, meaning the record indicator along the left hand side is not pointing towards that record... but the record is highlighted without actually being selected. Quite annoying.

In conclusion, I have decided to change a few things and do some combo boxes instead of the continuous subform as it will be much easier and take less work and I won't have to worry about recordsets at all!

Thanks again!