Multi Select controls another List Box

[FrankTimJr]

I need some help on creating a list box that when multiple entries are
selected, another List box on the same form is "filtered".

I've read up on several other posts regarding the same subject and I'm just
not getting it. I've also read the write up on the following link:
www.mvps.org/access/forms/frm00007.htm
The light-bulb has not gone off yet.

The main form (frm_Funnel) displays the sales projects in the List Box
(lst_funnel). There are controls on the form that will filter lst_funnel by
Manager and Sales Rep. I would also like to filter it down to what is being
sold. I have a list box that shows all of our Products (lst_products) and I
would like to be able to select multiple Products within lst_products and
have lst_funnel only show the projects that contain the Products selected.

I see somehow I have to code the _Click( ) event within the lst_products
list box to refer to a hidden Text Box that will house the criteria for
lst_funnel. Simply copying and pasting the code provided within these posts
has not helped me at all because I need to understand what the code actually
means so I can duplicate it.

The Product List box is named "lst_products"; the projects list box is named
"lst_funnel". I can easily get this to work with just one selection, but
when I need to select more than one, it becomes very confusing.

Any help would be most appreciated.
Thank you,
Frank

 

[Douglas J. Steele]

If you didn't have a multi-select list box, your AfterUpdate event would be
something like:

Private Sub Listbox1_AfterUpdate()

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id = " & Me.Listbox1

End Sub

Since you've got a multiselect listbox, though, you need to create a WHERE
clause that includes all of the values that have been selected in the first
list box.

The sample code you've cited will generate something like:

WHERE [EmpId] = 5 OR [EmpId] = 8 OR [EmpId] = 15

To be honest, I think it's better to use:

WHERE [EmpId] IN (5, 8, 15)

since that will generate a short SQL statement, which can make a difference
if you've got a lot of selections.

Private Sub Listbox1_AfterUpdate()
Dim strWhere As String
Dim varItem As Variant

' Get all of the selected items
For Each varItem in Me.Listbox1.ItemsSelected
strWhere = strWhere & Me.Listbox1.ItemData(varItem) & ", "
Next varItem

' Remove the superfluous final ", "
If Len(strWhere) > 2 Then
strWhere = Left$(strWhere, Len(strWhere) - 2)
End If

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id IN (" & strWhere & ")"

End Sub

 

[FrankTimJr]

So do I need to create a hidden text box?

Tell me if this is the right code I would use:
(Lst_Products is where the multi selection would occur)
(Lst_Funnel is where the parameters would be applied)

Private Sub Lst_Products_AfterUpdate()

Dim strWhere As String
Dim varItem As Variant

' Get all of the selected items
For Each varItem in Me.Lst_Products.ItemsSelected
strWhere = strWhere & Me.Lst_Products.ItemData(varItem) & ", "
Next varItem

' Remove the superfluous final ", "
If Len(strWhere) > 2 Then
strWhere = Left$(strWhere, Len(strWhere) - 2)
End If

Me.Lst_Funnel.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id IN (" & strWhere & ")"

End Sub

If you didn't have a multi-select list box, your AfterUpdate event would
be something like:

Private Sub Listbox1_AfterUpdate()

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id = " & Me.Listbox1

End Sub

Since you've got a multiselect listbox, though, you need to create a WHERE
clause that includes all of the values that have been selected in the
first list box.

The sample code you've cited will generate something like:

WHERE [EmpId] = 5 OR [EmpId] = 8 OR [EmpId] = 15

To be honest, I think it's better to use:

WHERE [EmpId] IN (5, 8, 15)

since that will generate a short SQL statement, which can make a
difference if you've got a lot of selections.

Private Sub Listbox1_AfterUpdate()
Dim strWhere As String
Dim varItem As Variant

' Get all of the selected items
For Each varItem in Me.Listbox1.ItemsSelected
strWhere = strWhere & Me.Listbox1.ItemData(varItem) & ", "
Next varItem

' Remove the superfluous final ", "
If Len(strWhere) > 2 Then
strWhere = Left$(strWhere, Len(strWhere) - 2)
End If

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id IN (" & strWhere & ")"

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I need some help on creating a list box that when multiple entries are
selected, another List box on the same form is "filtered".

I've read up on several other posts regarding the same subject and I'm
just not getting it. I've also read the write up on the following link:
www.mvps.org/access/forms/frm00007.htm
The light-bulb has not gone off yet.

The main form (frm_Funnel) displays the sales projects in the List Box
(lst_funnel). There are controls on the form that will filter lst_funnel
by Manager and Sales Rep. I would also like to filter it down to what is
being sold. I have a list box that shows all of our Products
(lst_products) and I would like to be able to select multiple Products
within lst_products and have lst_funnel only show the projects that
contain the Products selected.

I see somehow I have to code the _Click( ) event within the lst_products
list box to refer to a hidden Text Box that will house the criteria for
lst_funnel. Simply copying and pasting the code provided within these
posts has not helped me at all because I need to understand what the code
actually means so I can duplicate it.

The Product List box is named "lst_products"; the projects list box is
named "lst_funnel". I can easily get this to work with just one
selection, but when I need to select more than one, it becomes very
confusing.

Any help would be most appreciated.
Thank you,
Frank

 

[FrankTimJr]

I tried the code you provided and just replaced ListBox1 and ListBox2 with
Lst_Products and Lst_Funnel respectively. When I open the form and select a
Product, the Lst_Funnel goes blank, not even column headings are displayed.

Do I need to replace other syntax? For example, what are you referring to
when you have "SELECT Field1, Field2..." and "MyTable"? Is that part of the
syntax or do I need to replace those with items from my database?

If you didn't have a multi-select list box, your AfterUpdate event would
be something like:

Private Sub Listbox1_AfterUpdate()

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id = " & Me.Listbox1

End Sub

Since you've got a multiselect listbox, though, you need to create a WHERE
clause that includes all of the values that have been selected in the
first list box.

The sample code you've cited will generate something like:

WHERE [EmpId] = 5 OR [EmpId] = 8 OR [EmpId] = 15

To be honest, I think it's better to use:

WHERE [EmpId] IN (5, 8, 15)

since that will generate a short SQL statement, which can make a
difference if you've got a lot of selections.

Private Sub Listbox1_AfterUpdate()
Dim strWhere As String
Dim varItem As Variant

' Get all of the selected items
For Each varItem in Me.Listbox1.ItemsSelected
strWhere = strWhere & Me.Listbox1.ItemData(varItem) & ", "
Next varItem

' Remove the superfluous final ", "
If Len(strWhere) > 2 Then
strWhere = Left$(strWhere, Len(strWhere) - 2)
End If

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id IN (" & strWhere & ")"

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I need some help on creating a list box that when multiple entries are
selected, another List box on the same form is "filtered".

I've read up on several other posts regarding the same subject and I'm
just not getting it. I've also read the write up on the following link:
www.mvps.org/access/forms/frm00007.htm
The light-bulb has not gone off yet.

The main form (frm_Funnel) displays the sales projects in the List Box
(lst_funnel). There are controls on the form that will filter lst_funnel
by Manager and Sales Rep. I would also like to filter it down to what is
being sold. I have a list box that shows all of our Products
(lst_products) and I would like to be able to select multiple Products
within lst_products and have lst_funnel only show the projects that
contain the Products selected.

I see somehow I have to code the _Click( ) event within the lst_products
list box to refer to a hidden Text Box that will house the criteria for
lst_funnel. Simply copying and pasting the code provided within these
posts has not helped me at all because I need to understand what the code
actually means so I can duplicate it.

The Product List box is named "lst_products"; the projects list box is
named "lst_funnel". I can easily get this to work with just one
selection, but when I need to select more than one, it becomes very
confusing.

Any help would be most appreciated.
Thank you,
Frank

 

[Douglas J Steele]

You need to replace Field1, Field2, Id and MyTable with the actual items
from your database.

(To be honest, if you didn't change that code, I would have expected you to
get a bunch of pop-ups asking for values and/or error messages that it
couldn't find those items)

As to your other question, with my approach, there is no need for the hidden
text field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I tried the code you provided and just replaced ListBox1 and ListBox2 with
Lst_Products and Lst_Funnel respectively. When I open the form and select a
Product, the Lst_Funnel goes blank, not even column headings are displayed.

Do I need to replace other syntax? For example, what are you referring to
when you have "SELECT Field1, Field2..." and "MyTable"? Is that part of the
syntax or do I need to replace those with items from my database?

If you didn't have a multi-select list box, your AfterUpdate event would
be something like:

Private Sub Listbox1_AfterUpdate()

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id = " & Me.Listbox1

End Sub

Since you've got a multiselect listbox, though, you need to create a WHERE
clause that includes all of the values that have been selected in the
first list box.

The sample code you've cited will generate something like:

WHERE [EmpId] = 5 OR [EmpId] = 8 OR [EmpId] = 15

To be honest, I think it's better to use:

WHERE [EmpId] IN (5, 8, 15)

since that will generate a short SQL statement, which can make a
difference if you've got a lot of selections.

Private Sub Listbox1_AfterUpdate()
Dim strWhere As String
Dim varItem As Variant

' Get all of the selected items
For Each varItem in Me.Listbox1.ItemsSelected
strWhere = strWhere & Me.Listbox1.ItemData(varItem) & ", "
Next varItem

' Remove the superfluous final ", "
If Len(strWhere) > 2 Then
strWhere = Left$(strWhere, Len(strWhere) - 2)
End If

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id IN (" & strWhere & ")"

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I need some help on creating a list box that when multiple entries are
selected, another List box on the same form is "filtered".

I've read up on several other posts regarding the same subject and I'm
just not getting it. I've also read the write up on the following link:
www.mvps.org/access/forms/frm00007.htm
The light-bulb has not gone off yet.

The main form (frm_Funnel) displays the sales projects in the List Box
(lst_funnel). There are controls on the form that will filter lst_funnel
by Manager and Sales Rep. I would also like to filter it down to what is
being sold. I have a list box that shows all of our Products
(lst_products) and I would like to be able to select multiple Products
within lst_products and have lst_funnel only show the projects that
contain the Products selected.

I see somehow I have to code the _Click( ) event within the lst_products
list box to refer to a hidden Text Box that will house the criteria for
lst_funnel. Simply copying and pasting the code provided within these
posts has not helped me at all because I need to understand what the code
actually means so I can duplicate it.

The Product List box is named "lst_products"; the projects list box is
named "lst_funnel". I can easily get this to work with just one
selection, but when I need to select more than one, it becomes very
confusing.

Any help would be most appreciated.
Thank you,
Frank

 

[FrankTimJr]

Ok, I replaced them and I'm still getting the same result. The Lst_Funnel
list box goes completly blank.

Here is what the code looks like now (in the SELECT *..." statement, I also
tried putting in the field names, but that didn't work either.
qry_West_Funnel_Details is the source for Lst_Funnel and there is a field in
there called Product which is what I'm trying to filter on.):

Private Sub Lst_Products_AfterUpdate()
Dim strWhere As String

Dim varItem As Variant


For Each varItem In Me.Lst_Products.ItemsSelected

strWhere = strWhere & Me.Lst_Products.ItemData(varItem)

Next varItem

If Len(strWhere) > 2 Then

strWhere = Left$(strWhere, Len(strWhere) - 2)

End If


Me.Lst_funnel.RowSource = "SELECT *" & "FROM tbl_West_Funnel_Details WHERE
Product IN (" & strWhere & ")"


End Sub

You need to replace Field1, Field2, Id and MyTable with the actual items
from your database.

(To be honest, if you didn't change that code, I would have expected you
to
get a bunch of pop-ups asking for values and/or error messages that it
couldn't find those items)

As to your other question, with my approach, there is no need for the
hidden
text field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I tried the code you provided and just replaced ListBox1 and ListBox2
with
Lst_Products and Lst_Funnel respectively. When I open the form and
select a
Product, the Lst_Funnel goes blank, not even column headings are displayed.

Do I need to replace other syntax? For example, what are you referring
to
when you have "SELECT Field1, Field2..." and "MyTable"? Is that part of the
syntax or do I need to replace those with items from my database?

If you didn't have a multi-select list box, your AfterUpdate event
would
be something like:

Private Sub Listbox1_AfterUpdate()

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id = " & Me.Listbox1

End Sub

Since you've got a multiselect listbox, though, you need to create a WHERE
clause that includes all of the values that have been selected in the
first list box.

The sample code you've cited will generate something like:

WHERE [EmpId] = 5 OR [EmpId] = 8 OR [EmpId] = 15

To be honest, I think it's better to use:

WHERE [EmpId] IN (5, 8, 15)

since that will generate a short SQL statement, which can make a
difference if you've got a lot of selections.

Private Sub Listbox1_AfterUpdate()
Dim strWhere As String
Dim varItem As Variant

' Get all of the selected items
For Each varItem in Me.Listbox1.ItemsSelected
strWhere = strWhere & Me.Listbox1.ItemData(varItem) & ", "
Next varItem

' Remove the superfluous final ", "
If Len(strWhere) > 2 Then
strWhere = Left$(strWhere, Len(strWhere) - 2)
End If

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id IN (" & strWhere & ")"

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I need some help on creating a list box that when multiple entries are
selected, another List box on the same form is "filtered".

I've read up on several other posts regarding the same subject and I'm
just not getting it. I've also read the write up on the following link:
www.mvps.org/access/forms/frm00007.htm
The light-bulb has not gone off yet.

The main form (frm_Funnel) displays the sales projects in the List Box
(lst_funnel). There are controls on the form that will filter lst_funnel
by Manager and Sales Rep. I would also like to filter it down to what is
being sold. I have a list box that shows all of our Products
(lst_products) and I would like to be able to select multiple Products
within lst_products and have lst_funnel only show the projects that
contain the Products selected.

I see somehow I have to code the _Click( ) event within the lst_products
list box to refer to a hidden Text Box that will house the criteria
for
lst_funnel. Simply copying and pasting the code provided within these
posts has not helped me at all because I need to understand what the code
actually means so I can duplicate it.

The Product List box is named "lst_products"; the projects list box is
named "lst_funnel". I can easily get this to work with just one
selection, but when I need to select more than one, it becomes very
confusing.

Any help would be most appreciated.
Thank you,
Frank

 

[Douglas J Steele]

You need a space between the asterisk and the FROM keyword:

Me.Lst_funnel.RowSource = "SELECT * FROM tbl_West_Funnel_Details WHERE
Product IN (" & strWhere & ")"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok, I replaced them and I'm still getting the same result. The Lst_Funnel
list box goes completly blank.

Here is what the code looks like now (in the SELECT *..." statement, I also
tried putting in the field names, but that didn't work either.
qry_West_Funnel_Details is the source for Lst_Funnel and there is a field in
there called Product which is what I'm trying to filter on.):

Private Sub Lst_Products_AfterUpdate()
Dim strWhere As String

Dim varItem As Variant


For Each varItem In Me.Lst_Products.ItemsSelected

strWhere = strWhere & Me.Lst_Products.ItemData(varItem)

Next varItem

If Len(strWhere) > 2 Then

strWhere = Left$(strWhere, Len(strWhere) - 2)

End If


Me.Lst_funnel.RowSource = "SELECT *" & "FROM tbl_West_Funnel_Details WHERE
Product IN (" & strWhere & ")"


End Sub

You need to replace Field1, Field2, Id and MyTable with the actual items
from your database.

(To be honest, if you didn't change that code, I would have expected you
to
get a bunch of pop-ups asking for values and/or error messages that it
couldn't find those items)

As to your other question, with my approach, there is no need for the
hidden
text field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I tried the code you provided and just replaced ListBox1 and ListBox2
with
Lst_Products and Lst_Funnel respectively. When I open the form and
select a
Product, the Lst_Funnel goes blank, not even column headings are displayed.

Do I need to replace other syntax? For example, what are you referring
to
when you have "SELECT Field1, Field2..." and "MyTable"? Is that part

of
the

syntax or do I need to replace those with items from my database?

If you didn't have a multi-select list box, your AfterUpdate event
would
be something like:

Private Sub Listbox1_AfterUpdate()

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id = " & Me.Listbox1

End Sub

Since you've got a multiselect listbox, though, you need to create a WHERE
clause that includes all of the values that have been selected in the
first list box.

The sample code you've cited will generate something like:

WHERE [EmpId] = 5 OR [EmpId] = 8 OR [EmpId] = 15

To be honest, I think it's better to use:

WHERE [EmpId] IN (5, 8, 15)

since that will generate a short SQL statement, which can make a
difference if you've got a lot of selections.

Private Sub Listbox1_AfterUpdate()
Dim strWhere As String
Dim varItem As Variant

' Get all of the selected items
For Each varItem in Me.Listbox1.ItemsSelected
strWhere = strWhere & Me.Listbox1.ItemData(varItem) & ", "
Next varItem

' Remove the superfluous final ", "
If Len(strWhere) > 2 Then
strWhere = Left$(strWhere, Len(strWhere) - 2)
End If

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id IN (" & strWhere & ")"

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I need some help on creating a list box that when multiple entries are
selected, another List box on the same form is "filtered".

I've read up on several other posts regarding the same subject and I'm
just not getting it. I've also read the write up on the following link:
www.mvps.org/access/forms/frm00007.htm
The light-bulb has not gone off yet.

The main form (frm_Funnel) displays the sales projects in the List Box
(lst_funnel). There are controls on the form that will filter lst_funnel
by Manager and Sales Rep. I would also like to filter it down to

what
is

being sold. I have a list box that shows all of our Products
(lst_products) and I would like to be able to select multiple Products
within lst_products and have lst_funnel only show the projects that
contain the Products selected.

I see somehow I have to code the _Click( ) event within the lst_products
list box to refer to a hidden Text Box that will house the criteria
for
lst_funnel. Simply copying and pasting the code provided within these
posts has not helped me at all because I need to understand what the code
actually means so I can duplicate it.

The Product List box is named "lst_products"; the projects list box is
named "lst_funnel". I can easily get this to work with just one
selection, but when I need to select more than one, it becomes very
confusing.

Any help would be most appreciated.
Thank you,
Frank

 

[FrankTimJr]

There must be some other issue. Lst_funnel still goes blank when I select a
Product from the Lst_Products list box. (even the column headings in
Lst_funnel disappear). The source of Lst_funnel is not
tbl_West_Funnel_Details. It is a query, so I changed

Me.Lst_funnel.RowSource = "SELECT * FROM tbl_West_Funnel_Details WHERE
Product IN (" & strWhere & ")"

to

Me.Lst_funnel.RowSource = "SELECT * FROM qry_West_Funnel WHERE Product IN ("
& strWhere & ")"

The Multi Select setting of Lst_Products is Extended, but I switched it to
Simple and the result was the same. And I'm not getting any error messages.

You need a space between the asterisk and the FROM keyword:

Me.Lst_funnel.RowSource = "SELECT * FROM tbl_West_Funnel_Details WHERE
Product IN (" & strWhere & ")"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok, I replaced them and I'm still getting the same result. The
Lst_Funnel
list box goes completly blank.

Here is what the code looks like now (in the SELECT *..." statement, I also
tried putting in the field names, but that didn't work either.
qry_West_Funnel_Details is the source for Lst_Funnel and there is a field in
there called Product which is what I'm trying to filter on.):

Private Sub Lst_Products_AfterUpdate()
Dim strWhere As String

Dim varItem As Variant


For Each varItem In Me.Lst_Products.ItemsSelected

strWhere = strWhere & Me.Lst_Products.ItemData(varItem)

Next varItem

If Len(strWhere) > 2 Then

strWhere = Left$(strWhere, Len(strWhere) - 2)

End If


Me.Lst_funnel.RowSource = "SELECT *" & "FROM tbl_West_Funnel_Details
WHERE
Product IN (" & strWhere & ")"


End Sub

You need to replace Field1, Field2, Id and MyTable with the actual
items
from your database.

(To be honest, if you didn't change that code, I would have expected
you
to
get a bunch of pop-ups asking for values and/or error messages that it
couldn't find those items)

As to your other question, with my approach, there is no need for the
hidden
text field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried the code you provided and just replaced ListBox1 and ListBox2
with
Lst_Products and Lst_Funnel respectively. When I open the form and
select
a
Product, the Lst_Funnel goes blank, not even column headings are
displayed.

Do I need to replace other syntax? For example, what are you
referring
to
when you have "SELECT Field1, Field2..." and "MyTable"? Is that part of
the
syntax or do I need to replace those with items from my database?

If you didn't have a multi-select list box, your AfterUpdate event
would
be something like:

Private Sub Listbox1_AfterUpdate()

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id = " & Me.Listbox1

End Sub

Since you've got a multiselect listbox, though, you need to create a
WHERE
clause that includes all of the values that have been selected in
the
first list box.

The sample code you've cited will generate something like:

WHERE [EmpId] = 5 OR [EmpId] = 8 OR [EmpId] = 15

To be honest, I think it's better to use:

WHERE [EmpId] IN (5, 8, 15)

since that will generate a short SQL statement, which can make a
difference if you've got a lot of selections.

Private Sub Listbox1_AfterUpdate()
Dim strWhere As String
Dim varItem As Variant

' Get all of the selected items
For Each varItem in Me.Listbox1.ItemsSelected
strWhere = strWhere & Me.Listbox1.ItemData(varItem) & ", "
Next varItem

' Remove the superfluous final ", "
If Len(strWhere) > 2 Then
strWhere = Left$(strWhere, Len(strWhere) - 2)
End If

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id IN (" & strWhere & ")"

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I need some help on creating a list box that when multiple entries are
selected, another List box on the same form is "filtered".

I've read up on several other posts regarding the same subject and I'm
just not getting it. I've also read the write up on the following
link:
www.mvps.org/access/forms/frm00007.htm
The light-bulb has not gone off yet.

The main form (frm_Funnel) displays the sales projects in the List Box
(lst_funnel). There are controls on the form that will filter
lst_funnel
by Manager and Sales Rep. I would also like to filter it down to what
is
being sold. I have a list box that shows all of our Products
(lst_products) and I would like to be able to select multiple Products
within lst_products and have lst_funnel only show the projects that
contain the Products selected.

I see somehow I have to code the _Click( ) event within the
lst_products
list box to refer to a hidden Text Box that will house the criteria
for
lst_funnel. Simply copying and pasting the code provided within these
posts has not helped me at all because I need to understand what
the
code
actually means so I can duplicate it.

The Product List box is named "lst_products"; the projects list box is
named "lst_funnel". I can easily get this to work with just one
selection, but when I need to select more than one, it becomes very
confusing.

Any help would be most appreciated.
Thank you,
Frank

 

[Douglas J. Steele]

What does the value of strWhere look like?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

There must be some other issue. Lst_funnel still goes blank when I select
a Product from the Lst_Products list box. (even the column headings in
Lst_funnel disappear). The source of Lst_funnel is not
tbl_West_Funnel_Details. It is a query, so I changed

Me.Lst_funnel.RowSource = "SELECT * FROM tbl_West_Funnel_Details WHERE
Product IN (" & strWhere & ")"

to

Me.Lst_funnel.RowSource = "SELECT * FROM qry_West_Funnel WHERE Product IN
(" & strWhere & ")"

The Multi Select setting of Lst_Products is Extended, but I switched it to
Simple and the result was the same. And I'm not getting any error
messages.

You need a space between the asterisk and the FROM keyword:

Me.Lst_funnel.RowSource = "SELECT * FROM tbl_West_Funnel_Details WHERE
Product IN (" & strWhere & ")"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok, I replaced them and I'm still getting the same result. The
Lst_Funnel
list box goes completly blank.

Here is what the code looks like now (in the SELECT *..." statement, I also
tried putting in the field names, but that didn't work either.
qry_West_Funnel_Details is the source for Lst_Funnel and there is a
field in
there called Product which is what I'm trying to filter on.):

Private Sub Lst_Products_AfterUpdate()
Dim strWhere As String

Dim varItem As Variant


For Each varItem In Me.Lst_Products.ItemsSelected

strWhere = strWhere & Me.Lst_Products.ItemData(varItem)

Next varItem

If Len(strWhere) > 2 Then

strWhere = Left$(strWhere, Len(strWhere) - 2)

End If


Me.Lst_funnel.RowSource = "SELECT *" & "FROM tbl_West_Funnel_Details
WHERE
Product IN (" & strWhere & ")"


End Sub

You need to replace Field1, Field2, Id and MyTable with the actual
items
from your database.

(To be honest, if you didn't change that code, I would have expected
you
to
get a bunch of pop-ups asking for values and/or error messages that it
couldn't find those items)

As to your other question, with my approach, there is no need for the
hidden
text field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried the code you provided and just replaced ListBox1 and ListBox2
with
Lst_Products and Lst_Funnel respectively. When I open the form and
select
a
Product, the Lst_Funnel goes blank, not even column headings are
displayed.

Do I need to replace other syntax? For example, what are you
referring
to
when you have "SELECT Field1, Field2..." and "MyTable"? Is that part of
the
syntax or do I need to replace those with items from my database?

If you didn't have a multi-select list box, your AfterUpdate event
would
be something like:

Private Sub Listbox1_AfterUpdate()

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id = " & Me.Listbox1

End Sub

Since you've got a multiselect listbox, though, you need to create
a
WHERE
clause that includes all of the values that have been selected in
the
first list box.

The sample code you've cited will generate something like:

WHERE [EmpId] = 5 OR [EmpId] = 8 OR [EmpId] = 15

To be honest, I think it's better to use:

WHERE [EmpId] IN (5, 8, 15)

since that will generate a short SQL statement, which can make a
difference if you've got a lot of selections.

Private Sub Listbox1_AfterUpdate()
Dim strWhere As String
Dim varItem As Variant

' Get all of the selected items
For Each varItem in Me.Listbox1.ItemsSelected
strWhere = strWhere & Me.Listbox1.ItemData(varItem) & ", "
Next varItem

' Remove the superfluous final ", "
If Len(strWhere) > 2 Then
strWhere = Left$(strWhere, Len(strWhere) - 2)
End If

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id IN (" & strWhere & ")"

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I need some help on creating a list box that when multiple entries are
selected, another List box on the same form is "filtered".

I've read up on several other posts regarding the same subject and I'm
just not getting it. I've also read the write up on the following
link:
www.mvps.org/access/forms/frm00007.htm
The light-bulb has not gone off yet.

The main form (frm_Funnel) displays the sales projects in the List Box
(lst_funnel). There are controls on the form that will filter
lst_funnel
by Manager and Sales Rep. I would also like to filter it down to what
is
being sold. I have a list box that shows all of our Products
(lst_products) and I would like to be able to select multiple Products
within lst_products and have lst_funnel only show the projects
that
contain the Products selected.

I see somehow I have to code the _Click( ) event within the
lst_products
list box to refer to a hidden Text Box that will house the
criteria
for
lst_funnel. Simply copying and pasting the code provided within these
posts has not helped me at all because I need to understand what
the
code
actually means so I can duplicate it.

The Product List box is named "lst_products"; the projects list
box is
named "lst_funnel". I can easily get this to work with just one
selection, but when I need to select more than one, it becomes
very
confusing.

Any help would be most appreciated.
Thank you,
Frank

 

[FrankTimJr]

It's straight text. For example:

"Telephones and End User Devices" is one of the Products listed in the
Lst_Products list box. There are 73 in total.

The problem definitley lies with:
Me.Lst.funnel.RowSource = "SELECT * FROM qry_West_Funnel WHERE Product IN ("
& strWhere & ")"

because I removed the line to see what would happen. After I removed it, I
opened the form and when I selected a Product, nothing at all happened. But
as soon as that line is put back in, Lst_Funnel goes completely blank.

What does the value of strWhere look like?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

There must be some other issue. Lst_funnel still goes blank when I
select a Product from the Lst_Products list box. (even the column
headings in Lst_funnel disappear). The source of Lst_funnel is not
tbl_West_Funnel_Details. It is a query, so I changed

Me.Lst_funnel.RowSource = "SELECT * FROM tbl_West_Funnel_Details WHERE
Product IN (" & strWhere & ")"

to

Me.Lst_funnel.RowSource = "SELECT * FROM qry_West_Funnel WHERE Product IN
(" & strWhere & ")"

The Multi Select setting of Lst_Products is Extended, but I switched it
to Simple and the result was the same. And I'm not getting any error
messages.

You need a space between the asterisk and the FROM keyword:

Me.Lst_funnel.RowSource = "SELECT * FROM tbl_West_Funnel_Details WHERE
Product IN (" & strWhere & ")"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ok, I replaced them and I'm still getting the same result. The
Lst_Funnel
list box goes completly blank.

Here is what the code looks like now (in the SELECT *..." statement, I
also
tried putting in the field names, but that didn't work either.
qry_West_Funnel_Details is the source for Lst_Funnel and there is a
field
in
there called Product which is what I'm trying to filter on.):

Private Sub Lst_Products_AfterUpdate()
Dim strWhere As String

Dim varItem As Variant


For Each varItem In Me.Lst_Products.ItemsSelected

strWhere = strWhere & Me.Lst_Products.ItemData(varItem)

Next varItem

If Len(strWhere) > 2 Then

strWhere = Left$(strWhere, Len(strWhere) - 2)

End If


Me.Lst_funnel.RowSource = "SELECT *" & "FROM tbl_West_Funnel_Details
WHERE
Product IN (" & strWhere & ")"


End Sub

You need to replace Field1, Field2, Id and MyTable with the actual
items
from your database.

(To be honest, if you didn't change that code, I would have expected
you
to
get a bunch of pop-ups asking for values and/or error messages that
it
couldn't find those items)

As to your other question, with my approach, there is no need for the
hidden
text field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried the code you provided and just replaced ListBox1 and
ListBox2
with
Lst_Products and Lst_Funnel respectively. When I open the form and
select
a
Product, the Lst_Funnel goes blank, not even column headings are
displayed.

Do I need to replace other syntax? For example, what are you
referring
to
when you have "SELECT Field1, Field2..." and "MyTable"? Is that
part
of
the
syntax or do I need to replace those with items from my database?

message
If you didn't have a multi-select list box, your AfterUpdate event
would
be something like:

Private Sub Listbox1_AfterUpdate()

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id = " & Me.Listbox1

End Sub

Since you've got a multiselect listbox, though, you need to create
a
WHERE
clause that includes all of the values that have been selected in
the
first list box.

The sample code you've cited will generate something like:

WHERE [EmpId] = 5 OR [EmpId] = 8 OR [EmpId] = 15

To be honest, I think it's better to use:

WHERE [EmpId] IN (5, 8, 15)

since that will generate a short SQL statement, which can make a
difference if you've got a lot of selections.

Private Sub Listbox1_AfterUpdate()
Dim strWhere As String
Dim varItem As Variant

' Get all of the selected items
For Each varItem in Me.Listbox1.ItemsSelected
strWhere = strWhere & Me.Listbox1.ItemData(varItem) & ", "
Next varItem

' Remove the superfluous final ", "
If Len(strWhere) > 2 Then
strWhere = Left$(strWhere, Len(strWhere) - 2)
End If

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id IN (" & strWhere & ")"

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I need some help on creating a list box that when multiple entries
are
selected, another List box on the same form is "filtered".

I've read up on several other posts regarding the same subject
and
I'm
just not getting it. I've also read the write up on the
following
link:
www.mvps.org/access/forms/frm00007.htm
The light-bulb has not gone off yet.

The main form (frm_Funnel) displays the sales projects in the
List
Box
(lst_funnel). There are controls on the form that will filter
lst_funnel
by Manager and Sales Rep. I would also like to filter it down to
what
is
being sold. I have a list box that shows all of our Products
(lst_products) and I would like to be able to select multiple
Products
within lst_products and have lst_funnel only show the projects
that
contain the Products selected.

I see somehow I have to code the _Click( ) event within the
lst_products
list box to refer to a hidden Text Box that will house the
criteria
for
lst_funnel. Simply copying and pasting the code provided within
these
posts has not helped me at all because I need to understand what
the
code
actually means so I can duplicate it.

The Product List box is named "lst_products"; the projects list
box
is
named "lst_funnel". I can easily get this to work with just one
selection, but when I need to select more than one, it becomes
very
confusing.

Any help would be most appreciated.
Thank you,
Frank

 

[Douglas J. Steele]

You need quotes around the values.

If strWhere is equal to "Telephones and End User Devices", the SQL for your
RowSource would end up being:

SELECT * FROM qry_West_Funnel WHERE Product IN (Telephones and End User
Devices)

You need it to be:

SELECT * FROM qry_West_Funnel WHERE Product IN ("Telephones and End User
Devices")

Try:

For Each varItem in Me.Listbox1.ItemsSelected
strWhere = strWhere & Chr$(34) & Me.Listbox1.ItemData(varItem) &
Chr$(34) & ", "
Next varItem


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

It's straight text. For example:

"Telephones and End User Devices" is one of the Products listed in the
Lst_Products list box. There are 73 in total.

The problem definitley lies with:
Me.Lst.funnel.RowSource = "SELECT * FROM qry_West_Funnel WHERE Product IN
(" & strWhere & ")"

because I removed the line to see what would happen. After I removed it,
I opened the form and when I selected a Product, nothing at all happened.
But as soon as that line is put back in, Lst_Funnel goes completely blank.

What does the value of strWhere look like?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

There must be some other issue. Lst_funnel still goes blank when I
select a Product from the Lst_Products list box. (even the column
headings in Lst_funnel disappear). The source of Lst_funnel is not
tbl_West_Funnel_Details. It is a query, so I changed

Me.Lst_funnel.RowSource = "SELECT * FROM tbl_West_Funnel_Details WHERE
Product IN (" & strWhere & ")"

to

Me.Lst_funnel.RowSource = "SELECT * FROM qry_West_Funnel WHERE Product
IN (" & strWhere & ")"

The Multi Select setting of Lst_Products is Extended, but I switched it
to Simple and the result was the same. And I'm not getting any error
messages.

You need a space between the asterisk and the FROM keyword:

Me.Lst_funnel.RowSource = "SELECT * FROM tbl_West_Funnel_Details WHERE
Product IN (" & strWhere & ")"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ok, I replaced them and I'm still getting the same result. The
Lst_Funnel
list box goes completly blank.

Here is what the code looks like now (in the SELECT *..." statement, I
also
tried putting in the field names, but that didn't work either.
qry_West_Funnel_Details is the source for Lst_Funnel and there is a
field
in
there called Product which is what I'm trying to filter on.):

Private Sub Lst_Products_AfterUpdate()
Dim strWhere As String

Dim varItem As Variant


For Each varItem In Me.Lst_Products.ItemsSelected

strWhere = strWhere & Me.Lst_Products.ItemData(varItem)

Next varItem

If Len(strWhere) > 2 Then

strWhere = Left$(strWhere, Len(strWhere) - 2)

End If


Me.Lst_funnel.RowSource = "SELECT *" & "FROM tbl_West_Funnel_Details
WHERE
Product IN (" & strWhere & ")"


End Sub

message
You need to replace Field1, Field2, Id and MyTable with the actual
items
from your database.

(To be honest, if you didn't change that code, I would have expected
you
to
get a bunch of pop-ups asking for values and/or error messages that
it
couldn't find those items)

As to your other question, with my approach, there is no need for
the
hidden
text field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried the code you provided and just replaced ListBox1 and
ListBox2
with
Lst_Products and Lst_Funnel respectively. When I open the form and
select
a
Product, the Lst_Funnel goes blank, not even column headings are
displayed.

Do I need to replace other syntax? For example, what are you
referring
to
when you have "SELECT Field1, Field2..." and "MyTable"? Is that
part
of
the
syntax or do I need to replace those with items from my database?

message
If you didn't have a multi-select list box, your AfterUpdate
event
would
be something like:

Private Sub Listbox1_AfterUpdate()

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id = " & Me.Listbox1

End Sub

Since you've got a multiselect listbox, though, you need to
create a
WHERE
clause that includes all of the values that have been selected in
the
first list box.

The sample code you've cited will generate something like:

WHERE [EmpId] = 5 OR [EmpId] = 8 OR [EmpId] = 15

To be honest, I think it's better to use:

WHERE [EmpId] IN (5, 8, 15)

since that will generate a short SQL statement, which can make a
difference if you've got a lot of selections.

Private Sub Listbox1_AfterUpdate()
Dim strWhere As String
Dim varItem As Variant

' Get all of the selected items
For Each varItem in Me.Listbox1.ItemsSelected
strWhere = strWhere & Me.Listbox1.ItemData(varItem) & ", "
Next varItem

' Remove the superfluous final ", "
If Len(strWhere) > 2 Then
strWhere = Left$(strWhere, Len(strWhere) - 2)
End If

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id IN (" & strWhere & ")"

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I need some help on creating a list box that when multiple
entries
are
selected, another List box on the same form is "filtered".

I've read up on several other posts regarding the same subject
and
I'm
just not getting it. I've also read the write up on the
following
link:
www.mvps.org/access/forms/frm00007.htm
The light-bulb has not gone off yet.

The main form (frm_Funnel) displays the sales projects in the
List
Box
(lst_funnel). There are controls on the form that will filter
lst_funnel
by Manager and Sales Rep. I would also like to filter it down
to
what
is
being sold. I have a list box that shows all of our Products
(lst_products) and I would like to be able to select multiple
Products
within lst_products and have lst_funnel only show the projects
that
contain the Products selected.

I see somehow I have to code the _Click( ) event within the
lst_products
list box to refer to a hidden Text Box that will house the
criteria
for
lst_funnel. Simply copying and pasting the code provided within
these
posts has not helped me at all because I need to understand what
the
code
actually means so I can duplicate it.

The Product List box is named "lst_products"; the projects list
box
is
named "lst_funnel". I can easily get this to work with just one
selection, but when I need to select more than one, it becomes
very
confusing.

Any help would be most appreciated.
Thank you,
Frank

 

[FrankTimJr]

Still no good. I'll just have to deal w/out that functionality in my
database. I really appeciate you taking your time, but this is beyond my
knowledge and I just can't get it to work.

Thanks again for all your help.

You need quotes around the values.

If strWhere is equal to "Telephones and End User Devices", the SQL for
your RowSource would end up being:

SELECT * FROM qry_West_Funnel WHERE Product IN (Telephones and End User
Devices)

You need it to be:

SELECT * FROM qry_West_Funnel WHERE Product IN ("Telephones and End User
Devices")

Try:

For Each varItem in Me.Listbox1.ItemsSelected
strWhere = strWhere & Chr$(34) & Me.Listbox1.ItemData(varItem) &
Chr$(34) & ", "
Next varItem


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

It's straight text. For example:

"Telephones and End User Devices" is one of the Products listed in the
Lst_Products list box. There are 73 in total.

The problem definitley lies with:
Me.Lst.funnel.RowSource = "SELECT * FROM qry_West_Funnel WHERE Product IN
(" & strWhere & ")"

because I removed the line to see what would happen. After I removed it,
I opened the form and when I selected a Product, nothing at all happened.
But as soon as that line is put back in, Lst_Funnel goes completely
blank.

What does the value of strWhere look like?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


There must be some other issue. Lst_funnel still goes blank when I
select a Product from the Lst_Products list box. (even the column
headings in Lst_funnel disappear). The source of Lst_funnel is not
tbl_West_Funnel_Details. It is a query, so I changed

Me.Lst_funnel.RowSource = "SELECT * FROM tbl_West_Funnel_Details WHERE
Product IN (" & strWhere & ")"

to

Me.Lst_funnel.RowSource = "SELECT * FROM qry_West_Funnel WHERE Product
IN (" & strWhere & ")"

The Multi Select setting of Lst_Products is Extended, but I switched it
to Simple and the result was the same. And I'm not getting any error
messages.

You need a space between the asterisk and the FROM keyword:

Me.Lst_funnel.RowSource = "SELECT * FROM tbl_West_Funnel_Details WHERE
Product IN (" & strWhere & ")"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ok, I replaced them and I'm still getting the same result. The
Lst_Funnel
list box goes completly blank.

Here is what the code looks like now (in the SELECT *..." statement,
I
also
tried putting in the field names, but that didn't work either.
qry_West_Funnel_Details is the source for Lst_Funnel and there is a
field
in
there called Product which is what I'm trying to filter on.):

Private Sub Lst_Products_AfterUpdate()
Dim strWhere As String

Dim varItem As Variant


For Each varItem In Me.Lst_Products.ItemsSelected

strWhere = strWhere & Me.Lst_Products.ItemData(varItem)

Next varItem

If Len(strWhere) > 2 Then

strWhere = Left$(strWhere, Len(strWhere) - 2)

End If


Me.Lst_funnel.RowSource = "SELECT *" & "FROM tbl_West_Funnel_Details
WHERE
Product IN (" & strWhere & ")"


End Sub

message
You need to replace Field1, Field2, Id and MyTable with the actual
items
from your database.

(To be honest, if you didn't change that code, I would have
expected you
to
get a bunch of pop-ups asking for values and/or error messages that
it
couldn't find those items)

As to your other question, with my approach, there is no need for
the
hidden
text field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried the code you provided and just replaced ListBox1 and
ListBox2
with
Lst_Products and Lst_Funnel respectively. When I open the form
and
select
a
Product, the Lst_Funnel goes blank, not even column headings are
displayed.

Do I need to replace other syntax? For example, what are you
referring
to
when you have "SELECT Field1, Field2..." and "MyTable"? Is that
part
of
the
syntax or do I need to replace those with items from my database?

message
If you didn't have a multi-select list box, your AfterUpdate
event
would
be something like:

Private Sub Listbox1_AfterUpdate()

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id = " & Me.Listbox1

End Sub

Since you've got a multiselect listbox, though, you need to
create a
WHERE
clause that includes all of the values that have been selected
in the
first list box.

The sample code you've cited will generate something like:

WHERE [EmpId] = 5 OR [EmpId] = 8 OR [EmpId] = 15

To be honest, I think it's better to use:

WHERE [EmpId] IN (5, 8, 15)

since that will generate a short SQL statement, which can make a
difference if you've got a lot of selections.

Private Sub Listbox1_AfterUpdate()
Dim strWhere As String
Dim varItem As Variant

' Get all of the selected items
For Each varItem in Me.Listbox1.ItemsSelected
strWhere = strWhere & Me.Listbox1.ItemData(varItem) & ", "
Next varItem

' Remove the superfluous final ", "
If Len(strWhere) > 2 Then
strWhere = Left$(strWhere, Len(strWhere) - 2)
End If

Me.Listbox2.RowSource = "SELECT Field1, Field2 " & _
"FROM MyTable WHERE Id IN (" & strWhere & ")"

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I need some help on creating a list box that when multiple
entries
are
selected, another List box on the same form is "filtered".

I've read up on several other posts regarding the same subject
and
I'm
just not getting it. I've also read the write up on the
following
link:
www.mvps.org/access/forms/frm00007.htm
The light-bulb has not gone off yet.

The main form (frm_Funnel) displays the sales projects in the
List
Box
(lst_funnel). There are controls on the form that will filter
lst_funnel
by Manager and Sales Rep. I would also like to filter it down
to
what
is
being sold. I have a list box that shows all of our Products
(lst_products) and I would like to be able to select multiple
Products
within lst_products and have lst_funnel only show the projects
that
contain the Products selected.

I see somehow I have to code the _Click( ) event within the
lst_products
list box to refer to a hidden Text Box that will house the
criteria
for
lst_funnel. Simply copying and pasting the code provided
within
these
posts has not helped me at all because I need to understand
what the
code
actually means so I can duplicate it.

The Product List box is named "lst_products"; the projects list
box
is
named "lst_funnel". I can easily get this to work with just
one
selection, but when I need to select more than one, it becomes
very
confusing.

Any help would be most appreciated.
Thank you,
Frank