Running a report by selecting different criteria from a form

[akash2010]

Hello,

I am VERY very new to MS Access and here is my question -

I created a form, which has various drop down menus and I want the users to select different criteria and run a report. So it's basically like:

If Combox15 = "North America"
Then Runreport = "North America Sales"
Else
If Combox15 = "South America"
Then Runreport ="SOuth America Sales" and so on..

Any help would be very very appreciated and thank you for your patience.

-A

 

[boblarson]

Why not just have the combo box have the actual report selection (here's a sample of a listbox which lists all reports with a certain prefix) and then you can open it up by using:

DoCmd.OpenReport Me.Combo15, acViewPreview

and I'm not sure how you are passing criteria (you can use a single generic report and make it work for more than one thing). See here for a sample about that.

 

[boblarson]

Oh, and I would rename your controls to meaningful things because combo15 doesn't do it for people. If you come back to this 2 years down the line or someone new has to maintain it, they should know what the combo15 does without having to go look. So something like this

cboSelectReport

is something that can self-document.

 

[akash2010]

Bob,

Thank you for your help and the information. I have already created different reports (for eg. North Am Sales, South Am Sales etc - please note that this is just an example, the actual reports are confidential). It won't be feasible to have a generic report because, for example, each report is further divided into countries.

And I am an Access dummy, so all I am trying to do is for the user to select a continent from a drop down menu -> click on the run report button -> and the report pertaining to the continent shows up (voila!). Any other ideas (like If then else or select case) would be greatly appreciated. Thank you for your time and patience.
-A

 

[boblarson]

Look at the list box on the first sample I showed.

You can use the same row source for that list box in your Combo's row source and then you will never need to modify the code or combo again. All you need to do in order for the report to show up in the combo (once you have copied this:

SELECT MsysObjects.Name, Mid([Name],5) AS QueryName FROM MsysObjects WHERE (((Left([Name],4))="rpt_")) ORDER BY MsysObjects.Name;

Into your combo box's ROW SOURCE property) you just have to name your reports like this:
rpt_North Am Sales
rpt_South Am Sales

Then you can use a button if you want to use this code:

DoCmd.OpenReport combo15, acViewPreview

no other code needed and if you add another report and name it with the rpt_ prefix it will automatically show up in the list and you'll never have to worry about coding for this again if somethingis added or deleted. To delete a report from the list you just remove the rpt_ part from its name.