Multi Selection Combo Box Query

[goobrenders]

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!

 

[Jeff Boyce]

You may want to step back and reconsider your table design.

If you figure you need one column for every "category", then every time the
number of categories changes, you have to revise your table structure, your
queries, your forms, your reports, your macros, your code, (oh your aching
head!)!

That may be how you'd do it with a spreadsheet, but Access is a relational
database, and doesn't work well when you feed it 'sheet data.

If you are saying that one video can belong in more than one category, you'd
need a table structure something like:

tblVideo
VideoID
VideoTitle

tlkpCategory
CategoryID
CategoryTitle

trelVideoCategory
VideoCategoryID
VideoID
CategoryID

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP