Select Report from Combo Box

[fenhow]

Is there any way to add a combo box on a form that lists a series of reports that can be pulled.

When selected it opens the appropiate report.

Say I have Report1, Report2, Report3 etc..

If they select Report2 from the ComboBox Report2 will open..

Thanks.

Fen How

 

[JEA]

Create a table with all the report names. Create a combo box on your form that uses this table to populate it's list or just populate it manually with the report names, depending on if you intend to add more reports later. In the 'After Update' property of the combo box run a macro that opens a report.

Haven't tested this bit:

In the Macro arguments, use [Forms]![frmName]![comboBoxName] as the report name.

 

[pbaldy]

Sure; search on MSysObjects and you should find both the SQL to get the report names and the code to open the selected report.

 

[ShaneMan]

Is there any way to add a combo box on a form that lists a series of reports that can be pulled.

When selected it opens the appropiate report.

Say I have Report1, Report2, Report3 etc..

If they select Report2 from the ComboBox Report2 will open..

Thanks.

Fen How

Sure. One thought is to make your cboBox work off of a Value List and then set it's RowSource to Report1;Report2;Report3

In the cboBox AfterUpdate event you could then use a Select Case statement to open the report you want

Select Case Me.cboBoxName
Case "Report1"
Docmd.OpenReport "Report1" 'and etc.
Case "Report2"
Docmd.OpenReport "Report2"
End Select

etc, etc.

HTH,
Shane

 

[pbaldy]

Shane, you don't need the Select/Case structure. You can use the value of the combo directly:

Docmd.OpenReport Me.ComboName

 

[fenhow]

Thanks, What do I put in the control source property? The cboBox is on a form in a tab control..

How do I add values to the value list?

Thanks.

Fen

 

[pbaldy]

If you want them all, the simplest solution is the one I pointed you towards in my first post.

 

[fenhow]

Ok, this works. I do not want all the reports to show only the ones I want to show because there are sooo many...

One more question, when I open the report it works but how do I get it to open only one report with the OrderID versus all reports..

In otherwords I am in a record OrderID200 when I open the report with (as shown above) it open a report for all records not just OrderID200..

Thanks..

Fen

 

[GaryPanic]

little pointer on this - give your report a number and a name

i have to keep track of reports issused - and if i change the format of a report
letter say quoterep half way througha a year - i would have to rename it
so what i have done on my comob list is have a code 1though 6 (for my 6 reports) - works fine now i need to replace one-or update it - i change the number to 7 and remove the dleted number -in my excample lets say 3 - i still now have 6 reports 1,2 4 ,5 6 and 7

i have a if statement to preview report and if looks at a number on my table and if fieldvalue = 1 do rport A and so on i don't remove the reoprt 3 i leave it in their so i can see what was issued- longer coding but i know what was issues and what version ..-keeps the auditors off my back

 

[GaryPanic]

Ok, this works. I do not want all the reports to show only the ones I want to show because there are sooo many...

One more question, when I open the report it works but how do I get it to open only one report with the OrderID versus all reports..

In otherwords I am in a record OrderID200 when I open the report with (as shown above) it open a report for all records not just OrderID200..

Thanks..

Fen

in your underlying qry (on which the reports should be based )you need
to refer it to the main table and the form where the data is display

so in your qry you will have a pk field (I hope so)
now your qry will need this under this field
=forms![nameofform]![nameoffield]
so nameofform = the name of your form where the data is being dispalyed
nameoffield= the field on your form where the unique number is (PK)


so when you print/preview your report the qry underneath this asks what info -oh.. it's in a form, which form, oh its xxxx form and where on the form is the reference oh, its the PK in a named field
and will report 1 record only

 

[ShaneMan]

Shane, you don't need the Select/Case structure. You can use the value of the combo directly:

Docmd.OpenReport Me.ComboName

Hey Paul,

We were posting at the same time so I did not get to see yours, however I can't pretend that I knew anything about the way to said to do it. As I told Bob a couple weeks back, "always learning." Now I have another way to get the same thing done., but this one is much slicker and with far less code. Thanks!

 

[boblarson]

And one more tip for anyone who wants it.

I typically don't want certain reports available until I am ready for them, but I hate having to maintain a reports table. I will typically name reports that I am ready to use with rpt_ and then I use this for my combo, or list boxes for the reports:

SELECT MSysObjects.Name, Mid([Name],5) AS ReportName
FROM MSysObjects
WHERE (((Left([Name],4))="rpt_"));

And then I use this in the After Update event:

DoCmd.OpenReport Me.MyComboName, acViewPreview

so, it is real easy to add new ones to the list just by naming them with rpt_ in the front of the name.

 

[pbaldy]

I think we're ALL always learning. I certainly am. I'm always learning how much I don't know.

 

[boblarson]

And one more tip for anyone who wants it.

I typically don't want certain reports available until I am ready for them, but I hate having to maintain a reports table. I will typically name reports that I am ready to use with rpt_ and then I use this for my combo, or list boxes for the reports:

SELECT MSysObjects.Name, Mid([Name],5) AS ReportName
FROM MSysObjects
WHERE (((Left([Name],4))="rpt_"));

And then I use this in the After Update event:

DoCmd.OpenReport Me.MyComboName, acViewPreview

so, it is real easy to add new ones to the list just by naming them with rpt_ in the front of the name.

Oh, and I forgot to mention that with that SQL, I am setting the columns to 0";2" and it will show the user the report name without the rpt_ in it.

 

[ShaneMan]

Oh, and I forgot to mention that with that SQL, I am setting the columns to 0";2" and it will show the user the report name without the rpt_ in it.

Ok, this post goes in my Code Repository. Very slick idea Bob. Thanks for sharing.

 

[boblarson]

Ok, this post goes in my Code Repository. Very slick idea Bob. Thanks for sharing.

No problem Shane! When I found out how to use the system table to get the report names, it became a cool thing, which made things much easier. I'm glad that others can get the benefit of that simple, yet so functional, method.