Multiple MultiSelect Listbox Help

[Jaxson]

Hey guys,

I have to create a search that returns the results based on what the user selects in 4 listboxes. They should be able to select more than item from each listbox. From some references on this site, I was able to modify different vba codes so that access can filter my database based on what the user selects from each list box. However the results don't make sense when the user selects more than one item from each listbox. Can you please help? The fields in my database is 1-Level, 2-Level, 3-Level and 4 Level.

Here is the code.

Private Sub Filter_Click()

' Update the record source
Form_DatabaseSrch_sub.RecordSource = "SELECT * FROM Database" & BuildFilter

End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varLevel As Variant
Dim varItem As Variant

varWhere = Null ' Main filter
varLevel = Null ' Subfilter used for colors

' Check for items selected in multiselect lists
For Each varItem In Me.list1Level.ItemsSelected
varLevel = varLevel & "[1-Level] = """ & _
Me.list1Level.ItemData(varItem) & """ OR "
Next

' Test to see if we have subfilter
If IsNull(varLevel) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varLevel, 4) = " OR " Then
varLevel = Left(varLevel, Len(varLevel) - 4)
varLevel = varLevel & " AND "
End If
End If

For Each varItem In Me.list2Level.ItemsSelected
varLevel = varLevel & "[2-Level] = """ & _
Me.list2Level.ItemData(varItem) & """ OR "
Next

' Test to see if we have subfilter
If IsNull(varLevel) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varLevel, 4) = " OR " Then
varLevel = Left(varLevel, Len(varLevel) - 4)
varLevel = varLevel & " AND "
End If
End If

For Each varItem In Me.list3Level.ItemsSelected
varLevel = varLevel & "[3-Level] = """ & _
Me.list3Level.ItemData(varItem) & """ OR "
Next

' Test to see if we have subfilter
If IsNull(varLevel) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varLevel, 4) = " OR " Then
varLevel = Left(varLevel, Len(varLevel) - 4)
varLevel = varLevel & " AND "
End If
End If

For Each varItem In Me.list4level.ItemsSelected
varLevel = varLevel & "[4-Level] = """ & _
Me.list4level.ItemData(varItem) & """ OR "
Next

' Test to see if we have subfilter
If IsNull(varLevel) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varLevel, 4) = " OR " Then
varLevel = Left(varLevel, Len(varLevel) - 4)
varLevel = varLevel & " AND "
End If
End If


If Right(varLevel, 5) = " AND " Then
varLevel = Left(varLevel, Len(varLevel) - 5)
End If

' Add some parentheses around the subfilter
varWhere = varWhere & "( " & varLevel & " )"

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""

Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function

 

[pbaldy]

Have you put

Debug.Print varWhere

at the end to examine the results of the process in the Immediate window? Half the time that will lead you to the answer.

 

[Jaxson]

No I have not. I have never used this function.

 

[pbaldy]

I assume you are trying it now and will post the result if you still have trouble?

 

[Jaxson]

It worked...thanks. That debug.print function helped me to figure out a logic error that I had...I will post my search up for others to use soon.

 

[pbaldy]

Excellent; you'll find that to be a vital tool in your toolbox.