Multiple Selections from a List Box

[oleBucky]

I have researched this issue and through plagiarizing two forums, I've come up with an After Update event from one and two modules from another to do the task. The Public Function contributor indicated the SQL statement generated by the After Update Event could run with DoCmd which looks to his code. I assumed the Public Function and the Public code should be in a module (?). I also assumed the DoCmd should run after generation of the SQL statement in the After Update event. This is my latest failure.

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 = "Select * from tblPlayers where [Pos1]="
        '[Pos1] is the bound field in lb
        'enumerate selected items and
        'concatenate to strSQL
        For Each varItem In ctl.ItemsSelected
            strSQL = strSQL & ctl.ItemData(varItem) & " OR [Pos2]=" & ctl.ItemData(varItem) & " OR [Pos3]=" & ctl.ItemData(varItem) & " OR [Pos4]=" & ctl.ItemData(varItem) & " OR [Pos5]=" & ctl.ItemData(varItem) & " OR [Pos6]=" & ctl.ItemData(varItem) & " OR [Pos1]="
        Next varItem

        'Trim the end of strSQL
        strSQL = Left$(strSQL, Len(strSQL) - 11)
    DoCmd.OpenQuery strQueryName
    Me.txtPositionsSelected = strSQL
    Me.listPlayer.Requery
    Me.txtImageRecordControl = Null
End Sub

Again, it was my understanding the above code generated a SQL statement and that statement needed to be applied to the object control (txtPositionSelected). The following code is in a module titled "MakeQuery" intended to run the SQL.

Public Function MakeQueryDef(strSQLname As String, strSQLdef As String) As Boolean

   Dim qdf As QueryDef
   Dim dbs As Database

   On Error GoTo Err_MakeQueryDef

   Set dbs = CurrentDb

   DeleteQueryDef strSQLname ' Delete Query when existed

   Set qdf = dbs.CreateQueryDef(strSQLname, strSQLdef)

   MakeQueryDef = True

Exit_MakeQueryDef:
   Exit Function

Err_MakeQueryDef:
   MakeQueryDef = False
   MsgBox "Error in MakeQueryDef : " & Err.Number & ":" & Err.Description, vbExclamation, "Error creating query devinition."
   Resume Exit_MakeQueryDef
End Function
Public Sub DeleteQueryDef(strSQLname As String)

   Dim dbs As Database

   Set dbs = CurrentDb

   On Error Resume Next
   dbs.QueryDefs.Delete strSQLname 'Generates error when query doesn't exist.

End Sub

In this case, the debugger indicates the variable in the DoCmd, "strQueryName" is not defined. I've tried "strSQLname" unsuccessfully. I have not been able to get beyond that point to find the inevitable next bug. BTW, if I comment out the DoCmd, the After Update event populates txtPositionsSelected with the correct SQL text.

Might some of you folks verse in VBA review this code and help me debug. I am frustrated to the max.

[pbaldy]

What is the goal here (what's the query to be used for)? I do this type of thing for reports:

http://www.baldyweb.com/multiselect.htm

I see things wrong with both, but it would help to know the goal.

[oleBucky]

The goal is to allow the user multiple selections from a list box utilizing the MultiSelect property; i.e. select the Yankees, Reds and Cardinals from a list box with all the MLB teams. Then apply the selections to a query providing the records for another list box. From the newly queried list box, the user will select one record to show an image.

I'll check out your link now.

Thanks.

[pbaldy]

Based on that, I would expect the first method to use strSQL to set the rowsource of the other listbox. You appear to set a textbox and then requery the listbox, which wouldn't work. Try this line instead of those 2:

Me.listPlayer.RowSource = strSQL

If that doesn't work, add this one right before:

Debug.Print strSQL

which will print the finished SQL string to the VBA Immediate window. If you don't spot the problem with the SQL, post it here.

[oleBucky]

Thanks! I'll give it a shot and report back.