Open Report Based on Multiple Combo Box Selections

[Econ2016]

Hi All,

Not sure if this is the right section to post this but I hope it is.

I have a set of reports stored in Access, and I am trying to generate a specific report based on multiple combo box selections and a "run report" button on a form.

For this example, Combo Box 1 = Location, and Combo Box 2 = Report Type.

Suppose I choose Toronto from Location, and Sales from Report Type. This means I'd like to generate the Sales Report for Toronto.

I have two issues - 1 major and 1 minor.

Major issue: I can currently only generate reports based on 1 combo box selection. My code is as follows:

DoCmd.OpenReport Forms!Form1!Loc, acViewPreview, , "[Loc]='Toronto'"

In order for this to work, I would need to have my sales report named as "Toronto". For the purpose of checking the code, I did that and it worked. However, I am trying to use multiple combo boxes and need more complex naming for my reports. How can I make it so that the code pulls the selection from multiple combo boxes and generates the corresponding report?

Minor issue:

When running the report, I get a popup message that asks me to specify location again:

How can I prevent this popup from appearing?

Any help is greatly appreciated!!

 

[Minty]

You should create a report that brings in all locations then open it with the filter set to 'Toronto' . So your first combo box lists the overall reports and your where clause is picked up from that.

It's slightly trickier than it sounds as you will need to specify the criteria in the reports list, as I'm guessing that whilst a Sales report might have Location as its filter, a product report might have product types or manufacturers .

So create a list of your reports and which criteria they would require from which table. Use this to populate your combobox.

Report - - - - - - CriteriaFld - - - - - - CriteriaTable
rptSales - - - - - - Location - - - - - - - tblLocations
rptCustomers - - - Location - - - - - - - tblLocations
rptProducts - - - - Manufacturer - - - - tblManufacturers

etc.

Then you can build your second combo based on the first one. See how you get on. You can refer to the other columns in the combo box using Me.YourCombo1.Column(0) This would be the first column. (They start numbered at 0).

Trying to do it by having a report for each location or manufacturer would very quickly become unmanageable.

 

[Econ2016]

I only have a limited number of reports so managing it would not be too difficult. Is there not a way for the report name to be recognized as the selection in multiple comboboxes?

Also, I don't understand creating a report with all the locations and then opening it with a filter. How does one attach a filter to a report?

 

[Econ2016]

Update:

DoCmd.OpenReport Forms!Form1!Loc & Forms!Form1!rep, acViewPreview, , "[Loc]='Toronto'" & "[Rep]='Sales'"

First, when I had my report named as "Toronto Sales", I got an error that "The report name TorontoSales is either misspelled in the Property sheet or the Macro.

After renaming it TorontoSales, I got an error "Syntax error (missing operator) in query expression '[Loc]='Toronto'[Rep]='Sales''.

This means the report is being recognized but the way I entered the & is incorrect. If I can just get this minor tweak out the way I will be good to go .

EDIT: SOLVED!!!!!

I put '&' instead of &.

Now if only I could get rid of that annoying Loc popup!!!

 

[pbaldy]

The Loc popup is Access telling you it can't find it. Is Loc a field in the report's record source? It needs to be. Also, your 2 criteria would have to include AND or OR to work correctly.

 

[Econ2016]

The Loc popup is Access telling you it can't find it. Is Loc a field in the report's record source? It needs to be. Also, your 2 criteria would have to include AND or OR to work correctly.

Hi pbaldy.

It actually is not a field in the report's record source.. strangely enough.

Also, AND does not work. I get "Type Mismatch". Instead, I use & between the combo boxes, and '&' in the second part:

DoCmd.OpenReport Forms!Form1!Loc & Forms!Form1!rep, acViewPreview, , "[Loc]='Toronto'" '&' "[Rep]='Sales'"

 

[pbaldy]

Well, for it to work it has to be a field in the source. The code in essence says "filter the field Loc to records with 'Montreal'". Obviously for that to have any meaning, there has to be a field named Loc.

You need the AND. It would look like:

DoCmd.OpenReport Forms!Form1!Loc & Forms!Form1!rep, acViewPreview, , "[Loc]='Toronto' AND [Rep]='Sales'"

You don't need to concatenate unless you're bringing in a dynamic value, like from a form.

 

[pbaldy]

By the way, are those 2 form references resulting in the name of a report? It looks odd like that.

 

[Econ2016]

Yes they are, pbaldy. Loc is the name of my first combo box, and Rep is the name of my second combo box, both on the same form. The user will select the location, select the type of the report, then click the generate report button and voila. The reports are named "Location""ReportType".

Forms!FormName!ComboBoxName is how one references the selection in a combo box. Does that still look odd to you? Is there another way you would write your code?

 

[pbaldy]

You have them in the position where the name of the report to be opened is. Typcially

DoCmd.OpenReport "ReportName",...

So I can't see how your code could work at all, given what they contain. If they're supposed to be used for the criteria:

DoCmd.OpenReport "ReportName", acViewPreview,,"Loc = '" & Forms!Form1!Loc & "' AND Rep = '" & Forms!Form1!rep & "'"

But you still have the problem of Loc not being a field in the report's source.

 

[Econ2016]

@pbaldy Thanks so much for the advice. This might be premature but I've been able to rid of the Loc popup box. The code I am now using is as follows:

DoCmd.OpenReport "TorontoSales", acViewPreview, , "Forms!Form1!Loc='Toronto'" '&' "Forms!Form1!Rep='Sales'"

I will now try it with multiple Open Report commands.

 

[jdraw]

Econ2016,

You may get some ideas from this material by Martin Green re Dynamic Reports.

He has many tutorials on his site.

Good luck.

 

[pbaldy]

Does that filter the report to the selected options? I don't see how it could.

 

[Econ2016]

Econ2016,

You may get some ideas from ______________. this material by Martin Green re Dynamic Reports.[/URL]

He has many tutorials on his site.

Good luck.

Thanks a lot! I will take a look at this site. At a glance it looks like it has what I'm looking for.

Does that filter the report to the selected options? I don't see how it could.

It does. However, when I code it your way and then put multiple options with 'OR', it opens every report listed in the code .

As mentioned earlier, my example is following a method suggested by a member on this board @Galaxiom. I'd post the link but unfortunately I need to have 10 posts to post a link. Then end of the link is below after the www DOT access-programmers DOT co DOT uk SLASH forums.

showthread.php?t=254040

 

[Minty]

Your report is opening with the correct data because you are opening the 'TorontoSales' Report. if you remove the filter condition completely I'm sure it will still work as your report is already filtered correctly.

If you read further in that thread you'll see that @Glaxiom recommends storing the values you are trying to look up / filter to in a a table as I suggested earlier.

 

[Econ2016]

Hi Minty,

Do you have an example of this that I could follow somewhere?

 

[Minty]

Not to hand, but follow some simple steps as per my original post - firstly create a table listing the report names you want, along with the field you want to use for criteria. I would also include a field for the table or query name that the report is based on.
You could add a descriptive text for the query rather than having to name them in a strange overly wordy fashion.
Keep it simple to start with - one report (Sales) and the criteria. It might be worth adding the Criteria data type as a seperate field as well. (E.g Text, Number, Date) , you'll see why later.

Each report you use must have the criteria field in the underlying data. So using your example - the Sales report must have the Location field in the underlying report data. It doesn't need to be on the report, although for clarity it often makes sense to put it in the report title - eg.
="Sales Report for " & [YourLocationFIeld] & " Area"

Once you have your table set up make it the source of your combo box, include all the columns in the combo even if you don't want them displayed.

You now have a combo box (or Listbox) that has the information we need in it to create the first part of your routine.

Get this working then come back to here, and we'll create a second combo containing the available values for the filter you want to apply. Unless you want to have a go yourself.

 

[Econ2016]

Thank you Minty. I will organize my priorities and chip away at it using your suggestions. If I run into further issues, I will get back to you. Greatly appreciated.