List Box Selection

[Peter Hibbs]

Access 2000.

I have a standard list box on a form, bound to a query, which shows
several hundred names in alphabetical order. Is there any way to move
the selection shown using VBA code.

What I want to do is allow the user to enter a letter/s into a text
box which will then change the visible rows in the list box to show
the names starting with that letter. I do NOT want to change the query
criteria to show only those names that begin with the letter (which is
easy enough to do), what I want is to, in effect, move the scroll bars
down so that the first row of the list box shows the first name
beginning with B or C, or whatever is entered.

TIA

Peter Hibbs.

 

[Klatuu]

Is there any particular reason to use a list box? What you are describing is
the natural behaviour of a combo box with the Auto Expand property set to Yes.

 

[Peter Hibbs]

Dave,

Yes. I should have also mentioned that the List box is Multi-Select,
the user needs to select one or more names in the list which are then
copied to a table when they click a button. They just need an easier
method of finding a specific name without having to scroll through
hundreds of entries. This is also the reason I did not want to filter
the list in the query because they need to be able to select several
records.

I can't see any easy way to do this so maybe I am going to have to
devise some other 'user friendly' method.

Peter.

 

[Klatuu]

In this case, you could use a text box to do this. I'm not sure how the
performance would be because you would have to requery the list box with
every keystroke.

For this you would need to use the Change event of the text box. The Change
event fires on every keystroke.

First, you would need to set your list box row source query up so it would
filter on the test box:

SELECT YourFieldName FROM Your Table WHERE YourField LIKE
forms!YourFormName!YourTextBox Name & "*";

The way this works is that initially, the text box would be empty and its
value would be Null, so that with the *, it would return all records. Then
in the Change event, you would requery the list box and it would match on
what you have entered so far.

All you need to do is:

Private Sub MyListBox_Change()
Me.MyListBox.Requery
End Sub

 

[Peter Hibbs]

Thanks Dave, I will give that a go.

Peter.

 

[Klatuu]

Okay, post back if you have questions. Let me know how it goes. I am
concerned about performance, though.

 

[Peter Hibbs]

Dave,

Works fine, no problems with performance as there are only about 4000
records and it's running on a quick PC. Incidentally the query needed
to be linked to the Text property of the Text box rather than the
Value property but apart from that your code was OK.

I have redesigned the form slightly because it is not easy to select
multiple records and then read them from the list box using this
method but I think the client will be happy with that.

Peter Hibbs.

 

[Klatuu]

I don't remember us discussing a query. Note that the Text property of a
text box is only valid while the text box has the focus.
But, I am glad you got it working.