Multi Selection List Box Query

[goobrenders29]

I can't figure out how to connect my multi selection list box to a query. I
have created a table that lists different videos and then categorizes them by
such fields as "Christmas, Thanksgiving, Other Holdays, Misc." etc. So far in
the table I have each category in a different field and then a checkbox so
when the video is entered more than one category can be selected. However I'm
not sure that my table is set up correctly or if all the possible categories
have to be listed in one field, in a drop down box. I didn't think that would
work because it would depend on the order of the categories because I would
need to have more than one drop down box with all the categories in order for
the user to select more than one. The table is called "Media List" and the
multi section list box form is called "Media List Search Form".

I already have the following for my multi selection list box:

Private Sub List0_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")

For Each varItem In Me!lstRegions.ItemsSelected
strCriteria = strCriteria & ",'" & Me!lstRegions.ItemData(varItem) & "'"
Next varItem


If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If

strCriteria = Right(strCriteria, Len(strCriteria) - 1)

strSQL = "SELECT * FROM tblData " & _
"WHERE tblData.Region IN(" & strCriteria & ");"

qdf.SQL = strSQL

DoCmd.OpenQuery "qryMultiSelect"




Now I don't know how to set up the query so that it pulls up the selected
categories once you hit a button that runs the query.

Any help would be greatly appreciated! Thanks!

 

[Michel Walsh]

==========
So far in
the table I have each category in a different field
==========


I think that is the problem. The code you used assumes there is ONE field,
category, and MANY records (one per category) as example:

movie category
------------------ ------------
The Wizard of Oz Adventure
The Wizard of Oz Family
The Wizard of Oz Fantasy
The Wizard of Oz Musical



The way your table seems to be designed is 'ugly' in that if you ever add a
new category, you are obliged to add a field to the table AND you have to
add the combo box/controls which matches it in each and every form you
intend to use it.


Still, it can be done (but I don't suggest it) with:



For Each varItem In Me!lstRegions.ItemsSelected
strCriteria = strCriteria & " OR [" & Me!lstRegions.ItemData(varItem) &
"]"
Next varItem

(...)

strCriteria = Right(strCriteria, Len(strCriteria) - 4)
' remove the first " OR "
' but not the first [

(...)

strSQL = "SELECT * FROM tblData " & _
"WHERE " & strCriteria & ";"



Which assumes that Me!lstRegions.ItemData(varItem) returns the real field
names



Vanderghast, Access MVP

 

[goobrenders29]

If I set the table up so that the categories are in one drop box would I be
able to have 3 or 4 different drop down boxes so the user could select more
than one category. In doing this would I be able to have a query reguardless
of order?

==========
So far in
the table I have each category in a different field
==========

I think that is the problem. The code you used assumes there is ONE field,
category, and MANY records (one per category) as example:

movie category
------------------ ------------
The Wizard of Oz Adventure
The Wizard of Oz Family
The Wizard of Oz Fantasy
The Wizard of Oz Musical

The way your table seems to be designed is 'ugly' in that if you ever add a
new category, you are obliged to add a field to the table AND you have to
add the combo box/controls which matches it in each and every form you
intend to use it.

Still, it can be done (but I don't suggest it) with:

For Each varItem In Me!lstRegions.ItemsSelected
strCriteria = strCriteria & " OR [" & Me!lstRegions.ItemData(varItem) &
"]"
Next varItem

(...)

strCriteria = Right(strCriteria, Len(strCriteria) - 4)
' remove the first " OR "
' but not the first [

(...)

strSQL = "SELECT * FROM tblData " & _
"WHERE " & strCriteria & ";"

Which assumes that Me!lstRegions.ItemData(varItem) returns the real field
names

Vanderghast, Access MVP

I can't figure out how to connect my multi selection list box to a query. I
have created a table that lists different videos and then categorizes them

[quoted text clipped - 50 lines]

Any help would be greatly appreciated! Thanks!

 

[Michel Walsh]

A drop box only allow one selection (a list box may allow more than one, but
not a drop box). So, if the table has one field for the categories, you end
up making MANY records, not many fields.

For the selection, from the end user, you could use a LIST BOX, allowing
multi-select.


Building the OR criteria as suggested, or keeping your IN( ) criteria, will
retrieve all the records, having any one of the selected categories in that
listbox (regardless of the order the selection has been made).


Vanderghast, Access MVP

If I set the table up so that the categories are in one drop box would I
be
able to have 3 or 4 different drop down boxes so the user could select
more
than one category. In doing this would I be able to have a query
reguardless
of order?

==========
So far in
the table I have each category in a different field
==========

I think that is the problem. The code you used assumes there is ONE field,
category, and MANY records (one per category) as example:

movie category
------------------ ------------
The Wizard of Oz Adventure
The Wizard of Oz Family
The Wizard of Oz Fantasy
The Wizard of Oz Musical

The way your table seems to be designed is 'ugly' in that if you ever add
a
new category, you are obliged to add a field to the table AND you have to
add the combo box/controls which matches it in each and every form you
intend to use it.

Still, it can be done (but I don't suggest it) with:

For Each varItem In Me!lstRegions.ItemsSelected
strCriteria = strCriteria & " OR [" & Me!lstRegions.ItemData(varItem)
&
"]"
Next varItem

(...)

strCriteria = Right(strCriteria, Len(strCriteria) - 4)
' remove the first " OR "
' but not the first [

(...)

strSQL = "SELECT * FROM tblData " & _
"WHERE " & strCriteria & ";"

Which assumes that Me!lstRegions.ItemData(varItem) returns the real field
names

Vanderghast, Access MVP

I can't figure out how to connect my multi selection list box to a query.
I
have created a table that lists different videos and then categorizes
them

[quoted text clipped - 50 lines]

Any help would be greatly appreciated! Thanks!

 

[Ken Sheridan]

Its more than a question of amending the one table. You really need three
tables for this, Videos with one row per video, Categories with one row per
Category and VideoCategories say, which models the many-to-many relationship
between Videos and Categories. The VideoCategories table will have two
foreign key columns, each of which references the primary key of one of the
other tables, so these might be VideoID (assuming Videos has a numeric
primary key such as an autonumber) and Category (assuming that Categories has
a text column Category as its key rather than a 'surrogate' numeric key,
which is not necessary as all Category values will be distinct).

So for a video with a VideoID of 42 and which is classified as 'Christmas'
and 'Family' for instance the VideoCategories table would have rows:

42 Christmas
42 Family

For data input you'd normally use a form based on Videos (in single form
view), and within it a subform (in continuous form view) based on
VideoCategories, linked on VideoID. The subform would contain a combo box
bound to the category column and with a RowSource property of:

SELECT Category FROM Categories ORDER BY Category:

You don't need a control bound to VideoID in the subform; the value is
entered automatically into the underlying table via the linking mechanism.

Rather than simply opening a query you'll get a far better looking end
result with a report based on a query which joins Videos and VideoCategories
on VideoID. You can group the report on VideoID and out the title, date etc
in the group header, with the categories in the detail section. You can then
open the report from a button on the form which includes your multi-select
list box using code like this:

Dim varItem As Variant
Dim strCategoryList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.lstCategories

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strCategoryList = strCategoryList & ",""" &
ctrl.ItemData(varItem) & """"
Next varItem

' remove leading comma
strCategoryList = Mid(strCategoryList, 2)

strCriteria = "Category In(" & strCategoryList & ")"

DoCmd.OpenReport "rptVideos", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No category selected", vbInformation, "Warning"
End If

If you do really want to open a 'saved' query using the list box (rather
than building an SQL statement on the fly), then you need to create a value
list in a hidden text box on the form, which you can do with code in the list
box's AfterUpdate event procedure:

Dim varItem As Variant
Dim strCategoryList As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

For Each varItem In ctrl.ItemsSelected
strCategoryList = strCategoryList & "," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma and assign list to hidden text box
Me.txtCategoryList = Mid(strCategoryList, 2)

This is slightly inefficient as it rebuilds the list after each selection
(or de-selection) from the list box, but you won't notice any delay with the
small number of categories you'll be selecting. Note that unlike when
building the value list for opening a report you don't need to include quote
characters around each value if you use the second method at the link below
(I've assumed this in the above code).

You can then reference the hidden text box in a query which joins Videos and
VideoCategories using one of the methods described at:


http://support.microsoft.com/kb/100131/en-us


If you use the second method for instance you'd enter the following in a
column in query design view:

FieldName: InParam([Category],[Forms]![YourForm]![txtCategoryList])
Show: False
Criteria: True

Ken Sheridan
Stafford, England

If I set the table up so that the categories are in one drop box would I be
able to have 3 or 4 different drop down boxes so the user could select more
than one category. In doing this would I be able to have a query reguardless
of order?

==========
So far in
the table I have each category in a different field
==========

I think that is the problem. The code you used assumes there is ONE field,
category, and MANY records (one per category) as example:

movie category
------------------ ------------
The Wizard of Oz Adventure
The Wizard of Oz Family
The Wizard of Oz Fantasy
The Wizard of Oz Musical

The way your table seems to be designed is 'ugly' in that if you ever add a
new category, you are obliged to add a field to the table AND you have to
add the combo box/controls which matches it in each and every form you
intend to use it.

Still, it can be done (but I don't suggest it) with:

For Each varItem In Me!lstRegions.ItemsSelected
strCriteria = strCriteria & " OR [" & Me!lstRegions.ItemData(varItem) &
"]"
Next varItem

(...)

strCriteria = Right(strCriteria, Len(strCriteria) - 4)
' remove the first " OR "
' but not the first [

(...)

strSQL = "SELECT * FROM tblData " & _
"WHERE " & strCriteria & ";"

Which assumes that Me!lstRegions.ItemData(varItem) returns the real field
names

Vanderghast, Access MVP

I can't figure out how to connect my multi selection list box to a query. I
have created a table that lists different videos and then categorizes them

[quoted text clipped - 50 lines]

Any help would be greatly appreciated! Thanks!

 

[Springman]

Hi Ken,
I used your method to pass selections from a multi-select List box to a
hidden text box on a form for the purpose of running a parameter query. That
part worked great but I'm not having luck restricting the query. It appears
that the InParam operator isn't recognized in my query. Any advise is
appreciated.

Its more than a question of amending the one table. You really need three
tables for this, Videos with one row per video, Categories with one row per
Category and VideoCategories say, which models the many-to-many relationship
between Videos and Categories. The VideoCategories table will have two
foreign key columns, each of which references the primary key of one of the
other tables, so these might be VideoID (assuming Videos has a numeric
primary key such as an autonumber) and Category (assuming that Categories has
a text column Category as its key rather than a 'surrogate' numeric key,
which is not necessary as all Category values will be distinct).

So for a video with a VideoID of 42 and which is classified as 'Christmas'
and 'Family' for instance the VideoCategories table would have rows:

42 Christmas
42 Family

For data input you'd normally use a form based on Videos (in single form
view), and within it a subform (in continuous form view) based on
VideoCategories, linked on VideoID. The subform would contain a combo box
bound to the category column and with a RowSource property of:

SELECT Category FROM Categories ORDER BY Category:

You don't need a control bound to VideoID in the subform; the value is
entered automatically into the underlying table via the linking mechanism.

Rather than simply opening a query you'll get a far better looking end
result with a report based on a query which joins Videos and VideoCategories
on VideoID. You can group the report on VideoID and out the title, date etc
in the group header, with the categories in the detail section. You can then
open the report from a button on the form which includes your multi-select
list box using code like this:

Dim varItem As Variant
Dim strCategoryList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.lstCategories

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strCategoryList = strCategoryList & ",""" &
ctrl.ItemData(varItem) & """"
Next varItem

' remove leading comma
strCategoryList = Mid(strCategoryList, 2)

strCriteria = "Category In(" & strCategoryList & ")"

DoCmd.OpenReport "rptVideos", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No category selected", vbInformation, "Warning"
End If

If you do really want to open a 'saved' query using the list box (rather
than building an SQL statement on the fly), then you need to create a value
list in a hidden text box on the form, which you can do with code in the list
box's AfterUpdate event procedure:

Dim varItem As Variant
Dim strCategoryList As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

For Each varItem In ctrl.ItemsSelected
strCategoryList = strCategoryList & "," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma and assign list to hidden text box
Me.txtCategoryList = Mid(strCategoryList, 2)

This is slightly inefficient as it rebuilds the list after each selection
(or de-selection) from the list box, but you won't notice any delay with the
small number of categories you'll be selecting. Note that unlike when
building the value list for opening a report you don't need to include quote
characters around each value if you use the second method at the link below
(I've assumed this in the above code).

You can then reference the hidden text box in a query which joins Videos and
VideoCategories using one of the methods described at:


http://support.microsoft.com/kb/100131/en-us


If you use the second method for instance you'd enter the following in a
column in query design view:

FieldName: InParam([Category],[Forms]![YourForm]![txtCategoryList])
Show: False
Criteria: True

Ken Sheridan
Stafford, England

If I set the table up so that the categories are in one drop box would I be
able to have 3 or 4 different drop down boxes so the user could select more
than one category. In doing this would I be able to have a query reguardless
of order?

==========
So far in
the table I have each category in a different field
==========

I think that is the problem. The code you used assumes there is ONE field,
category, and MANY records (one per category) as example:

movie category
------------------ ------------
The Wizard of Oz Adventure
The Wizard of Oz Family
The Wizard of Oz Fantasy
The Wizard of Oz Musical

The way your table seems to be designed is 'ugly' in that if you ever add a
new category, you are obliged to add a field to the table AND you have to
add the combo box/controls which matches it in each and every form you
intend to use it.

Still, it can be done (but I don't suggest it) with:

For Each varItem In Me!lstRegions.ItemsSelected
strCriteria = strCriteria & " OR [" & Me!lstRegions.ItemData(varItem) &
"]"
Next varItem

(...)

strCriteria = Right(strCriteria, Len(strCriteria) - 4)
' remove the first " OR "
' but not the first [

(...)

strSQL = "SELECT * FROM tblData " & _
"WHERE " & strCriteria & ";"

Which assumes that Me!lstRegions.ItemData(varItem) returns the real field
names

Vanderghast, Access MVP

I can't figure out how to connect my multi selection list box to a query. I
have created a table that lists different videos and then categorizes them
[quoted text clipped - 50 lines]

Any help would be greatly appreciated! Thanks!

 

[Ken Sheridan]

Have you posted the InParam and GetToken functions from the MS site into a
standard module in the database?

Hi Ken,
I used your method to pass selections from a multi-select List box to a
hidden text box on a form for the purpose of running a parameter query. That
part worked great but I'm not having luck restricting the query. It appears
that the InParam operator isn't recognized in my query. Any advise is
appreciated.

Its more than a question of amending the one table. You really need three
tables for this, Videos with one row per video, Categories with one row per
Category and VideoCategories say, which models the many-to-many relationship
between Videos and Categories. The VideoCategories table will have two
foreign key columns, each of which references the primary key of one of the
other tables, so these might be VideoID (assuming Videos has a numeric
primary key such as an autonumber) and Category (assuming that Categories has
a text column Category as its key rather than a 'surrogate' numeric key,
which is not necessary as all Category values will be distinct).

So for a video with a VideoID of 42 and which is classified as 'Christmas'
and 'Family' for instance the VideoCategories table would have rows:

42 Christmas
42 Family

For data input you'd normally use a form based on Videos (in single form
view), and within it a subform (in continuous form view) based on
VideoCategories, linked on VideoID. The subform would contain a combo box
bound to the category column and with a RowSource property of:

SELECT Category FROM Categories ORDER BY Category:

You don't need a control bound to VideoID in the subform; the value is
entered automatically into the underlying table via the linking mechanism.

Rather than simply opening a query you'll get a far better looking end
result with a report based on a query which joins Videos and VideoCategories
on VideoID. You can group the report on VideoID and out the title, date etc
in the group header, with the categories in the detail section. You can then
open the report from a button on the form which includes your multi-select
list box using code like this:

Dim varItem As Variant
Dim strCategoryList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.lstCategories

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strCategoryList = strCategoryList & ",""" &
ctrl.ItemData(varItem) & """"
Next varItem

' remove leading comma
strCategoryList = Mid(strCategoryList, 2)

strCriteria = "Category In(" & strCategoryList & ")"

DoCmd.OpenReport "rptVideos", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No category selected", vbInformation, "Warning"
End If

If you do really want to open a 'saved' query using the list box (rather
than building an SQL statement on the fly), then you need to create a value
list in a hidden text box on the form, which you can do with code in the list
box's AfterUpdate event procedure:

Dim varItem As Variant
Dim strCategoryList As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

For Each varItem In ctrl.ItemsSelected
strCategoryList = strCategoryList & "," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma and assign list to hidden text box
Me.txtCategoryList = Mid(strCategoryList, 2)

This is slightly inefficient as it rebuilds the list after each selection
(or de-selection) from the list box, but you won't notice any delay with the
small number of categories you'll be selecting. Note that unlike when
building the value list for opening a report you don't need to include quote
characters around each value if you use the second method at the link below
(I've assumed this in the above code).

You can then reference the hidden text box in a query which joins Videos and
VideoCategories using one of the methods described at:


http://support.microsoft.com/kb/100131/en-us


If you use the second method for instance you'd enter the following in a
column in query design view:

FieldName: InParam([Category],[Forms]![YourForm]![txtCategoryList])
Show: False
Criteria: True

Ken Sheridan
Stafford, England

If I set the table up so that the categories are in one drop box would I be
able to have 3 or 4 different drop down boxes so the user could select more
than one category. In doing this would I be able to have a query reguardless
of order?

Michel Walsh wrote:
==========
So far in
the table I have each category in a different field
==========

I think that is the problem. The code you used assumes there is ONE field,
category, and MANY records (one per category) as example:

movie category
------------------ ------------
The Wizard of Oz Adventure
The Wizard of Oz Family
The Wizard of Oz Fantasy
The Wizard of Oz Musical

The way your table seems to be designed is 'ugly' in that if you ever add a
new category, you are obliged to add a field to the table AND you have to
add the combo box/controls which matches it in each and every form you
intend to use it.

Still, it can be done (but I don't suggest it) with:

For Each varItem In Me!lstRegions.ItemsSelected
strCriteria = strCriteria & " OR [" & Me!lstRegions.ItemData(varItem) &
"]"
Next varItem

(...)

strCriteria = Right(strCriteria, Len(strCriteria) - 4)
' remove the first " OR "
' but not the first [

(...)

strSQL = "SELECT * FROM tblData " & _
"WHERE " & strCriteria & ";"

Which assumes that Me!lstRegions.ItemData(varItem) returns the real field
names

Vanderghast, Access MVP

I can't figure out how to connect my multi selection list box to a query. I
have created a table that lists different videos and then categorizes them
[quoted text clipped - 50 lines]

Any help would be greatly appreciated! Thanks!