Back to the talking point database questions - every time I get close to wrapping this project up, I find some new feature to add or get told "it sure would be nice...." and I have to add things :) The latest need/ask is to allow for multi-keyword searching. I.e., find all talking points that have specific tags associated with it. Here's how my tables are setup:

1. tTalkingPoints - this is the table with the talking point text and links to other tables.

2. tTalkingPointKeywords - this is a mapping table. It has a column for idTalkingPoints (from the tTalkingPoints table) and a column for idKeywords (from the tKeywords table). This is how I do my one to many relationships in the back end.

3. Finally, the tKeywords table. ID and keyword columns only.

Right now, I have one big join query called aggregateQ that links all the data together, so I end up with row multiplication for the talking points (as expected) - so if there are 7 keywords associated with a talking point --> it returns 7 rows.

My keyword search now, is simple... combo box with a list of keywords; find rows with that keyword. Works brilliantly. What my users want to be able to do: find talking points based on more than one keyword. I.e. if this talking point has TRUCK & VAN as a keyword, show it. They want to be able to select as many keywords as possible. Given the way I have the tables setup; I suspect I need subqueries... but given that I have *n possible keyword selections - not sure where to begin. To snag the keywords, I intend on using a multiselect list box. I think the following code will get my values from it:

Details

Private Sub btnRunQuery_Click()
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
    
    ' Check if items are selected
    If Me.lstBox.ItemsSelected.Count = 0 Then
        MsgBox "Please select at least one item."
        Exit Sub
    End If
    
    ' Loop through the selected items
    For Each varItem In Me.lstBox.ItemsSelected
        strCriteria = strCriteria & "'" & Me.lstBox.ItemData(varItem) & "',"
    Next varItem
    
    ' Remove the trailing comma
    strCriteria = Left(strCriteria, Len(strCriteria) - 1)
    
    ' Build the SQL query
    strSQL = "SELECT * FROM YourTable WHERE YourField IN (" & strCriteria & ");"
    
    ' Run the query or use it in a form/report
    Me.RecordSource = strSQL
End Sub
Copy