Re: Code for List Boxes

You put the closing parenthesis in the wrong place:

strWhere = strWhere & "'" & Me.Make.ItemData(varSelected) & "', "

Sorry about that.

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


"Stu" <Stu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F42024D0-1744-42CC-A898-2453D5CB9122@xxxxxxxxxxxxxxxx
I cut and pasted your lines of code to the After Update Event of the
lstMake
list box. The field Make is the only field in the Row Source of the
lstMake
list box when looking at the grid. I compiled and got the message:
Compile
Error: Method or data member not found. .ItemsSelected is highlighted at
this point. I also had to add a closing parentheses to strWhere =
strWhere &
"'" & Me.Make.ItemData(varSelected & "', ") to make the red highlight go
away. Any further ideas.

"Douglas J. Steele" wrote:

You sure you're working with the list box, and not some other control?

Even if the list box doesn't allow Multiselect, the ItemsSelected
collection
is still populated and accessible.

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


"Stu" <Stu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C9C95FB6-332E-44A1-900E-4C132BA47448@xxxxxxxxxxxxxxxx
I finally got my code to work with multiple list boxes thanks to your
efforts. I appreciate your extended efforts to help the not so
talented
user. I tried using your logic example for filtering selections in a
second
list box based on selection in first box. It retrieved an error on the
.ItemsSelected saying Method or Data property not available and the
strWhere
statement was entirely red. Is ItemsSelected not available in this
case?
Any help appreciated. Thanks again.

"Douglas J. Steele" wrote:

What you've got will work for list boxes where the Multiselect
property
is
set to None. When you've enabled Multiselect, though, any reference to
the
list box simply returns Null, even when only a single entry has been
selected.

You're going to have to use the same sort of logic as you've got
elsewhere:

Dim strWhere As String
Dim varSelected As Variant

If Me.Make.ItemsSelected.Count > 0 Then
strWhere = "WHERE [Make] IN ("
For Each varSelected In Me.Make.ItemsSelected
strWhere = strWhere & "'" & Me.Make.ItemData(varSelected & "', "
Next varSelected
strWhere = Left(strWhere, Len(strWhere) - 2) & ") "
End If

Me.Controls("Model").RowSource = "SELECT DISTINCT " & _
"[Model] FROM [qryModels] " & strWhere & _
"ORDER BY [Model]"

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


"Stu" <Stu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:11FBF1D5-C2C1-4D6B-AF66-91BDEFBF8A1E@xxxxxxxxxxxxxxxx
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











.