Multi-Select List Box as Query Parameter

[Jeff Stroope]

I have a multi-select list box on a form with Subscriber ID's (1, 2, 3,...)
and I need to be able to select more than one Subscriber and pass those
selections to a query as criteria to pull records for the selected ID's.
With help from Douglas Steele, I was able to concatenate the ID's into a
string variable named strSQL as the following SQL statement and put it into a
text box on my form: "Select * from Subscribers where [SubscriberID] = 1
Or[SubscriberID] = 2 Or[SubscriberID] = 3 Or...". Next, I went to my query
and in the Criteria row for the SubscriberID field I built the following
expression:=[Forms]![MyForm]![txtStrSQL]. Unfortunately, the query pulls no
records and that's where I'm stumped. I then manually typed the value of
strSQL into the Criteria row and I get this message: "The syntax of the
subquery in this expression is incorrect. Check the subquery's syntax and
enclose the subquery in parentheses. Can anyone see what's wrong here?

Thanks,

Jeff

[Dan Artuso]

Hi,
There seems to be some confusion here.
You do not want to have the whole sql statement as criteria, that makes no sense.

The way I always handle this type of thing is to just change the sql of the query in code.
Seeing as you already have the complete sql statement built, just assign it to the query in question.

Dim qryDef As DAO.QueryDef
Set qryDef = CurrentDb.QueryDefs("yourQuery")
qryDef.Sql = strSql
Set qryDef = Nothing

That's it, your query now has the correct criteria

--
HTH
Dan Artuso, Access MVP

"Jeff Stroope" <JeffS...@discussions.microsoft.com> wrote in message news:6C809AE1-DD8C-4F55...@microsoft.com...

[Steven Greenberg]

=?Utf-8?B?SmVmZiBTdHJvb3Bl?= <JeffS...@discussions.microsoft.com>
wrote in news:6C809AE1-DD8C-4F55...@microsoft.com:

That's funny, I was just thinking about how to go about that myself. I
would like to also pick a group of names from a list of possible ones then
use them to generate a query for just those names.
You said you got help on how to generate the string statement. It is
probably through a loop run listbox.listcount times and checking if
selected is true or something like that. in VB I could do something like
this with ease. in Access, I haven't had experience. could you save me some
time and describe how this string variable is constructed. Thanks

[Jeff Stroope]

"Steven Greenberg" wrote:

Steven,

The advice I got was to take a look at
http://www.mvps.org/access/forms/frm0007.htm at "The Access
Web"

I hope it works for you.

>

[Steven Greenberg]

=?Utf-8?B?SmVmZiBTdHJvb3Bl?= <JeffS...@discussions.microsoft.com> wrote
in news:BB36E353-0BC2-4E85...@microsoft.com:

> http://www.mvps.org/access/forms/frm0007.htm

Thanks, I had already worked it out (and more) by now. It took a little
experimentation but I have it working perfectly.

[Amery]

I'm trying to do the same thing via a function using the materials at:

http://www.mvps.org/access/forms/frm0007.htm

I have it so that I can select one value and have it come out as a list. The
problem is when I try to select additional values at the same time, no values
are returned in the list. I'm thinking that it's because I do not have the
separator incorrectly set up. I would prefer to use this rather than the sub
so that when I get it working, I can use it multiple times within the same
database. Any suggestions?

[Duane Hookom]

There is a generic function for multi-select list boxes with sample usage at
http://www.access.hookom.net/Samples.htm.

--
Duane Hookom
MS Access MVP

"Amery" <Am...@discussions.microsoft.com> wrote in message
news:5A2E5237-190C-439E...@microsoft.com...

[Deb]

I've tried using the statement from the sample you referenced
(http://www.access.hookom.net/Samples.htm), but I'm getting the error message
"Undefined function 'IsSelectedVar' in expression"

Here's what I'm using:

SELECT [final output].name, [final output].Region, [final output].[Training
Type]
FROM [final output]
WHERE(( IsSelectedVar("Search","list10",[Region])=-1 ))
ORDER BY [final output].Region;

Do you know what I'm doing wrong?

Thanks...

"Duane Hookom" wrote:

> There is a generic function for multi-select list boxes with sample usage at
>http://www.access.hookom.net/Samples.htm
>

[Duane Hookom]

Did you import the module into your mdb? I think you also need to reference
the full form and control name. Please refer back to the samples.

--
Duane Hookom
MS Access MVP

"Deb" <D...@discussions.microsoft.com> wrote in message
news:6B713ECB-AF05-451D...@microsoft.com...

[Deb]

I did - as a module (it's been a few years since I've worked directly with
VBA, so I may not have it in the correct place).
Here's the code:

Function IsSelectedVar( _
Search As String, _
list10 As String, _
Region As Variant) _
As Boolean
'strFormName is the name of the form
'strListBoxName is the name of the listbox
'varValue is the field to check against the listbox
Dim lbo As ListBox
Dim item As Variant
If IsNumeric(Region) Then
Region = Trim(Str(Region))
End If
Set lbo = Forms(Search)(list10)
For Each item In lbo.ItemsSelected
If lbo.ItemData(item) - varValue Then
IsSelectedVar = True
End If
Next
End Function

[Duane Hookom]

Make sure the function is in a standard module and the name of the module is
not the same as the name of the function. Also, you should not have changed
any code.

--
Duane Hookom
MS Access MVP

"Deb" <D...@discussions.microsoft.com> wrote in message

news:E55A3453-B4B4-4B45...@microsoft.com...

[Deb]

The function is in a standard module & the only changes I've made in the code
are the table/form/field references. The debugger says it can't find the
form, but I've checked the spelling & refernce several times.

(thanks for all of your assistance - you've been very, very helpful with
this project)

[Deb]

After trying some different text in the code, I was able to get it.
Rather than using "Set lbo = Forms(Search)(List2)" to find the table, I used
"Set lbo = Forms![Search].List2"

Once again - thanks for all of your help.