help with code

[fipp]

I was given some advice by the website mvps.org and I am trying to put it together. I am trying to use a multi select list box as a query parameter.

"You can still use a parameterized query provided you pass the entire Where clause to it via code as a parameter. (eg. Have the query reference a hidden control to which you manually assign the complete WHERE clause using the following logic.)"

Here is what I have so far:

I have a form called "reportfilterfrm" on that form I will start with my
first list box named [teamlb]

the query that I would like to put the where clause into is
"statscalculationsqry" with the bound field of [team] ([team] is a text field)

I don't currently have a hidden control to which the where clause is
manually assigned and I am not sure where that is in the code below?

Here is the code that I have thanks to the MVPS.ORG website.

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form!reportfilterfrm
Set ctl = frm!teamlb
strSQL = "Select * from statscalculationsqry where [team]="
'Assuming text [team] 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 [team]="
Next varItem

'Trim the end of strSQL
strSQL=left$(strSQL,len(strSQL)-12))


I am not sure what to attach this code to?
I don't know how to send this string to the hidden control?

 

[Guus2005]

In the end you have a sql statement?
Use this sql statement to create a query:

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 "Fout in MakeQueryDef : " & Err.Number & ":" & Err.Description, vbExclamation, "Fout bij maken query definitie."
   Resume Exit_MakeQueryDef
End Function

(you also need this one)

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

and run it with

docmd.openquery strQueryName

hidden text box:
me.txtHidden.Value = strSql

Enjoy!