FindFirst only working when setting breakpoint/debugging

[laborimb]

I want to find and move to a record by giving the project number of a record (in a textfield).
When I set a breakpoint to the FindFirst-methodcall everything works fine, the record is found. If I do not set a breakpoint however, the RS.Bookmark is the same as the original Me.Bookmark, so nothing changes.
Here is my code:

        Dim RS As DAO.Recordset
        Set RS = Me.RecordsetClone
        If Not IsNull(Me![PNr_Searchfield]) Then
            ' breakpoint here ?!
            RS.FindFirst "[Proj]![PNr] = '" & Me![PNr_Searchfield] & "'"
            If Not RS.NoMatch Then
                Me.Bookmark = RS.Bookmark
            End If
        End If
        RS.Close
        Set RS = Nothing

I hope someone can explain to me, what's going on here :)

[Stewart Ross]

Hard to say from what you have posted, as the search itself seems OK.

Is your search value control (Me![PNr_Searchfield]) bound or unbound (it would normally be unbound)? If it is bound to your recordsource for whatever reason you may could have a problem with calling the search routine before the record concerned has been updated and saved - I'm just guessing here as I don't know how you are calling your search routine.

You mention that when you set a breakpoint the search works as expected. One possible reason for this is that as the breakpoint takes the focus off the search form Access may be storing the record at that point, allowing the search to proceed normally once you step through your code.

You can test for whether or not the current record has been stored at a point just before you call your search by using something like this:

If Me.Dirty then
  Me.Dirty = False
End If

which forces record storage if the current record is in an unsaved ('dirty') condition. This will not function if it is called from the form's BeforeUpdate event, however - the record storage would trigger this again and lead to a lock-up condition.

As mentioned, without knowing more about how you are calling your search it is difficult to make an informed suggestion for you.

-Stewart

[laborimb]

Thank you very much for your reply and suggestions.

The search value control is an unbound textbox. The search routine is called from within the PNr_Searchfield _KeyDown event handler. Could that be a problem? (I added the Dirty-testing as you suggested: everything seems ok.)

Steve

[Stewart Ross]

Using an unbound control the test for the record being dirty is not required, as the control will not change the underlying record.

You are right to query the use of the Keydown event - it is triggered before any update to the control's value takes place. I suggest you use the control's AfterUpdate event instead.

-Stewart

[laborimb]

Ok, I see. Thanks.
Now, when I use the AfterUpdate event with setting a breakpoint everything works fine, but without setting a breakpoint I get a "Runtime error 2001: You canceled the previous operation." at the line

Me.Bookmark = RS.Bookmark

. Well, that sounds mysterious to me :) Which operation did I cancel?

edit: ok, when I use

Me.Recordset.Bookmark = RS.Bookmark

: no runtime error, but same behaviour as in the first place.

btw: I am using Access 2003, SP3