Multiselect listbox in a form for a report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

trs9385 - 19 Jun 2007 17:19 GMT

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?

Reply to this Message

Douglas J. Steele - 19 Jun 2007 17:44 GMT

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"

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hello experts!
>
[quoted text clipped - 11 lines]
> 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?

Reply to this Message

trs9385 - 19 Jun 2007 19:09 GMT

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?

> You need to use VBA to loop through the selected rows, create a WHERE clause
> and update the SQL for the query.
[quoted text clipped - 17 lines]
> > 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?

Reply to this Message

Douglas J. Steele - 19 Jun 2007 19:41 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>A VBA loop to go through over half a million rows and 58 different
>products.
[quoted text clipped - 33 lines]
>> > query
>> > for each report? And how do I go about doing these reports?

Reply to this Message

trs9385 - 19 Jun 2007 20:06 GMT

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!

> Your original question was about using a multi-select listbox. VBA is the
> only way to do that.
[quoted text clipped - 42 lines]
> >> > query
> >> > for each report? And how do I go about doing these reports?

Reply to this Message

Douglas J. Steele - 19 Jun 2007 20:17 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Sorry, this is a very confusing subject for me. I will try and take it
> one
[quoted text clipped - 24 lines]
>
> I appreciate your help!

Reply to this Message