Open Report Based on Combobox

[james28]

Hello All!


I have a very simple setup.


1 Form with a combobox. The combobox has 3 options: Report A, Report B, and Report C.

The form also has a command button.


I have 3 different reports created: ReportA, ReportB, and ReportC.




I would like for the correct report to open, based on the selection of the combobox at the time of clicking submit.


I know an if statement of some kind will be required. Could anyone please point me in the correct direction?


Thank you in advance!

[pbaldy]

If the combo contains actual report names:

DoCmd.OpenReport Me.ComboName

[james28]

Paul,

Thank you for the prompt reply. That is a simple yet elegant solution. Currently, my report names do not align exactly the same, because the reports have 4 words each in the title.

Is there anyway to do it if they do not match?


Thank you again!

[pbaldy]

Certainly, you have to use If/Then or Select/Case to examine the selected item and open the appropriate report.

[james28]

Paul,

Thank you again for the prompt reply.


I have tried to create a nested iif, but it does not seem to be working. Do you happen to see anything that I am missing?



IIf([forms]![testform]![ComboName]="Report A Long Name",DoCmd.OpenReport "ReportA", acViewReport, IIf([forms]![testform]![ComboName]="Report B Long Name",DoCmd.OpenReport "ReportB", acViewReport,DoCmd.OpenReport "ReportC", acViewReport))

Thank you again!

[pbaldy]

I wouldn't use IIf(), I'd use If/Then in VBA in either the after update event of the combo or behind a button.

[amrut]

@Paul

DoCmd.OpenReport Me.ComboName

Just for curiosity, irrespective of the report names ( like 2 or 4 words in title as mentioned by OP), why can't your first solution work ?

[pbaldy]

That solution would only work if the combo contained the actual report name. If the combo has "Forum Post Report" and the report to be opened is named "rptForumPosts" obviously the code would fail. It does occur to me that the combo could have a second hidden column with the actual names in it.

[June7]

Consider:

DoCmd.OpenReport "Report" & Switch(Me.[ComboName] = "Report A Long Name", "A", Me.[ComboName]="Report B Long Name", "B", Me.[ComboName]="Report C Long Name", "C"), acViewReport

Or with the hidden column as Paul suggested - column index begins with 0:

DoCmd.OpenReport Me.ComboName.Column(x), acViewReport

Why 3 reports - are they completely different in structure not just different filter criteria?

[james28]

June,

Thank you for the reply. Yes, all 3 reports are very different in structure, so I figured the easiest way would be to just create 3 different reports, especially since I can not have more than one detail section.

I am not sure how to add a hidden column, so I will try the first version that you suggested. I will report back shortly! Thank you again.

[james28]

June,

Following back up. That works perfectly. Thank you!

[June7]

Maybe subreports would serve your requirement.

[james28]

June,

Thanks again. Subreports is exactly what I am going to try.