Multi-Select List Box as Query Parameter

[Guest]

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

 

[Steven Greenberg]

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

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

 

[Guest]

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

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

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]

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.

 

[Guest]

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.

 

[Guest]

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]

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

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...

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

 

[Guest]

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

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

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...

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

 

[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

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

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

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...

:

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

 

[Guest]

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)

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

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

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

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...

:

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

 

[Guest]

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.

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

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...