We help IT Professionals succeed at work.

Best method to filter out items shown in an Access listbox using VBA?

stephenlecomptejr
on
Medium Priority
558 Views
Last Modified: 2016-08-29
Please note I have a listbox and I want to filter out and shown only items that are entered in a textbox - txtFilter and anything that starts with that value in that text box?  What's the most efficient code to make this work?  Again, I just want to filter the records but not disturb the recordsource...

Please note sample:
https://filedb.experts-exchange.com/incoming/ee-stuff/8159-sample-filter.mdb

sample-filter.mdb
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
since you are using value list for the row source type of the listbox,
you need to recreate the list everytime you filter the listbox

Author

Commented:
Do you think I should throw that in a temp table and then have it temporarily tie to that on the filter - then as they filter out more - go with that?

Author

Commented:
Doesn't Microsoft think we would not like that feature?
CERTIFIED EXPERT
Top Expert 2016

Commented:
<and I want to filter out and shown only items that are entered in a textbox>

what will the normal entry be ? partial item,  first 5 letters ??

Author

Commented:
I originally plan to allow a person to enter * (asterix) for ignoring characters before/after.

so if they type AB* - then it will only show the names that start with AB

if they type *AB* - then nit will search for in between

*AB - ignore the beginning stuff.
CERTIFIED EXPERT
Top Expert 2016

Commented:


try this,

enter PLAZA
or
OVAL


sample-filter.mdb

Author

Commented:
It's not working...sorry.
CERTIFIED EXPERT
Top Expert 2016

Commented:
what do you mean it is not working.. see this image
FilterListBox.jpg

Author

Commented:
NO sorry.   I type Typical in the Filter text box and then click GO button and it does not filter...

Author

Commented:
Can you download and try again?
If it works for you - all I can do on my end is make a video of me downloading and to the location - then open and run to show you...
CERTIFIED EXPERT
Top Expert 2016

Commented:
just did and it work perfectly ok... with the Go button and Undo all filter

Author

Commented:
Please disregard last post..

will resubmit
CERTIFIED EXPERT
Top Expert 2016

Commented:
i can't see anything on the .avi file you uploaded
tried QT and WMP
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
Can I ask why you're opening a recordset, to then assign the listbox control's rowsource as text?
Why not assign it directly as a Rowsource property of the SQL statement (OK that would involve subsequent queries as you "filter") or from the recordset itself?

And if efficiently filtering is a factor (i.e. filtering the loaded text rather than making subsequent database calls) - then keep the recordset open, and filter it before re-assigning it (filtered) to the list control.

Cheers.

Author

Commented:
capricorn1,

please note images:

even after entering typical in the filter.
print-values0.png
print-values.png

Author

Commented:
LPurvis, I'd rather try to do as much manipulation in the recourdsource of the listbox then save to the recordset once finished... but did not know the method before shown to me by capricorn1.

I've never used LBound or RBound before....nor the Split function.  It seems like the newlist is still not filtering out correctly what's entered though.
CERTIFIED EXPERT
Top Expert 2016
Commented:
use this codes  


Private Sub cmdGO_Click()
  cmdUndo.Visible = True
newList = ""
  Dim lstArr, j As Integer
'lstArr = Split(origList, ";;")
lstArr = Split(origList, ";'';")

For j = LBound(lstArr) To UBound(lstArr)
    If InStr(lstArr(j), Me.txtFilter) > 0 Then
        newList = newList & lstArr(j) & ";'';"
    End If

Next
Me.lstVolume.RowSource = newList
End Sub
CERTIFIED EXPERT
Top Expert 2016

Commented:


better if you do it this way

set the Column Count property of the  List box to  1

then use this codes


Private Sub cmdGO_Click()
  cmdUndo.Visible = True
newList = ""
  Dim lstArr, j As Integer

lstArr = Split(origList, ";")

For j = LBound(lstArr) To UBound(lstArr)
    If InStr(lstArr(j), Me.txtFilter) > 0 Then
        newList = newList & lstArr(j) & ";"
    End If

Next
Me.lstVolume.RowSource = newList
End Sub

Open in new window

Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
>> I'd rather try to do as much manipulation in the recourdsource of the listbox

My point was more about why?
Working with text lists can be limiting when the list becomes very long (impossible beyond 32K characters - bear in mind that can typically mean a list not longer than about 1K entries... Usually longer than most would want to make a list, but not necessarily so.)

As opposed to assigning the source originally
Set Me.lstVolume.Recordset = rst

Open in new window


And filtering by
Set rst.Filter = "FieldName Like '*" & Me.txtControl & "*'"
Set Me.lstVolume.Recordset = rst.OpenRecordset

Open in new window


With the recordset declared at the module level and kept open between procedures, and the list control is kept in "Table/Query" type mode for such assignments.  

Author

Commented:
LPurvis, would you be able to please rearrange the sample I have above where I could see how you use VBA to set the listbox in the way you describe above?
Leigh PurvisDatabase Developer
CERTIFIED EXPERT
Commented:
Well... s'pose - as the code is already on show.  (Prefer to have code solutions open and easily viewable rather than having to download a file.)

Ultimately - the entire module just looks like:
Private rst As DAO.Recordset

Private Sub cmdUndo_Click()
    rst.Filter = ""
    Set Me.lstVolume.Recordset = rst.OpenRecordset
End Sub

Private Sub Form_Open(Cancel As Integer)
    Call Set_ListBoxes
End Sub

Private Sub Set_ListBoxes()
    Call Fill_Volume_ListBox
End Sub

Private Sub Fill_Volume_ListBox()
  
    Dim lVol As Long
    Dim sSQL As String

    lVol = 1
    sSQL = "SELECT VolFileName, VolumeID FROM tblVolFiles WHERE VolumeID = " & lVol
    
    Set rst = CurrentDb.OpenRecordset(sSQL)
    Set Me.lstVolume.Recordset = rst
    
End Sub

Private Sub cmdGO_Click()
    
    rst.Filter = "VolFileName Like '*" & Me.txtFilter & "*'"
    Set Me.lstVolume.Recordset = rst.OpenRecordset
    Me.cmdUndo.Visible = True
    
End Sub

Open in new window

sample-filter.zip

Author

Commented:
Thanks to all!