find

[fred]

i have a find button on a form and it works, but when a
record is not found, it moves to the first record. how do
i keep it on the current record? here is my code:

If IsNull(Me.txtFind) Then
MsgBox "Please enter a Policy Number",
vbExclamation, "Input Error"
Me.txtFind.SetFocus
Exit Sub
End If

strPolNum = Me.txtFind
rst.FindFirst "PolicyNum = '" & strPolNum & "'"

If rst.NoMatch Then
MsgBox "The Policy Number was not found.",
vbExclamation, "Not Found"
End If

 

[Allen Browne]

Search the form's RecordsetClone, and then test the NoMatch property.

This example assumes txtFind is an unbound text box where the user enters
the policy number to find:

Private Sub txtFind_AfterUpdate()
Dim strWhere As String

If Not IsNull(Me.txtFind) Then
strWhere = "PolicyNum = """ & Me.txtFind & """"
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found"
Else
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

Note: If PolicyNum is a Nmber field (not a Text type field), drop the extra
quotes, i.e.:
strWhere = "PolicyNum = " & Me.txtFind

 

[fred]

this code gives me the following error message:

you entered an expression that has an invalid reference to
the recordsetclone property

 

[Allen Browne]

Is this data in Access tables? Or attached tables from Oracle or something
else?

If Access and you still receive the error, press Ctrl+G to open the
Immediate window. Choose References from the Tools menu, and make sure you
have selected:
Microsoft DAO 3.6 Library.

More info:
http://members.iinet.net.au/~allenbrowne/ser-38.html