Using Listboxes for Report Generation?

[burnsmr]

I have a table with 8 categories. I would like to be able to create a subform that will allow me to use listboxes to set the parameters that will be used to generate a report. Is there a way to set this up so that once the parameters have been chosen in each category I can generate the report based on those values? Im a novice to the whole coding thing and my developers handbook is really confusing. Any help is greatly appreciated.
Thanks
Mike

 

[KenHigg]

You could base your report on a query, where the query references these cbo box's as parameters.

kh

 

[daveUK]

Look at the following example, it should help you to get started

http://www.rogersaccesslibrary.com/download3.asp?SampleName=ChooseReportFields.mdb


HTH

 

[maxmangion]

as Ken suggested you should create your report based on a query and then in the criteria row of your query, place the reference of your controls names of your form i.e. Forms!FormName!ListboxName

 

[burnsmr]

I created a report based on my query, and returned to the query to set the parameters in the criteria. I am following the suggestion that Maxmangion provided, but am getting an error telling me I dont have an operand. Here is the code I put in .... Forms!Agency SubForm!List0
The subform is Agency Subform and the combo box is List0. Am I not defining the parameter correctly?

 

[KenHigg]

Seems the error is prompt is indicating you missed the equal sign in front of the expression. However, I suspect you may have the reference itself incorrectly coded. In the example below, replace everything starting with 'my', with your object names:

= forms!myMainForm!mySubForm.form!myListBoxName

???
kh

 

[burnsmr]

Thanks Ken,
I used the parameter you gave, replacing everything after "my" and when I added the equal sign it changed everything to .... [forms]![SubForm]![List0]
I changed the name of my subform to SubForm,(easier to remember) and the Listbox is List0. Do I need the brackets? I dont have the subform on the Main form yet, so I assumed I should leave out the mainform name. When I open the subform, click the data I want queried, then run the report, it gives me blank info. I know Im on the right track, but Im just not getting it. What do you think Im doing wrong?
Mike

 

[KenHigg]

When referencing controls on sub forms, you must refer to the sub form control name as defined in the parent form and not it's source name.

In the attached example, there is a main form named frmCustomers with a sub form control named Orders. This sub form has as it's source, the form named subfrmOrders. By selecting the sub form (SubFormExample_01.gif, Item A), and viewing the properties, you can see the control can have be named something other than the default name that Access assigns it, which defaults to the name of the form itself (SubFormExample_01.gif, Item B). In this example the name of the sub form control has been changed to 'Orders' from the default that Access assigned it; 'subfrmOrders'.

In this example, we want to refer to the text box 'PurchaseOrderNumber' on the sub form. SubFormExample_02.gif, Item C and D show how to make sure you have the correct name to refer to as you may also rename this control.

SubFormExample_03.gif is a screenshot of the final form. And SubFormExample_04.gif item D shows how to refer to the text box named 'PurchaseOrderNumber' on the sub form.

Hope this helps...
Ken

 

[burnsmr]

Thanks for all your help. It gave me a base to start trying different options that finally led to a working parameter on my form. Now, I am having trouble getting multiple parameters to work on one form. There are 4 sets of info on 4 septerate listboxes that I need to pull from, but if I try to pick only the last listbox or one in the middle without selecting one from the first box, I get a blank report. Is there a way to get the report to query each field without actually picking a dataset in the first combobox? Example of issue: Box1 = city, Box2 = categroy, Box3 = Component, Box4 = Type. If I want to query only Box2 and Box4 I get a blank report because I didnt select anything in box1. How can I get around this?

 

[KenHigg]

There may be a way to use iif()'s, isnull()'s and '*'s in the parameter section of a query for your report that will work, but I would simply build a custom query string to base your report on that is built in the report's 'OnOpen' event...

kh