Re: Code for List Boxes

Your much simpler code is flawless. Works like a champ. You are an MVP in
my book. Moving forward, I wanted the values in the second list box
displayed to the selection(s) in the first box. I have had success with
combo boxes in doing this, but seem to fail with list boxes and multiple
selections. In combo box I used :
Me.Controls("Model").RowSource = "SELECT DISTINCT
[qryModels].[Model],[qryModels].[Model] FROM [qryModels] WHERE
((([qryModels].[Make])=[forms]![frmModelSearchTEST].[Make])) ORDER BY
[Model]; " This did not work with list boxes. Where might I go from here?
Again, Thank you.


"Douglas J. Steele" wrote:

Your code could be a lot simpler!

There's no need for the SelectListBox function: the ItemsSelected collection
has a Count property that'll give you that. In the WhereString function, why
bother adding " AND " to the end of strWhere and strWhere1 if the next thing
you do is strip it off? Keep it on both strings, and then if anything was
written to either string, you can strip it off at the end. As well, include
the keyword WHERE in what you return from WhereString, and it'll make
cmdSearch_Click simpler

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 Me.lstMake.ItemsSelected.Count > 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) - 2) & ") AND "
End If

If Me.lstModel.ItemsSelected.Count > 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) - 2) & ") AND "
End If

WhereString = strWhere & strWhere1
If Len(WhereString) > 0 Then
WhereString = " WHERE " & Left(WhereString, Len(WhereString) - 5)
End If

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 = "SELECT * FROM " & strRecordSource & _
WhereString()

Me.RecordSource = strSQL

Call SetVisibility(True)

End Sub

That having been said, exactly what's returned by the WhereString function
when you've selected values from the two lists?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Stu" <Stu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E8581DED-1615-4D8A-BC90-54B680021084@xxxxxxxxxxxxxxxx
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



.

Loading