Select multiple list box items from the text box entries

[gimmyd]

I am using Access 97. I have a multiselect list box that I would like to search using a text box to facilitate selecting items before applying filter. I've tried using snippets of code in these posts to no avail. Any help would be much appreciated.

 

[JerichoJ]

Do I understand your question correctly, you want a user to be able to type in the name of an item in your list box (or partial item name) into a text box, then have the list box automatically select every item in the list that matches?

For example, items in list box:

Fox
Hound
Fox & Hound
Houndstooth

If the user types "Hound" into the text box, you are wanting the "Hound" and "Houndstooth" items to be selected in the list box?

If this is what you want, here is the code:

Dim x As Integer
Dim lstCount as Integer

lstCount = Me.lstItems.ListCount

'You might want to clear the all of the selected items first?
'If so, use this code to do so
For x = 0 To lstCount - 1
  If Me.lstItems.Selected(x) = True Then
    Me.lstItems.Selected(x) = False
  End If
Next x
'End of code to clear selected items in list

For x = 0 To lstCount - 1
  If Me.lstItems.ItemData(x) Like "*" & Me.txtSearch & "*" Then
    Me.lstItems.Selected(x) = True
  End If
Next x

Note that if your list box has multiple columns and you need to search against a column other than the bound column, then you need to change the ItemData reference to specify the optional column parameter such as:

  Me.lstItems.ItemData(x, 1) Like ....

Remember that the columns of list boxes and combo boxes use a 0 (zero) base for counting, so column 1 is actually the second column.

Also note that if you have the column heads set to "Yes" then the .ListCount property returns 1 too many. Access includes the Column header row in the count of items in the list, so you would want to run the For loop from 1 To lstCount instead of from 0.

I hope this helps.

 

[gimmyd]

I was not clear. I have a very large list of unique items. As each item is selected in the list box it is moved to an adjacent text box. I would like to be able to search the list box via another text box, click on an item it goes in the list box from typing an account number in the search text box and repeat the procedure as many times as necessary to get all account numbers needed for the filter to proceed.

 

[JerichoJ]

Ok,

Well, the solution I posted is still basically correct. List boxes are not searchable by themselves. You have to search through them in the way that I have shown. The difference could be that instead of just selecting the item that matches, you go ahead and run the code that adds that item to your adjacent text box.

You would put this code in the AfterUpadate event of your search text box.

Dim x As Integer
Dim lstCount as Integer

lstCount = Me.lstItems.ListCount
For x = 0 To lstCount - 1
  If Me.lstItems.ItemData(x) Like Me.txtSearch & "*" Then
    Me.lstItems.Selected(x) = True
    'Call your code that moves this item
    'to the adjacent text box
    'Then, since items in list are unique,
    'there is no need to search the rest of the
    'list, so Exit the for loop
    Exit For
  End If
Next x
Me.lstItems = Null
Me.txtSearch = Null
Me.txtSearch.SetFocus

Now, if your list is very, very large, then maybe searching the list box will be too slow. If so, you might consider having a recordset that is bound to the same rowsource as the list box. Then, instead of searching the list box, search the recordset with the .FindFirst method. Then, when you find the item in the recordset, all you have to do is set the list box equal to the recordset item. Then run your code to move the item, etc. Of course, I am assuming that your list box rowsource is either a query, or an sql string. If it is a value list, this won't work.

Dim rst as DAO.Recordset
Dim db as DAO.Database

Set db = CurrentDb()
Set rst = db.OpenRecordset(Me.lstItems.RowSource, dbOpenSnapshot)

rst.FindFirst "[AcctNum] = " & Me.txtSearch
If Not rst.NoMatch Then
  Me.lstItems = rst("AcctNum")
  'Call code to move item
Else
  'Do something else if item not found

End If
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing

Now, you can always have the recordset set up to be global to the form so that you don't have to go through the overhead of setting it every time they search.