Filtering information in a list box

[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!!

 

[Wayne Morgan]

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

 

[Wayne Morgan]

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.

 

[Russ]

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

 

[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