Performing a search in a form

T

Trenchardc

Hi again!

I have a table, and a form which displays the same data. I wish to be able
to search the form, and have my results displayed in the style of my form.
The search will be a text box on a separate form, and when you press enter,
hopefully it will search. I am not an expert at access, but have fiddled with
some macros to no result. If it makes it easier, the search box could be part
of the original form to avoid confusion.
As already mentioned, I am not amazing at access, but understand basic
macros. So please keep answers fairly easy to understand, or go through in
steps what I will need to do to achieve this. Thanks a lot!
 
A

Allen Browne

Place the text box on the form, and use its AfterUpdate event to FindFirst
in the RecordsetClone of the form.

There's an example, and the code in this article:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html
Although the article uses a combo, the code is the same for a text box.

If there might be multiple matches for the entry, you might like to set the
Filter of the form so it contains only the matches. The After Update event
procedure would looke something like this:
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
strWhere = "[SomeField = """ & Me.[Text0] & """"
Me.Filter = strWhere
Me.FilterOn = True
 
T

Trenchardc

I tried most of this in various ways, but unfortunately didn't succeed.
I have the following in Visual Basic:

Sub Search_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.Search) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Name]" = "Me.Search"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

My database has the form called addresses, which is an address book, and I
am wishing to search for a name by using this text box in the addresses form.
Thanks.


Allen Browne said:
Place the text box on the form, and use its AfterUpdate event to FindFirst
in the RecordsetClone of the form.

There's an example, and the code in this article:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html
Although the article uses a combo, the code is the same for a text box.

If there might be multiple matches for the entry, you might like to set the
Filter of the form so it contains only the matches. The After Update event
procedure would looke something like this:
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
strWhere = "[SomeField = """ & Me.[Text0] & """"
Me.Filter = strWhere
Me.FilterOn = True

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Trenchardc said:
Hi again!

I have a table, and a form which displays the same data. I wish to be able
to search the form, and have my results displayed in the style of my form.
The search will be a text box on a separate form, and when you press
enter,
hopefully it will search. I am not an expert at access, but have fiddled
with
some macros to no result. If it makes it easier, the search box could be
part
of the original form to avoid confusion.
As already mentioned, I am not amazing at access, but understand basic
macros. So please keep answers fairly easy to understand, or go through in
steps what I will need to do to achieve this. Thanks a lot!
Click to expand...
Click to expand...
 
A

Allen Browne

What happens?
Error message?
No results?

The quotes are wrong on the FindFirst line. Try:
rs.FindFirst "[Name] = """ & Me.Search & """"

Then make sure the code compiles, by choosing Compile on the Debug menu.

You should get away with it in this context, but Name is not a good field
name. Most objects (inculding forms) have a Name property, and Access gets
confused what you are referring to.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Trenchardc said:
I tried most of this in various ways, but unfortunately didn't succeed.
I have the following in Visual Basic:

Sub Search_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.Search) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Name]" = "Me.Search"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

My database has the form called addresses, which is an address book, and I
am wishing to search for a name by using this text box in the addresses
form.
Thanks.


Allen Browne said:
Place the text box on the form, and use its AfterUpdate event to
FindFirst
in the RecordsetClone of the form.

There's an example, and the code in this article:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html
Although the article uses a combo, the code is the same for a text box.

If there might be multiple matches for the entry, you might like to set
the
Filter of the form so it contains only the matches. The After Update
event
procedure would looke something like this:
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
strWhere = "[SomeField = """ & Me.[Text0] & """"
Me.Filter = strWhere
Me.FilterOn = True

Trenchardc said:
Hi again!

I have a table, and a form which displays the same data. I wish to be
able
to search the form, and have my results displayed in the style of my
form.
The search will be a text box on a separate form, and when you press
enter,
hopefully it will search. I am not an expert at access, but have
fiddled
with
some macros to no result. If it makes it easier, the search box could
be
part
of the original form to avoid confusion.
As already mentioned, I am not amazing at access, but understand basic
macros. So please keep answers fairly easy to understand, or go through
in
steps what I will need to do to achieve this. Thanks a lot!
Click to expand...
Click to expand...
Click to expand...
 
T

Trenchardc

Great! Nearly done. This works with the new quotes for the FindFirst line.
Thanks for that.
The only thing now, is that when I search, I have to type in the full name
exactly as it is in the database. Is there a way to set the search to find
some of this search in the database? I tried using a wildcard (*) after the
start of a name which I know is in the database and it does not seem to work.
Thanks for your help.


Allen Browne said:
What happens?
Error message?
No results?

The quotes are wrong on the FindFirst line. Try:
rs.FindFirst "[Name] = """ & Me.Search & """"

Then make sure the code compiles, by choosing Compile on the Debug menu.

You should get away with it in this context, but Name is not a good field
name. Most objects (inculding forms) have a Name property, and Access gets
confused what you are referring to.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Trenchardc said:
I tried most of this in various ways, but unfortunately didn't succeed.
I have the following in Visual Basic:

Sub Search_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.Search) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Name]" = "Me.Search"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

My database has the form called addresses, which is an address book, and I
am wishing to search for a name by using this text box in the addresses
form.
Thanks.


Allen Browne said:
Place the text box on the form, and use its AfterUpdate event to
FindFirst
in the RecordsetClone of the form.

There's an example, and the code in this article:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html
Although the article uses a combo, the code is the same for a text box.

If there might be multiple matches for the entry, you might like to set
the
Filter of the form so it contains only the matches. The After Update
event
procedure would looke something like this:
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
strWhere = "[SomeField = """ & Me.[Text0] & """"
Me.Filter = strWhere
Me.FilterOn = True

Hi again!

I have a table, and a form which displays the same data. I wish to be
able
to search the form, and have my results displayed in the style of my
form.
The search will be a text box on a separate form, and when you press
enter,
hopefully it will search. I am not an expert at access, but have
fiddled
with
some macros to no result. If it makes it easier, the search box could
be
part
of the original form to avoid confusion.
As already mentioned, I am not amazing at access, but understand basic
macros. So please keep answers fairly easy to understand, or go through
in
steps what I will need to do to achieve this. Thanks a lot!
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Use the Like operator, and the wildcard:
rs.FindFirst "[Name] Like """ & Me.Search & "*"""

To explain the quote marks, you cannot type:
"This string has a "word" in quotes"
because VBA thinks the quote closes before word, and then doesn't know what
to do with the rest of the string. The convention is to double up the
quotes, so you need:
"This string has a ""word"" in quotes"

So, if the last thing in a string is a quote mark, you end up with 3
together:
"This string has a ""word"""
 
Top