Filtering information in a list box

  • Thread starter Thread starter Deb Smith
  • Start date Start date
D

Deb Smith

I have a form with a list box that contains three items
(LName,FName,PersonID). Using recordset and Bookmark when you click on an
item in the list box the form's information is automatically populated.

There are a large number of records in the list box so scrolling to find the
appropriate record is cumbersome. I want to filter the records in the list
box alphabetically by lname(column1) so that only a limited number of
records display in the list box at a time. For example only records with
people with last names starting with "C".

I just can't seem to get this to work.

Help Please!!
 
Base the Row Source of the listbox on a query. Set the sorting in the query
as desired. In the criteria of the query place

Like Forms!frmMyForm!txtMyTextbox & "*"

under the last name field. Add a textbox to the form that the user can type
in the first letter (or more if desired) of the last name. In the
AfterUpdate event of the textbox do a requery on the listbox. The textbox
will Update when the user presses enter or moves to another control.

Me.lstMyListbox.Requery
 
Thanks for the solution! While it works there are some limitations in terms
of my application and the interface with the end-users..

I was wondering if there was another way to accomplish filtering the records
in the list box alphabetically by lname(column1) so that only a limited
number of records display in the list box at a time. Can an option group or
some other button driven method work. I have tried, but have been
unsuccessful.

Also is there a way to make it so that the items in the list box scroll and
correspond to the actual records vs remaining on the original person chosen.

Thanks again for the assistance.
 
Yes, you can use other contols. To use an Option Group, I would probably set
up a hidden, unbound textbox on the form to hold the data for the query's
criteria. The query would refer to this hidden textbox. In the AfterUpdate
event of the option group, update the textbox to the desired value. An
option group returns a number, you'll need to convert this into the
appropriate letter(s).

As far as scolling the listbox goes, you could use the Current event of the
form to get the current user id then set the value of the listbox to this
id. This will move the highlighted selection and, hopefully, will scroll the
listbox when the selection drops out of the visible range.
 
A friend of mine has done this nicely. He clicks on a list of the
alphabet and only the records beginning with that letter will appear.
I've asked him to send me a copy of his db and when I get it I'll
repost on how it's done.

Russ
 
My friend emails me that he borrowed that technique from the Northwind
db. I don't have it installed on this limited space laptop, so I
haven't looked, but you might give it a peek.

Russ
 
Back
Top