MS Access Recordset Moving on Search even if no match found?-CodePudding

This is driving me nuts. I have an access VBA form where I want to allow users to type a document number into a box in order to jump directly to that record.

The underlying table as a field "DocNum", and the number is always sequential. I want the searchbox to display the current Doc Number the user is on, if they type in a different number and hit enter, it should either jump to that record if it exists, or if it doesn't exist, stay on the current record.

I'm trying to accomplish thisby having a hidden textbox bound to "DocNum", and an unbound visible box on top of it. On Form_Current() the unbound box is made to match the underlying field. After, update I run the following code to perform the search.

Private Sub txt_DocNumSearch_AfterUpdate()

    Dim rs As Object
        
    Set rs = Me.RecordsetClone
    
    With rs
        rs.FindFirst "[DocNum] = " & Str(Me![txt_DocNumSearch])
        
        If rs.NoMatch Then
            MsgBox "Record not found."
            GoTo Cleanup
        Else
            Me.Bookmark = rs.Bookmark
            Exit Sub
        End If
    End With
   
Cleanup:
            rs.Close
            Set rs = Nothing
            Set dbs = Nothing

End Sub

What's driving me insane is that even when it doesn't find a match.... it skips to the next record anyway. No matter what I do... move last, trying to bookmark before I even search, etc... I can't get it to not jump forward. What am I missing?

CodePudding user response：

Try simplifying it:

Private Sub txt_DocNumSearch_AfterUpdate()

    Dim rs As DAO.Recordset
        
    Set rs = Me.RecordsetClone
    
    With rs
        .FindFirst "[DocNum] = " & Str(Me![txt_DocNumSearch])        
        If .NoMatch Then
            MsgBox "Record not found."
        Else
            Me.Bookmark = .Bookmark
        End If
        .Close
    End With

End Sub