Report based on value in list box or ...

[benniesanders]

Hi there,

I am relatively new to Access so please bear with me. I need to create a report based on multiple listbox values from my Category table. There is only one table involved. I would like the user to be able to choose one or more categories from a listbox. Then a report would return the category or categories and the list of parts associated with that particular category under the catetory heading. I don't know where to start except I have created a form with a listbox that lists the catetories. After that, I'm just plain lost. Any help would be appreciated. Thanks in advance.

 

[BanditWk]

Create your report that you would like to view based on the Listbox. In the underlying query of the report (you can find this by selecting Properties of the Report, click on the Data tab, Record Source is where we would create the underlying query)... If you click on the "...", you can go to a query designer and design that which you want to gather.

In the field for the Categories in the Criteria area, the following would be typed: "=Forms!NameOfFormWithListBox!NameOfListBox" (without the quotes)

Then, on the AfterUpdate on the listbox, have it open that specific report.

The following code I ALWAYS put into a report just in case:

In the NoData subfunction:

Private Sub Report_NoData(Cancel As Integer)
msgbox "The report has no data.", vbOKOnly +
vbInformation
Cancel = True
End Sub

Hope this helps!
Roy
aka BanditWk
Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)

 

[benniesanders]

Thanks, Roy. I"ll give it a try and let you know how it works. Thanks again.

 

[benniesanders]

Hi Roy,

Here's what I'm running into. I followed your instructions, created my query for the report, in the criteria field added the line you suggested. When I go to the form with the list box, and choose an item, I get the "The report has no data" message, but I know it does because it runs as a stand alone on the same query without the =form!frmPartsListing!lstPartsListing line. THen because it doesn't open the report, I get a debug error on the following "after update" event line from the list box:

DoCmd.OpenReport "rptPartListing", acPreview

Also, what if they choose multiple category types?

Does this make any sense? Thanks for your help!

 

[BanditWk]

Make sure the form is not in design view when you run the report. It's a common mistake. I do it all the time. However, after rereading your note, I see that you've done that.

Try removing the "=" from the query criteria.

Also, you need to make sure that the Listbox is referring to the right column number.

Check all name spellings and whatnot. Programming can get VERY picky.

OR, I was just thinking, on the AfterUpdate, you can open a report with limitations (maybe this is the best way). First remove the criteria that refers to the listbox, then on the AfterUpdate, put in the following code:

DoCmd.OpenReport "rptPartListing",acPreview,,"[NameofField] = " & Forms!NameOfFormWithListBox!NameOfListBox

If the field is a text item:

DoCmd.OpenReport "rptPartListing",acPreview,,"[NameofField] = '" & Forms!NameOfFormWithListBox!NameOfListBox & "'"


As for multiple categories, I'm not sure what you mean and you will need to elaborate.
Roy
aka BanditWk
Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)

 

[benniesanders]

Thanks Roy, I'll try that. I meant the users need to pick more than one item from the list box.

One thing I did manage to do is to view the list of selected items and they are coming out numerical instead of the value of the actual item. Like, 1, 3, 6 etc. instead of Beam, Stud, Anchor, etc... Any thoughts? I thought this might have something to do with no data being found, but if I run the report straight and type in BEAM, etc. the reports functions properly...