Find not behaving consistently in .ADP form

Is there something wrong with the following code (in an ADP form) ?

Private Sub cmbSearchCode_AfterUpdate()
  Me.Recordset.Find "usr_cde = '" & ctl & "'"`
  ctl = null
end sub

It behaves erratically: the first times after opening the form, it works, then suddenly it does not seem to do anything anymore.

I replaced it by this code that seems to have no problems:

With Me.RecordsetClone
    .Find "usr_cde = '" & ctl & "'"
    Me.Bookmark = .Bookmark
End With
ctl = Null

Any explanation ? The client is A2003, the server is SS 2000 (I know it's old, nothing I can do about it !)

Solution

The Form.Recordset Property is a fairly new addition to access, there are a couple of bits in the helptexts, it's how they combine that I believe is causing your issue.

If a form is based on a query, for example, referring to the Recordset property is the equivalent of cloning a Recordset object by using the same query. However, unlike using the RecordsetClone property, changing which record is current in the recordset returned by the form's Recordset property also sets the current record of the form.

So while it doesn't look like it at first, it does actually clone the recordset and create a new copy of it. Just it keeps it synchronised.

So you have a new Recordset Object and here's the rub:

A new Recordset object is automatically added to the Recordsets collection when you open the object, and is automatically removed when you close it.

First time round, you clone the recordset find on usr_cde, and the form magically sets the current record to match.

Second time round, you clone the recordset find on usr_cde, but the magical record synchronisation is still stuck on the first copy of the recordset that has been persisted in the Recordsets collection.

So you just need to close the recordset, but to make sure you don't just create another copy and close that do the following:

'untested
Private Sub cmbSearchCode_AfterUpdate() 
  Dim rs as adodb.Recordset

  Set rs = Me.Recordset
  rs.Find "usr_cde = '" & ctl & "'"`
  rs.Close

  ctl = null
end sub

I haven't had a chance to test this, but you already have a working solution using .Bookmark. I hope this explains the unexpected behaviour.