Multi-select Combo Box

  • Thread starter Thread starter damien
  • Start date Start date
D

damien

Hi there,

I've got a multi-select combo box on a form, and I'd like
to pass the results to a query as a parameter.

I was trying to do using a VBA function, but this only
seems to be able to handle single values.

What's the best way to accomplish this ?


Damien

eg what I am trying to do

Function GetValues()
GetValues = "1, 2, 3, 4"
End Function

Then call above function in query...
 
I'll start by assuming that this is a multi-select list box. The combo box
doesn't multi-select.

When you set the list box for multi-select, its Value property no longer
works. You have to step through the SelectedItems list to see what has been
selected. Once you do this, there are a couple of ways to handle this in a
query.

1) Use a single field temporary table. Delete the values in the table and
fill it with the selected values from the list box. In your query, add this
table to the tables area at the top of the design grid and link it to the
matching field of the table already in the query. This will limit your
results to items where the linked field's value exists in both tables.

2) Create a comma delimited list of the selected items in a string variable.
Use this list in the WHERE clause of the query. You can do this by rewriting
the SQL of the query or possibly by filling in a textbox on the form with
this list and refer to the textbox. In the WHERE clause in the query, you
would use an IN statement to reference this list.

Example
SELECT .... FROM .... WHERE [MyField] IN ("a", "b", "c")
 
Cheers Wayne,

sorry about the List Box gaffe, how embarrassing !?

I've now got it to work using Eval:

WHERE Eval([control_no] & " In( " & GetValues() & ")") =
True

so I don't have to dynamically rewrite the SQL or use a
table ! Cool huh ?


Damien
-----Original Message-----
I'll start by assuming that this is a multi-select list box. The combo box
doesn't multi-select.

When you set the list box for multi-select, its Value property no longer
works. You have to step through the SelectedItems list to see what has been
selected. Once you do this, there are a couple of ways to handle this in a
query.

1) Use a single field temporary table. Delete the values in the table and
fill it with the selected values from the list box. In your query, add this
table to the tables area at the top of the design grid and link it to the
matching field of the table already in the query. This will limit your
results to items where the linked field's value exists in both tables.

2) Create a comma delimited list of the selected items in a string variable.
Use this list in the WHERE clause of the query. You can do this by rewriting
the SQL of the query or possibly by filling in a textbox on the form with
this list and refer to the textbox. In the WHERE clause in the query, you
would use an IN statement to reference this list.

Example
SELECT .... FROM .... WHERE [MyField] IN ("a", "b", "c")

--
Wayne Morgan
Microsoft Access MVP


damien said:
Hi there,

I've got a multi-select combo box on a form, and I'd like
to pass the results to a query as a parameter.

I was trying to do using a VBA function, but this only
seems to be able to handle single values.

What's the best way to accomplish this ?


Damien

eg what I am trying to do

Function GetValues()
GetValues = "1, 2, 3, 4"
End Function

Then call above function in query...
Click to expand...


.
Click to expand...
 
Hi there,

I've got a multi-select combo box on a form, and I'd like
to pass the results to a query as a parameter.

I was trying to do using a VBA function, but this only
seems to be able to handle single values.
Click to expand...

I presume you mean a multiselect Listbox - combo boxes don't come in
multiselect!

You'll need to build up the SQL string in code. See an example at
http://www.mvps.org/access/forms/frm0007.htm
 
Back
Top