Multi-Select list box as query criteria

[ReportSmith]

I've read all of the other posts and they've been very helpful, but I'm still
stuck.

I have a form frmMain, a list box (lstParticipant) with Multi Select set to
"Extended", and a text box (hidden) that contains my select statement:
......
Set frm = Forms!frmMain
Set ctl = frm!lstParticipant
strSQL = "SELECT [Participant] FROM [qryABC] WHERE [Participant]= " & """"

For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & """" & " OR [Participant]=
" & """"
Next varItem

strSQL = Left$(strSQL, Len(strSQL) - 20)
txtParameter = Trim(strSQL) 'I know this is extra
......
in addition, I have the following code to be able to pass this sql string to
the query...
......
Private Sub txtParameter_AfterUpdate()
Me.txtParameter = lstParticipant.Column(0)
End Sub
......
Within my query, I have the field [Participant] with the citeria grid entry
of [forms]![frmMain]![txtParameter]

For a single selection, I can get it to work, for multiple - no can do.
Another question is: should I be looking at the query in "SELECT
[Participant] FROM [qryABC] WHERE [Participant]= " & """" (it seems to be a
recursive call) or change [qryABC] to the original table name?

I've changed the code so many times that it may not be exactly what will
work for the single criterion, but any help would be greatly appreciated.

 

[Ken Snell MVP]

You cannot pass an SQL statement as a parameter to a query.

Your code is confusing to me. The following code step updates a control with
a value after you manually update that same control?

Private Sub txtParameter_AfterUpdate()
Me.txtParameter = lstParticipant.Column(0)
End Sub

And the above code is good only for a single-select listbox. It will not
work for a multi-select listbox.

What are you wanting to do? Filter a query for a report, using the
Participant filters? Or just change the SQL statement of a stored query?

Perhaps my sample database will give you some ideas for what you want to do:

Building SQL string based on values entered into controls
http://www.accessmvp.com/KDSnell/SampleDBs.htm#FilterForm

 

[ReportSmith]

Thanks Ken for the reply - I'll look at the example you listed.
As far as your question...I want to filter a query for dataset that I will
either use for a report or simply export, using the Participant filters.

 

[ReportSmith]

To save time (I've been working on this far too much - using it as a lesson
to myself and as an actual project), I changed the list box to a
(single-select) combo box and have an append query write each selection to a
table that I will then export.

It works and has some intrinsic error handling as well (not bad).

Thanks again for your help.

Thanks Ken for the reply - I'll look at the example you listed.
As far as your question...I want to filter a query for dataset that I will
either use for a report or simply export, using the Participant filters.

I've read all of the other posts and they've been very helpful, but I'm still
stuck.

I have a form frmMain, a list box (lstParticipant) with Multi Select set to
"Extended", and a text box (hidden) that contains my select statement:
.....
Set frm = Forms!frmMain
Set ctl = frm!lstParticipant
strSQL = "SELECT [Participant] FROM [qryABC] WHERE [Participant]= " & """"

For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & """" & " OR [Participant]=
" & """"
Next varItem

strSQL = Left$(strSQL, Len(strSQL) - 20)
txtParameter = Trim(strSQL) 'I know this is extra
.....
in addition, I have the following code to be able to pass this sql string to
the query...
.....
Private Sub txtParameter_AfterUpdate()
Me.txtParameter = lstParticipant.Column(0)
End Sub
.....
Within my query, I have the field [Participant] with the citeria grid entry
of [forms]![frmMain]![txtParameter]

For a single selection, I can get it to work, for multiple - no can do.
Another question is: should I be looking at the query in "SELECT
[Participant] FROM [qryABC] WHERE [Participant]= " & """" (it seems to be a
recursive call) or change [qryABC] to the original table name?

I've changed the code so many times that it may not be exactly what will
work for the single criterion, but any help would be greatly appreciated.