List Box Search

  • Thread starter Thread starter Mike C.
  • Start date Start date
M

Mike C.

Hello.

I have a form with a list box on that lists specific
fields (LastName, FirstName, State, Organization) of all
the records in my database. And I have it so when I
double-click on the specific record, it opens up the main
form with all of that person's info.

What I would like to do is set up a few text boxes on that
form that will allow me to filter the list box's results.
So if I type in the txtLastName box "Car", all of the
records with the last names beging with "Car" (e.g.
Carter, Carrie, Carson) will come up then, if I type in
the txtFirstName "Mi" all of those names with "Mi" for a
first name will come up (e.g. Carter Mike, Carrie
Michelle, Carson Milford). Is this possible, and if so,
will those records still appear even if they do not have a
State and/or a organization associated with their record?

Any assistance would be appreciated.

Thanks in advance,

m.
 
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
 
Back
Top