Using a multi-select listbox to provide query criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a listbox with a couple hundred customers. I want to be able to use
the listbox to limit my report to the selected customers. How can I do this?

I did try to follow Browne's tutorial on this but the tutorial uses a
two-column listbox, which doesn't apply to my case.

And guidance would be greatly appreciated.
 
Hi AB

Generally you would have (at least) two columns in such a list box. The
bound column would contain the primary key of your Customers table
(CustomerID) and the second column would show the customer name. It would
be normal to set the width of the first column to zero, so that the list
appeared to have only one column - CustomerIDs are often "messy" numbers
which are of no interest to the user.

The trick is then to enumerate the ItemsSelected collection of the listbox
and construct a string, so you can filter using an IN operator:

Dim vItem as Variant
Dim sList as string
For Each vItem In lstCustomers.ItemsSelected
sList = sList & lstCustomers.ItemData(vItem) & ","
Next vItem
' remove the last comma
If Len(sList)<>0 then sList = Left(sList, Len(sList)-1)
DoCmd.OpenReport "MyReport", , , "[CustomerID] IN (" & sList & ")"

I don't know what you've tried, but if you are still having trouble then
post back the code.
 
Graham (or anyone else)

I'm not using a customer table, hence, no customer #. The actual customer
is insignificant for the most part, except in the case where I want to see
everything that is inbound for Customer A (which might be 200 records at any
given time).

The point is that I want to use multi-select listboxes for provide
parameters for a report. For example, filter the report for records showing
a specific destination.

In these cases, the box is only one column.

Graham Mandeno said:
Hi AB

Generally you would have (at least) two columns in such a list box. The
bound column would contain the primary key of your Customers table
(CustomerID) and the second column would show the customer name. It would
be normal to set the width of the first column to zero, so that the list
appeared to have only one column - CustomerIDs are often "messy" numbers
which are of no interest to the user.

The trick is then to enumerate the ItemsSelected collection of the listbox
and construct a string, so you can filter using an IN operator:

Dim vItem as Variant
Dim sList as string
For Each vItem In lstCustomers.ItemsSelected
sList = sList & lstCustomers.ItemData(vItem) & ","
Next vItem
' remove the last comma
If Len(sList)<>0 then sList = Left(sList, Len(sList)-1)
DoCmd.OpenReport "MyReport", , , "[CustomerID] IN (" & sList & ")"

I don't know what you've tried, but if you are still having trouble then
post back the code.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


AB said:
I have a listbox with a couple hundred customers. I want to be able to use
the listbox to limit my report to the selected customers. How can I do
this?

I did try to follow Browne's tutorial on this but the tutorial uses a
two-column listbox, which doesn't apply to my case.

And guidance would be greatly appreciated.
Click to expand...
Click to expand...
 
OK, so the principle is exactly the same, except chances are the field you
are filtering on is text, not numeric, so instead of:
[CustomerName] in (John Doe,Fred Smith,Mary Martin)
you will need to enclose each name in quotes:
[CustomerName] in ("John Doe","Fred Smith","Mary Martin")

So now your code looks like this:
Dim vItem as Variant
Dim sList as string
Dim sDelim as string
sDelim = Chr(34) ' a double-quote as a delimiter
For Each vItem In lstCustomers.ItemsSelected
sList = sList & sDelim & lstCustomers.ItemData(vItem) & sDelim & ","
Next vItem
' remove the last comma
If Len(sList)<>0 then sList = Left(sList, Len(sList)-1)
DoCmd.OpenReport "MyReport", , , "[CustomerName] IN (" & sList & ")"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

AB said:
Graham (or anyone else)

I'm not using a customer table, hence, no customer #. The actual customer
is insignificant for the most part, except in the case where I want to see
everything that is inbound for Customer A (which might be 200 records at
any
given time).

The point is that I want to use multi-select listboxes for provide
parameters for a report. For example, filter the report for records
showing
a specific destination.

In these cases, the box is only one column.

Graham Mandeno said:
Hi AB

Generally you would have (at least) two columns in such a list box. The
bound column would contain the primary key of your Customers table
(CustomerID) and the second column would show the customer name. It
would
be normal to set the width of the first column to zero, so that the list
appeared to have only one column - CustomerIDs are often "messy" numbers
which are of no interest to the user.

The trick is then to enumerate the ItemsSelected collection of the
listbox
and construct a string, so you can filter using an IN operator:

Dim vItem as Variant
Dim sList as string
For Each vItem In lstCustomers.ItemsSelected
sList = sList & lstCustomers.ItemData(vItem) & ","
Next vItem
' remove the last comma
If Len(sList)<>0 then sList = Left(sList, Len(sList)-1)
DoCmd.OpenReport "MyReport", , , "[CustomerID] IN (" & sList & ")"

I don't know what you've tried, but if you are still having trouble then
post back the code.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


AB said:
I have a listbox with a couple hundred customers. I want to be able to
use
the listbox to limit my report to the selected customers. How can I do
this?

I did try to follow Browne's tutorial on this but the tutorial uses a
two-column listbox, which doesn't apply to my case.

And guidance would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top