Filtering information in a list box

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

--
Wayne Morgan
Microsoft Access MVP


"Deb Smith" wrote in message
...
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!!

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.
"Wayne Morgan" wrote in message
...
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
Microsoft Access MVP


"Deb Smith" wrote in message
...
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!!

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.

--
Wayne Morgan
MS Access MVP


"Deb Smith" wrote in message
...
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.
"Wayne Morgan" wrote in
message
...
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
Microsoft Access MVP


"Deb Smith" wrote in message
...
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!!

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


On Thu, 3 Jun 2004 09:21:22 -0400, "Deb Smith"
wrote:

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

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

On Thu, 3 Jun 2004 09:21:22 -0400, "Deb Smith"
wrote:

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