Searching in VBA like the find function

[John Constantine]

I want to search a table , query or continuous form and have the
search will take you to the match item whilst still displaying all
other items eg similar to the find option ( ie when you use Control
+F to find something )
A normal query will only display the matched items
I want to display all items but move the curser to the match

How do you do this with a variable input item in VBA ?

 

[John Spencer]

Why not just use the built-in find function?

In a continuous form, you could search the recordsetClone, get the bookmark
for the found record, and then set the form's recordset bookmark equal to the
found bookmark.

If you could give a more concrete example of exactly what you want to do,
perhaps you will get a more concrete solution.

Do you want a generic routine that you can pass in a field name, a value to
find, and a source object (a table, a query, or a form)?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John Constantine]

Why not just use the built-in find function?

In a continuous form, you could search the recordsetClone, get the bookmark
for the found record, and then set the form's recordset bookmark equal tothe
found bookmark.

If you could give a more concrete example of exactly what you want to do,
perhaps you will get a more concrete solution.

Do you want a generic routine that you can pass in a field name, a value to
find, and a source object (a table, a query, or a form)?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County




- Show quoted text -

John S thanks for reading it

Actually I did suspect it was searching the recordsetclone but I was
hoping to see an example of the code to try it out
I haven't had enough experience with recordsetclones to write it from
scratch myself
Why do I want it ?
Because I thought it would be handy in using it in a form / subform
set up

 

[John Spencer]

Here is a sample that you might be able to modify. This one is designed to be
in a form's code, but you could generalize it by passing in the name of a form
and the where clause.

Private Sub fFindRecord()

Dim rst As DAO.Recordset: Set rst = Me.RecordsetClone
Dim strFind As String

'Where clause without the Where
strFind = "NameOffield = ""stringValue"" "
'For a number that would be strFind = "NameOfField = numbervalue"

With rst
.FindNext strFind 'Start searching from the current location
If .NoMatch = False Then
Me.Bookmark = .Bookmark
Else 'Nothing found so search from the beginning
.FindFirst strFind
If .NoMatch = False Then
Me.Bookmark = .Bookmark
Else 'WHOOPS nothing found
'Beep or message box
End If
End If

End With
End Function

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County