Passing Multi Select string to Query criteria

[oleBucky]

I know, it's a long post but please bear with me...

I am trying to pass the string results from a Multi Select list box to an existing SQL row source for a different lb. Two questions: 1) Is the following a reasonable approach? 2) If so, what might I use to pass the string to the existing SQL?

The row source in the dependent lb relies upon selections for four other lbs. I've tested the first three independently and then together and all is well. I am currently unable to integrate the final lb (which is the MS lb) into the row source for the dependent lb.

The particulars:
The three working source lbs are listHLBTeams, listMLBTeams and listPlayerYears. These are not MS.
The MS ls with which I am struggling is listPositions.
The lb with the results (dependent) is listPlayers.

My current approach is to use an After Update event with the following code I plagiarized and adapted to generate a string (strSQL) from the selections in listPositions. The string generated is exactly as I tested by manually entering into the criteria on the design view for the SQL driving the row source on listPlayers. Such as ""L-R" Or "Cf""

Private Sub listPositions_AfterUpdate()
    
    Dim frm As Form, ctl As Control
    Dim varItem As Variant
    Dim strSQL As String
        'Set frm = Form!frmCardByPosition
        Set ctl = Me.listPositions
        strSQL = """" & Me.listPositions.Value
        'enumerate selected items and
        'concatenate to strSQL
        For Each varItem In ctl.ItemsSelected
            strSQL = strSQL & ctl.ItemData(varItem) & """ Or """
        Next varItem

        'Trim the end of strSQL
        strSQL = Left$(strSQL, Len(strSQL) - 4)
    Me.txtEvalPositionsSelected = strSQL
    Me.listPlayer.Requery
    Me.txtImageRecordControl = Null
End Sub

However, I cannot come up with a function in the criteria window to use strSQL. This is the current code for the row source of the dependent lb without any attempt at passing the string from listPositions.

SELECT tblPlayers.PlayerID, tblPlayers.NameLast, tblPlayers.NameFirst, tblPlayerYear.PlayerYear, tblMLBTeams.City, tblMLBTeams.Club, tblPlayers.HLBTeamID, tblPlayers.MLBTeamID, tblPlayers.PlayerYearIDFK, tblPlayers.Pos1
FROM tblPlayerYear INNER JOIN (tblMLBTeams INNER JOIN tblPlayers ON tblMLBTeams.MLBTeamID = tblPlayers.MLBTeamID) ON tblPlayerYear.PlayerYearID = tblPlayers.PlayerYearIDFK
WHERE (((tblPlayers.HLBTeamID) Like [Forms]![frmCardByPosition]![listHLBTeam]) AND ((tblPlayers.MLBTeamID) Like [Forms]![frmCardByPosition]![listMLBTeam]) AND ((tblPlayers.PlayerYearIDFK) Like [Forms]![frmCardByPosition]![listPlayerYear]))
ORDER BY tblPlayers.NameLast, tblPlayers.NameFirst, tblPlayerYear.PlayerYear, tblMLBTeams.City, tblMLBTeams.Club;

My research continues but, in advance, any insight or assistance is greatly appreciated.

[jzwp11]

I cannot come up with a function in the criteria window to use strSQL

So you are trying to pass the contents of the variable in the code to a query you are designing in the query design grid? I don't think that will work.

Why don't you just build the row source query of the list box within the code itself and use the variable there. Then just assign the constructed query to the row source of the list box?

dim mySQL as string

mySQL= "SELECT .... FROM....."
mySQL= mySQL & "WHERE somefield in (" & strSQL & ")"

me.listboxname.rowsource=mySQL

[oleBucky]

I knew my flawed table design would lead to a very long SQL statement. This is what I was trying ot avoid as I am awful with syntax.

True to my fears, it took hours for me to get the syntax right but, with years of research and billions of dollars, I succeeded. The code below is my working version. Please note I decided to change the MS lb from listPositions to listMlbteams after some db working trials.

 
Private Sub listMLBTeam_AfterUpdate()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strTeamSubSQL As String
Dim strSQL As String
 
'Set frm = Form!frmCardByPosition
Set ctl = Me.listMLBTeam
strTeamSubSQL = "(tblPlayers.MLBTeamID)=" & Me.listMLBTeam.Value
'[MLBTeamID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strTeamSubSQL = strTeamSubSQL & ctl.ItemData(varItem) & " Or (tblPlayers.MLBTeamID)="
Next varItem
'Trim the end of strSQL
strTeamSubSQL = Left$(strTeamSubSQL, Len(strTeamSubSQL) - 26)
 
'Create the sql by substituting "strTeamsSubSQL" into longer sql statement
strSQL = _
"SELECT tblPlayers.PlayerID, tblPlayers.NameLast, tblPlayers.NameFirst, tblPlayerYear.PlayerYear, tblMLBTeams.City, tblMLBTeams.Club, tblPlayers.Pos1, tblPlayers.Pos2, tblPlayers.Pos3, tblPlayers.Pos4, tblPlayers.Pos5, tblPlayers.Pos6, tblPlayers.PlayerYearIDFK, tblPlayers.HLBTeamID, tblPlayers.MLBTeamID " & _
"FROM tblPlayerYear INNER JOIN (tblMLBTeams INNER JOIN tblPlayers ON tblMLBTeams.MLBTeamID = tblPlayers.MLBTeamID) ON tblPlayerYear.PlayerYearID = tblPlayers.PlayerYearIDFK " & _
"Where " & _
"(((tblPlayers.Pos1) Like [Forms]![frmCardByPosition]![listPositions]) AND ((tblPlayers.PlayerYearIDFK) Like [Forms]![frmCardByPosition]![listPlayerYear]) AND ((tblPlayers.HLBTeamID) Like [Forms]![frmCardByPosition]![listHLBTeam]) AND (" & strTeamSubSQL & ")) " & _
"OR (((tblPlayers.Pos2) Like [Forms]![frmCardByPosition]![listPositions]) AND ((tblPlayers.PlayerYearIDFK) Like [Forms]![frmCardByPosition]![listPlayerYear]) AND ((tblPlayers.HLBTeamID) Like [Forms]![frmCardByPosition]![listHLBTeam]) AND (" & strTeamSubSQL & ")) " & _
"OR (((tblPlayers.Pos3) Like [Forms]![frmCardByPosition]![listPositions]) AND ((tblPlayers.PlayerYearIDFK) Like [Forms]![frmCardByPosition]![listPlayerYear]) AND ((tblPlayers.HLBTeamID) Like [Forms]![frmCardByPosition]![listHLBTeam]) AND (" & strTeamSubSQL & ")) " & _
"OR (((tblPlayers.Pos4) Like [Forms]![frmCardByPosition]![listPositions]) AND ((tblPlayers.PlayerYearIDFK) Like [Forms]![frmCardByPosition]![listPlayerYear]) AND ((tblPlayers.HLBTeamID) Like [Forms]![frmCardByPosition]![listHLBTeam]) AND (" & strTeamSubSQL & ")) " & _
"OR (((tblPlayers.Pos5) Like [Forms]![frmCardByPosition]![listPositions]) AND ((tblPlayers.PlayerYearIDFK) Like [Forms]![frmCardByPosition]![listPlayerYear]) AND ((tblPlayers.HLBTeamID) Like [Forms]![frmCardByPosition]![listHLBTeam]) AND (" & strTeamSubSQL & ")) " & _
"OR (((tblPlayers.Pos6) Like [Forms]![frmCardByPosition]![listPositions]) AND ((tblPlayers.PlayerYearIDFK) Like [Forms]![frmCardByPosition]![listPlayerYear]) AND ((tblPlayers.HLBTeamID) Like [Forms]![frmCardByPosition]![listHLBTeam]) AND (" & strTeamSubSQL & ")) " & _
"ORDER BY tblPlayers.NameLast, tblPlayers.NameFirst, tblPlayerYear.PlayerYear, tblMLBTeams.City, tblMLBTeams.Club;"
Me.txtEvallistMLBTeams = strTeamSubSQL 'data verification text box
Me.txtSQLforlistPlayers = strSQL 'data verification text box
Me.listPlayer.RowSource = strSQL
Me.listPlayer.Requery
Me.txtImageRecordControl = Null
End Sub

[jzwp11]

Glad to hear that you got it worked out. If you want to save yourself future headaches maybe it is time to fix the flawed table structure?

[oleBucky]

Glad to hear that you got it worked out. If you want to save yourself future headaches maybe it is time to fix the flawed table structure?

m-m-m-m-m-m... could be.