Synchronize the Values in List Box with a Text Box

[Robert T]

Hello:

We have a query that retrieves the names of almost 5,000 students in our
Student Registration database and they’re displayed in a list box. Obviously
we don’t want the user to get frustrated by forcing him/her to scroll through
thousands of records every time they want to find a particular student so
they can register the student for a class. It was suggested in another thread
that we create a text box on the form so the user can type in the last name
of the student in the text box and the list box would immediately move to
that name.

Don’t know if I explained it well so I’ll try it another way. We want the
same capability in our List Box that ‘s available in a combo box where a user
can type in the first few letters of the student’s last name and the query in
the list box will automatically move to the first Lastname that matches what
was typed in the text box.

The concept sounds simple, however, I’m relatively new to Access and need
help with where and how to code this.

Thanks,
Robert

 

[Al Campagna]

Robert,
Since we haven't seen your previous thread...
Why aren't you just using a Combobox, which gives you that Auto Expand capability?
You wrote...

they want to find a particular student so they can register the student for a class.

If you're only selecting one student from the list, why reinvent the wheel. If you
were doing a Multi-Select, then a List would have to be the choice, but for one selection,
use a combo.
Also, considering that you can Me!cboYourCombo.DropDown, the combo at any time (except
for Multi Select) a Combo has all the convenience of a Listbox... and more.

 

[Robert T]

Good questions Al, let me try to answer them.

1. Consistency
----------------
We have a One to Many Form with two Listboxes which enables the user to
register One Student to Many Classes. The user selects a student from a Combo
Box, then below the combo box are two list boxes. The one on the left is a
list of all the available classes, while the List Box on the right is a list
of all the classes where the student is registered.

The user simply clicks on all of the classes in the ListBox on the left
which the student selected, then clicks on an arrow box, and voila, the
student is registered for several classes in one silky smooth procudure. It
is very cool and efficient.

If the user made a mistake, he/she simply clicks on a class in the right
ListBox, clicks on a left arrow icon in a box, and the class moves back to
the listbox on the left with the list of all classes the student did NOT
register for.

The above works seamlessly. One of the reasons it works so well is the fact
that the number of available classes in the ListBox on the left is only 35 to
40. This is very different from the One Class to Many Students form because
we have almost 5,000 students in our student table. See the next paragraph.

OK, there is also a 2nd form with two ListBoxes that is One Class to Many
Students and THIS IS THE ONE I'M TALKING ABOUT IN THIS THREAD. Although we
usually use the One Student to Many Classes form, sometimes we will select
One Class and register students one at a time. We originally had a
multi-select box but we had problems because students were showing up as
registered on the list box on the right who didn't belong there so we changed
it from multi-select to single select.

Since we usually utilize the two list boxes in the One Student to Many
Classes form described earlier, I think we should be consistent in terms of
doing exactly the same thing on the One Class to Many Students Form. I think
having a list box on the former and a combo box on the latter could cause
some confusion on the part of the user. In addtion, it would probably look
less than professional with two different approaches.

2. Functionality
-----------------
One of the features built into our two list boxes is the ability to click on
a LEFT arrow icon and move students and/or classes from the registered List
Box on the right, back to it's original position in the List Box on the Left.
This is very handy if and when the user made a mistake and clicked on the
wrong name or names.

Wheeeeeeeeew, that was a long winded explanation, I hope it was clear.

Thanks,
Robert

 

[Al Campagna]

Robert,
Well, I take it the upshot is that on the second form you described, the listbox
concerned would be a Multi Select (the Students in OneClass Many Students)
My original thought was to use a text control to filter the list, and upon a Google
Group search just to see what might be out there regarding this problem, I think that
would be a reasonable solution. Please try your own group search just so you can see some
sample responses... I used
"public.access" "listbox" "autoexpand"

Here's a response from Sandra Daigle... and I would certainly give this a go first.
---------------------------
I'm not sure if this is exactly what you want but you can pair a textbox
with a listbox, then using the following code the listbox will narrow the
choices in the list based on what you type into the textbox (simply using
the Like operator in the SQL).

Add a textbox just above the listbox - add this code to the change event for
the textbox (adjust control, field and table names for your project):

Private Sub Text6_Change()
Const strBaseSQL As String = "Select Custid, Custname from Customer "
Me.txtSQL = strBaseSQL & "Where Custname like """ _
& Me.Text6.Text & "*"" Order by Custname;"
Me.List2.RowSource = Me.txtSQL
End Sub
-
Sandra Daigle
[Microsoft Access MVP]
---------------------------------

I would consider adding a small "clear" button next to the search field. Type a partial
name in the search field till you drill down far enougfh to make a list selection, select
from the list, "clear" the search field (Alt-C), and start another name. That should give
you a bit of "automation."
That should do it... not "elegant" but utile nonetheless,

--
Al Campagna
Candia Computer Consulting
67 Douglas Dr
Candia, NH 03034-2305
603.483.5388
(e-mail address removed)
http://home.comcast.net/~cccsolutions