List Box Coding Problem

[Guest]

I have a bound form that I am trying to filter records based on selection of
single or multiple rows from single or multiple list boxes. The code works
well with one box only. When I added code for the second list box, no
different results displayed. It obviously doesn't work. Can anyone assist
me where I went wrong and steer me in the right direction?

Private Function SelectListBox(xlstListBox As ListBox) As Long
' *** THIS FUNCTION RETURNS THE NUMBER OF ITEMS SELECTED IN A LISTBOX.

Dim xlngSelected As Long
Dim xvarSelected As Variant

On Error Resume Next

xlngSelected = 0

For Each xvarSelected In xlstListBox.ItemsSelected
xlngSelected = xlngSelected + 1
Next xvarSelected

SelectListBox = xlngSelected
Err.Clear
End Function

Private Function WhereString() As String
Dim strWhere As String
Dim strWhere1 As String
Dim varItem As Variant

On Error Resume Next

strWhere = ""
' ... build "Make" criterion expression
If SelectListBox(Me.lstMake) <> 0 Then
strWhere = strWhere & "Make IN ("
For Each varItem In Me.lstMake.ItemsSelected
strWhere = strWhere & "'" & _
Me.lstMake.ItemData(varItem) & "', "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - Len(", ")) & ") And "
End If

' Strip off the trailing " And " text string
If Len(strWhere) > 0 Then strWhere = Left(strWhere, Len(strWhere) - _
Len(" And "))

' ... build "Model" criterion expression
If SelectListBox(Me.lstModel) <> 0 Then
strWhere1 = strWhere1 & "Model IN ("
For Each varItem In Me.lstModel.ItemsSelected
strWhere1 = strWhere1 & "'" & _
Me.lstModel.ItemData(varItem) & "', "
Next varItem
strWhere1 = Left(strWhere1, Len(strWhere1) - Len(", ")) & ") And "
End If

' Strip off the trailing " And " text string
If Len(strWhere1) > 0 Then strWhere1 = Left(strWhere1, Len(strWhere1) - _
Len(" And "))

WhereString = strWhere
If Len(WhereString) > 0 And Len(strWhere1) > 0 Then
strWhere = strWhere & " AND " & strWhere1
Else
strWhere = strWhere & strWhere1
End If

Exit Function
End Function

Private Sub cmdSearch_Click()
Dim strSQL As String
Dim strRecordSource As String
On Error Resume Next

strRecordSource = "qryModelSearchTEST"

' move focus to clear button
Me.cmdClear.SetFocus

' build sql string for form's RecordSource
strSQL = WhereString
strSQL = "SELECT * FROM " & strRecordSource & _
IIf(strSQL = "", "", " WHERE ") & strSQL & ";"

Me.RecordSource = ""
Me.RecordSource = strSQL

Call SetVisibility(True)

End Sub

Any help is appreciated. Thanks

 

[George Nicholson]

1)
SelectListBox() is unnecessary.
listbox.ItemsSelected.Count

2)
As written, WhereString is totally ignorant of strWhere1, so:

If Len(strWhere) > 0 And Len(strWhere1) > 0 Then
WhereString = strWhere & " AND " & strWhere1
Else
WhereString = strWhere & strWhere1
End If


3)
If you still have problems:

In the Vbe, put a breakpoint on
Me.RecordSource = strSQL
Run your form/code. When the breakpoint is reached, type
?strSQL
in the Immediate window (VBE: View).

What do you get? Do you see any obvious syntax issues? (post results if you
still have problems).