Multiselect listbox in a form for a report

[trs9385]

Hello experts!

I am creating a multiselect listbox in a form for a report. I have multiple
products that i would like to view in my report, however when I select two
products in the box nothing shows up. I would like to have multiple reports
off of this form. One which compares products prices, another that shows the
price, run date and month of a product and one that shows the difference in
prices from month to month (although i haven't gotten this far yet).

Is it possible to use one query to do all of this or will I need one query
for each report? And how do I go about doing these reports?

 

[Douglas J. Steele]

You need to use VBA to loop through the selected rows, create a WHERE clause
and update the SQL for the query.

See, for example, http://www.mvps.org/access/forms/frm0007.htm at "The
Access Web"

 

[trs9385]

A VBA loop to go through over half a million rows and 58 different products.

In other words I have 58 different products and they all have a price for a
specific date. The 58 products will be used as headings and the prices will
be under the corresponding heading. The VBA loop is still the simplest way
to do this?

 

[Douglas J. Steele]

Your original question was about using a multi-select listbox. VBA is the
only way to do that.

Now, however, you seem to be throwing a crosstab query into the equation (at
least, hopefully you are: that you don't have a table with 58 fields
corresponding to each product). That sounds as though you're going to want
to create your crosstab query dynamically so that it reflects the product(s)
chosen from the list box.

 

[trs9385]

Sorry, this is a very confusing subject for me. I will try and take it one
report at a time.

I have 4 headings right now:
Run Date, Product, Month, Value

I am trying to make different reports off this table, which has over half a
million rows.

The report I am working on has headings like this:
Run
Date-----------Month---------Product---------Product--------Product-------Product

With values under the product headings.

I have a form that has a combo box for each of these:
Run Dates, Months and Product

The same product shows up whenever I pick the Product combo box. I tried to
switch to a list box so i can select multiples but the report then shows
nothing.

I hope this helps explain things.

I appreciate your help!

 

[Douglas J. Steele]

What does your table look like? How are you getting the data on the report
today? Assuming you're using a query, what's the SQL of the query? If you're
not using a query, then how/where are you referring to the combo boxes?

Bottom line is that while you can refer to list boxes for which the
Multiselect property is set to None in the same way that you refer to combo
boxes, when the Multiselect property is other than None, you must use VBA to
get at the selected values.