Urgent! FILTER AND QUERY

Ive got a form with three combo boxes, cmbBox1, cmbBox2, cmbBox3 and one listbox, lstView.  I have a list of items being added to the listbox from another source, looking like:

 Frank A 1
 George A 1
 Walter B 2
 Betty A 3
 Sam B 3
 
In cmbBox1 I have the names Frank, George, etc...
In cmbBox2 I have the group  A, B, C, etc....
In cmbBox3 I have the numbers 1, 2, 3, etc....

I want to filter whats showing in the listbox by what I have chosen
from the 3 combo boxes. And I plan to put a blank entry in each
combo box, in case I wanted to just search for a Name and Group and
not the Number or any of the other scenarios. How would I do this? Source very appreciated!

 (Im not using a datasource or database to pull in the
information into the listbox. Thats already taken care of.)
cybrthugAsked:
Who is Participating?
 
nungaConnect With a Mentor Commented:
Hi cybrthug

I think this code should do what you're looking for. I'm not at a computer with VB installed so I haven't been able to test it completely.

I hope it helps.


'*** START Code ***

Sub PopulateList()

Dim strWhereName as String, strWhereGroup as String, strWhereNumber as String
Dim strName as String, strGroup as String, strNumber as String

strWhereName = cmbBox1
strWhereGroup = cmbBox2
strWhereNumber = cmbBox3

List1.Clear

For I = 0 To objGrid.GetRowCount - 1
    strName = objGrid.GetField(Name, I)
    strGroup = objGrid.GetField(Group, I)
    strNumber = objGrid.GetField(Number, I)

   If strWhereName <> "Any" Then
      If strName <> strWhereName Then Goto LoopHere
   End If

   If strWhereGroup <> "Any" Then
      If strGroup <> strWhereGroup Then Goto LoopHere
   End If

   If strWhereNumber <> "Any" Then
      If strNumber <> strWhereNumber Then Goto LoopHere
   End If

   'If you make it to this point, the record matches all the criteria
    List1.AddItem strName & " " & strGroup & " " & strNumber

LoopHere:
Next I

End Sub

'*** END Code ***


0
 
alokanantCommented:
Here is the function which u have to call in the lost focus of each of the combo boxes.


Private Sub FilterList()
    Dim inti As Integer
   
    For inti = 0 To (lst.ListCount - 1)
        If lst.List(inti) = Trim(IIf(Trim(cmb1) = Space(0), "", cmb1 & " ") & IIf(Trim(cmb2) = Space(0), "", cmb2 & " ") & IIf(Trim(cmb3) = Space(0), "", cmb3)) Then
            lst = lst.List(inti)
            Exit Sub
        End If
    Next inti
    lst = " "
End Sub

i have used following data in the form load to test it -
  cmb1.AddItem "George"
    cmb1.AddItem "Frank"
    cmb1.AddItem " "
    cmb2.AddItem "A"
    cmb2.AddItem "B"
    cmb2.AddItem " "
    cmb3.AddItem "1"
    cmb3.AddItem "2"
    cmb3.AddItem " "
    lst.AddItem "George A 1"
    lst.AddItem "Frank B 1"
    lst.AddItem "George A 2"
    lst.AddItem "Frank B 2"
    lst.AddItem "George B 1"
    lst.AddItem "Frank A 1"
    lst.AddItem "George"
    lst.AddItem " "
    cmb1 = " "
    cmb2 = " "
    cmb3 = " "
    FilterList

hth
alok
0
 
cybrthugAuthor Commented:
Ok thats almost perfect! Only thing I need to do is remove the other entries from the listbox (lst) that dont match the combo boxes and I will have what I need, basically just relist the only matched items. :)
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
alokanantCommented:
if u want to remove any item from the list box, u can use the RemoveItem method.
or is it something else which u want?
please clarify?

alok.
0
 
alokanantCommented:
if u want to remove any item from the list box, u can use the RemoveItem method.
or is it something else which u want?
please clarify?

alok.
0
 
alokanantCommented:
if u want to remove the unmatched entries u can modify the function as follows -
Private Sub FilterList()
    Dim inti As Integer
     
    For inti = 0 To (lst.ListCount - 1)
        If lst.List(inti) = Trim(IIf(Trim(cmb1) = Space(0), "", cmb1 & " ") & IIf(Trim(cmb2) = Space(0), "", cmb2 & " ") & IIf(Trim(cmb3) = Space(0), "", cmb3)) Then
            lst = lst.List(inti)
        Else
            lst.RemoveItem(i)
        End If
    Next inti
    lst = " "
End Sub

0
 
alokanantCommented:
also remove the last lst = " " statement from the function.

but have u considered the case when there is a no matching entry found in the list box. all the items in the list box shall then be removed.
will that be ok?

alok.
0
 
cybrthugAuthor Commented:
When I choose from any of the combo boxes I want only the choices that I have selected to show up in the ListBox rather then it keep the whole list in there. The main reasoning for this is there can be duplicate Names, Groups, and maybe different Numbers for individuals and I want them to all pull up but only the duplicates and nothing else.
0
 
cybrthugAuthor Commented:
If there is no matching entry then I definately want it to show nothing. I want all duplicate entries found. It looks like your last answer did remove items but for some reason its not pulling anything up now :( Thanks for all the help so far, I can tell we're getting somewhere!
0
 
alokanantCommented:
do u mean to say that if there are three entries in the list box say
George
George A
George 1
George A 1

then if the combo box selections are George, A and 1, the should all the above show up in the list box or only George A 1 should come up in the list box.

hth
alok.
0
 
cybrthugAuthor Commented:
Yes, If i just wanted to choose George in the Names combo box, it will list all the Georges, no matter what group they belong to.
0
 
cybrthugAuthor Commented:
The only things that I need to show up are whats currently selected in the combo boxes. If I select George in the Names combo box only then all that should be seen is

George

If I select George from the Names combo box and Group A in the Group combo box then

George A
will be the only thing seen.
Basically a very specific filter is what im looking for.

Now if I choose "only" a Group letter like A, and leave the other combo boxes blank then I want it to pull up all Names that are related to Group A.

Hope this isn't too confusing :)

0
 
alokanantCommented:
if u will remove items from the list box then when the user changes the selection in combo box (say he makes a selection second time), there will be no list available for filtering.
do u have the list which populates the list box in an array or as some column of a table?

alok
0
 
cybrthugAuthor Commented:
The listbox is being populated from an array.  

For I = 0 To objGrid.GetRowCount - 1
           List1.AddItem objGrid.GetField(Name, I) & " " & objGrid.GetField(Group, I) & " " & objGrid.GetField(Number, I)
 Next I

This will pull the info in looking like
George A 1
0
 
alokanantCommented:
one more thing, if the user does not select an item in the combo box having names, but selects an item in other combo boxes, then what should happen.

eg, cmb1 = " "
cmb2 = "A"
cmb3 = "1"

assume
astrItemList(0) = "George A"
astrItemList(1) = "George 1"
astrItemList(2) = "Frank A"
astrItemList(3) = "George A 1"

alok

0
 
cybrthugAuthor Commented:
In your last case if cmb1 is left blank and the other two have selections it needs to be able to find all the Names that have Group A and Number 1 and list them.

George A 1
Frank A 1
Tom A 1
Harry A 1

Think we are movin in the right direction :)
0
 
cybrthugAuthor Commented:
I would suppose the blank entry in each combo box could be changed to "ANY"
0
 
cybrthugAuthor Commented:
Adjusted points to 100
0
 
cybrthugAuthor Commented:
Lastly to clarify my intentions, if cmb1 has been set to "ANY" and cmb2 is set to "A" and cmb3 is set to "1" then it will pull up all Names belonging to Group A and Number 1.

George A 1
Tom A 1
Frank A 1

If cmb1 and cmb2 were set to "ANY" and cmb3 were set to Group "1" then it would pull up all Names and Groups belonging to Number 1.

George A 1
George B 1
Frank A 1
Frank B 1
Tom C 1

if cmb1 and cmb3 were set to "ANY" and cmb2 were set to Group B then it would pull up.

George B 1
George B 2
Frank B 1
Frank B 2
Tom B 1
Tom B 2

If all boxes are set to "ANY" then all entries should be listed.
0
 
alokanantCommented:
i think here is what u need.
instead of populating the list box u can populate an array named astrItemList in exactly the same way as u r populating the list box.

in the form load for testing purpose i have written the foll. code (besides the code for populating the combo boxes boxes) -
 astrItemList(0) = "George A 1"
    astrItemList(1) = "Frank B 1"
    astrItemList(2) = "George A 2"
    astrItemList(3) = "Frank B 2"
    astrItemList(4) = "George B 1"
    astrItemList(5) = "Frank A 1"
    astrItemList(6) = "George"
    astrItemList(7) = " "
    cmb1 = " "
    cmb2 = " "
    cmb3 = " "
    lst = " "

now the modified function which is to be called from the lost focus of the combo box is as follows -
Private Sub FilterList()
    Dim inti As Integer
    Dim strSearch As String
   
    lst.Clear
    strSearch = IIf(Trim(cmb1) = Space(0), "*", cmb1) & " " & IIf(Trim(cmb2) = Space(0), "*", cmb2) & " " & IIf(Trim(cmb3) = Space(0), "*", cmb3)
    For inti = 0 To 8
        If astrItemList(inti) Like strSearch Then
            lst.AddItem astrItemList(inti)
        End If
    Next inti
   
End Sub

alok
0
 
alokanantCommented:
sorry, i saw ur comments after i put the solution.
i think if "Any" is put in the combo box, u can replace strSearch statement by -
    strSearch = IIf(Trim(cmb1) = "Any", "*", cmb1) & " " & IIf(Trim(cmb2) = "Any", "*", cmb2) & " " & IIf(Trim(cmb3) = "Any", "*", cmb3)
   
i think it is ensured that none of your names shall be "Any"

Cheers
alok
0
 
cybrthugAuthor Commented:
Don't think your last array is going to work for me since i am pulling in the names from another source into the listbox. From there I just want the combo boxes to filter what I see/get.
0
 
cybrthugAuthor Commented:
That worked beautifully! Thank you very much!
0
All Courses

From novice to tech pro — start learning today.