Filter list box by text box

[Edward H via AccessMonster.com]

I have a query form. The form have many text boxes. I want to use the values
entered in these text boxes in order to requery my list box. My list box is
linked to a query that displays all entries in my table.
Basically, I want to search my table according to the MemID or FirstName or
LastName. So If I enter a name in the FirstName text box, and click
FindRecord. I want my list box to display all matching fields.

Please help. I have been all over this website looking for a solution.

 

[Marshall Barton]

I have a query form. The form have many text boxes. I want to use the values
entered in these text boxes in order to requery my list box. My list box is
linked to a query that displays all entries in my table.
Basically, I want to search my table according to the MemID or FirstName or
LastName. So If I enter a name in the FirstName text box, and click
FindRecord. I want my list box to display all matching fields.

When the text box(es) must always have a value, you can get
away with simply using a criteria in the list box's row
source query like:
=Forms!nameofform!nameoftextbox

However, when the text box might not have a value, you need
to expand it to:
=Forms!nameofform!nameoftextbox OR
Forms!nameofform!nameoftextbox Is Null

 

[Guest]

Hi!

I'd slightly modify Marshall's suggestion, and that is this:

make the criterion:

Like "*" & Forms!nameofform!nameoftextbox & "*"

This does two things:
(1) it means that you dont need the 'or ... is null' bit;
(2) the search is more flexible - you don't need a precise match in your
Search. (example: someone mistyped a name, by including a space in front of
it. So the entry is, say, " Smith". My method would find it! Also, you don't
need to type the entire name. Useful if there's part of the name where you're
unsure of the spelling. It happens, honest!!)

Hope it helps.

All the best

 

[Edward H via AccessMonster.com]

Thank you both. I will implement it tonite.
I just need to know where should this code be bound to?
The search button? the listbox bound to the query? or in the textbox?

Appreciate all of your help!

Hi!

I'd slightly modify Marshall's suggestion, and that is this:

make the criterion:

Like "*" & Forms!nameofform!nameoftextbox & "*"

This does two things:
(1) it means that you dont need the 'or ... is null' bit;
(2) the search is more flexible - you don't need a precise match in your
Search. (example: someone mistyped a name, by including a space in front of
it. So the entry is, say, " Smith". My method would find it! Also, you don't
need to type the entire name. Useful if there's part of the name where you're
unsure of the spelling. It happens, honest!!)

Hope it helps.

All the best

I have a query form. The form have many text boxes. I want to use the values
entered in these text boxes in order to requery my list box. My list box is

[quoted text clipped - 4 lines]

Please help. I have been all over this website looking for a solution.

 

[Guest]

Hi again

The criterion goes in the query that's bound to the list form. Then all you
need is a button with the single command "DoCmd.Requery" - type tyhe name
you're looking for, click the button, and the form 'filters' itself. To get
the full list back, just blank the search box and hit the button again.

A further thought. If it's possible that the field you're searching on
might, on some records, be null, then you'll need to add to the criterion 'Or
Is Null', otherwise those records will never appear. (It's a frustrating
fact, and one that took me a while to learn, that a 'zero length string' and
a 'null' are not the same. Intellectually I can't quite grasp it, any more
than I can quite grasp the concept of infinity or of the 'nothingness' that
apparently preceded the big bang - but at least I do know how to get around
the problem!! But enough of philosophy...)

Hope it helps!
--
Laury Burr a.k.a. Doogle
data dot dynamics at virgin dot net

Thank you both. I will implement it tonite.
I just need to know where should this code be bound to?
The search button? the listbox bound to the query? or in the textbox?

Appreciate all of your help!

Hi!

I'd slightly modify Marshall's suggestion, and that is this:

make the criterion:

Like "*" & Forms!nameofform!nameoftextbox & "*"

This does two things:
(1) it means that you dont need the 'or ... is null' bit;
(2) the search is more flexible - you don't need a precise match in your
Search. (example: someone mistyped a name, by including a space in front of
it. So the entry is, say, " Smith". My method would find it! Also, you don't
need to type the entire name. Useful if there's part of the name where you're
unsure of the spelling. It happens, honest!!)

Hope it helps.

All the best

I have a query form. The form have many text boxes. I want to use the values
entered in these text boxes in order to requery my list box. My list box is

[quoted text clipped - 4 lines]

Please help. I have been all over this website looking for a solution.

 

[Edward H via AccessMonster.com]

Thank you all!
You guys are great.
Just one more thing..
Once I query for the specific entries, is there any way for me to change the
values in these entries?
It seems like I need to make a subform linked to the listbox, which I do not
know how to do, in order for me to change any of the entries.

Hi again

The criterion goes in the query that's bound to the list form. Then all you
need is a button with the single command "DoCmd.Requery" - type tyhe name
you're looking for, click the button, and the form 'filters' itself. To get
the full list back, just blank the search box and hit the button again.

A further thought. If it's possible that the field you're searching on
might, on some records, be null, then you'll need to add to the criterion 'Or
Is Null', otherwise those records will never appear. (It's a frustrating
fact, and one that took me a while to learn, that a 'zero length string' and
a 'null' are not the same. Intellectually I can't quite grasp it, any more
than I can quite grasp the concept of infinity or of the 'nothingness' that
apparently preceded the big bang - but at least I do know how to get around
the problem!! But enough of philosophy...)

Hope it helps!

Thank you both. I will implement it tonite.
I just need to know where should this code be bound to?

[quoted text clipped - 27 lines]