M
Mike C.
Sorry for the length of this post...
The following is a solution that I received in regards to
having a text boxes filter out the results of list box.
It worked like a charm, thanks Nikos. My only problem is,
if the record does not contain information for all the
fields the list box is pulling from (e.g. LastName is
blank in the actual record), that record will not show up
in the list box. Is there any way around this?
Thanks in advance,
m.
Here is the previous solution:
Mike,
I'll assume the form is called MyForm1 for this example.
First, open the
form in design view, select the listbox, diplay the
properties window, tab
Data, and in the Row Source property clear the name of the
table that is
currently there, and invoke the query builder (click on
the little butyton
on the right with the three dots).
When in query design view, get the original table, add the
fields required
to the grid, and then go to each field that you want to
filter on and type
an expression like the following in the first criterion
line:
Like Forms!MyForm1!txtLastName & "*"
Likewise for the other fields. The result of this is, the
query reads the
criteria from the form (be sure to use the actual form
name instead of
MyForm1), and if you enter Car the criterion is Car*, if
you enter nothing
the criterion is * so all records are returned.
Close this query design view confirming to save the SQL
statement.
The other thing you need is some way to have the listbox
retrieve a new set
of records everytime you change your criteria. Start by
making a simple
macro with a Requery action, argument: the name of the
listbox; save it with
a meaningful name. Then, back to the form design, select
each of the
criteria controls in turn, select tab Events in
properties, and select (from
the drop down list) the name of your macro in the On
Change event of the
control. So, everytime you change one of the criteria, the
listbox will be
requeried, and display the new set of records.
HTH,
Nikos
The following is a solution that I received in regards to
having a text boxes filter out the results of list box.
It worked like a charm, thanks Nikos. My only problem is,
if the record does not contain information for all the
fields the list box is pulling from (e.g. LastName is
blank in the actual record), that record will not show up
in the list box. Is there any way around this?
Thanks in advance,
m.
Here is the previous solution:
Mike,
I'll assume the form is called MyForm1 for this example.
First, open the
form in design view, select the listbox, diplay the
properties window, tab
Data, and in the Row Source property clear the name of the
table that is
currently there, and invoke the query builder (click on
the little butyton
on the right with the three dots).
When in query design view, get the original table, add the
fields required
to the grid, and then go to each field that you want to
filter on and type
an expression like the following in the first criterion
line:
Like Forms!MyForm1!txtLastName & "*"
Likewise for the other fields. The result of this is, the
query reads the
criteria from the form (be sure to use the actual form
name instead of
MyForm1), and if you enter Car the criterion is Car*, if
you enter nothing
the criterion is * so all records are returned.
Close this query design view confirming to save the SQL
statement.
The other thing you need is some way to have the listbox
retrieve a new set
of records everytime you change your criteria. Start by
making a simple
macro with a Requery action, argument: the name of the
listbox; save it with
a meaningful name. Then, back to the form design, select
each of the
criteria controls in turn, select tab Events in
properties, and select (from
the drop down list) the name of your macro in the On
Change event of the
control. So, everytime you change one of the criteria, the
listbox will be
requeried, and display the new set of records.
HTH,
Nikos