List all Reports in a Combo Box

[foxfinance]

Good day everyone. I am trying to put a combo box in a form that lists all of my reports. I'd like to be able to select all of my reports from a list and when I choose a specific report, it automatically updates and opens. For example, I have report "A", "B" and "C" already formatted and in my objects list. I'd like to add Report "A", "B" and "C" to a combo box and when I select either of those reports, they update automatically (based on any information that I put into the tables / queries) and open. I've tried using the wizard to do this, but reports are not an option in the dialogue box. I have also searched these forums for answers, but these forums seem to be for the advanced and the technical jargon that's used is WAYYYY over my head. Is there anyone that can tell me what to do in laymans terms to help me figure this out? I have no programming experience, but I'm slightly familiar with Macros if I need to use them. I appreciate your help.

 

[boblarson]

Check out my sample database which basically does this. It uses a listbox instead of a combo box but you could adapt it to a combo if you liked. This dynamically shows all reports available, based on what you name them, so that you never need add the reports to the list; it will always list the full list as long as you name them with the certain prefix:

http://downloads.btabdevelopment.com/Samples/listbox/SampleSelectReportFromForm.zip

 

[foxfinance]

Check out my sample database which basically does this. It uses a listbox instead of a combo box but you could adapt it to a combo if you liked. This dynamically shows all reports available, based on what you name them, so that you never need add the reports to the list; it will always list the full list as long as you name them with the certain prefix:

http://downloads.btabdevelopment.com/Samples/listbox/SampleSelectReportFromForm.zip

Thank you. I really appreciate the input. However, I'm even more confused because I dont know how to use the MSysObjects thing. I've seen it before in one of the Access 2007 templates and somehow I figured out how to run the query for MSysObjects so that it'll list all of my objects, but I still don't know how to use MSysObjects for the combo box.

I've also noticed that your MSysObject list isn't listed in your queries list. When I ran mine, I had to save to my queries list How do you run it without having to save it to your queries list? Im still confused

 

[boblarson]

Okay, here goes...

1. You open my sample and go to the list box ROW SOURCE property.

2. Copy the string that is in there:

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

3. Go to the RowSource of your combo box and paste that in.

4. In the combo's Number of Columns property put 2.

5. In the combo's Column Width property put 0";2"

6. Then, whenever you name a report rpt_ and then the report name, it will display the report in the combo box.


Unless you know what you're doing, you shouldn't be using the MsysObject table as that is a system table and is hidden from view unless you go into TOOLS > OPTIONS > VIEW and select System Objects.

 

[foxfinance]

Thanks! I will try these steps in a few minutes and update you.

Okay, here goes...

1. You open my sample and go to the list box ROW SOURCE property.

2. Copy the string that is in there:

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

3. Go to the RowSource of your combo box and paste that in.

4. In the combo's Number of Columns property put 2.

5. In the combo's Column Width property put 0";2"

6. Then, whenever you name a report rpt_ and then the report name, it will display the report in the combo box.


Unless you know what you're doing, you shouldn't be using the MsysObject table as that is a system table and is hidden from view unless you go into TOOLS > OPTIONS > VIEW and select System Objects.

 

[foxfinance]

OK, I firgured out some things but not everything. When you told me what to paste, I paste it in the SQL view of the RowSource, but I used the code from the Access Template because, for some reason, your code wouldnt work for me. Now my combo box has the name if the reports, however, there must be some sort of Macro code to enter in because the combo box only lists the name of the reports, but it doesn't open them.


Also, I've been told that the MsysObjects are for advanced users, however, this is the only thing I saw when I first tried to figure out how to accomplish this current task. (The TOOLS>OPTIONS> VIEW MSystemObject doesn't seem to apply in Access 2007.) I have tried looking for books on this, but have been unsuccessful. Do you know of any books that teach me more on this?

Okay, here goes...

1. You open my sample and go to the list box ROW SOURCE property.

2. Copy the string that is in there:

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

3. Go to the RowSource of your combo box and paste that in.

4. In the combo's Number of Columns property put 2.

5. In the combo's Column Width property put 0";2"

6. Then, whenever you name a report rpt_ and then the report name, it will display the report in the combo box.


Unless you know what you're doing, you shouldn't be using the MsysObject table as that is a system table and is hidden from view unless you go into TOOLS > OPTIONS > VIEW and select System Objects.

 

[boblarson]

How do you want them to open? If on selection, then put this in the combo's Auto Update event (make sure it is in the event and not the event property - see here if not clear on where to put it: http://www.btabdevelopment.com/main/QuickTutorials/Wheretoputcodeforevents/tabid/56/Default.aspx):

DoCmd.OpenReport Me.YourComboName, acViewPreview

 

[foxfinance]

Page not found

How do you want them to open? If on selection, then put this in the combo's Auto Update event (make sure it is in the event and not the event property - see here if not clear on where to put it: http://www.btabdevelopment.com/main/QuickTutorials/Wheretoputcodeforevents/tabid/56/Default.aspx):

DoCmd.OpenReport Me.YourComboName, acViewPreview

 

[boblarson]

Page not found

Try it again as I just modified the link to work. Somehow the ): got included as part of the URL.

 

[JoseL]

This is how I did.

At the combox box property "Row Source", place how do you want the report names to be displayed:
"Report1";"Report2";.......

At the AfterUpdate property place:

if me!ComboBoxName!ListIndex=0 then
docmd.openreport "Report1Name", acViewPreview
endif
if me!ComboBoxName!ListIndex=1 then
docmd.openreport "Report2Name", acViewPreview
endif
... and so on

 

[theKruser]

6. Then, whenever you name a report rpt_ and then the report name, it will display the report in the combo box.

In this, my naming convention will be altered in that I will have to remove all of the underscores and replace with spaces. I thought this practise was rather taboo in Access. Will there ever be a time where this will cause an issue when renaming my reports? I know it would never work for queries and tables, but am wondering if the same applies to reports.

Thanks for all of your help, Bob. We on AWF would be lost without you!!

 

[boblarson]

In this, my naming convention will be altered in that I will have to remove all of the underscores and replace with spaces. I thought this practise was rather taboo in Access. Will there ever be a time where this will cause an issue when renaming my reports? I know it would never work for queries and tables, but am wondering if the same applies to reports.

I typically am only opening reports and the names with spaces don't have a deterimental effect to me. Although it is up to you how you really do it. There are many, many different ways you can do it. So, I just provided one way, of many, to do it.

Thanks for all of your help, Bob.

Glad I can be of help

We on AWF would be lost without you!!

Not really - we have so many really good people here that, if I suddenly went away, it really wouldn't be noticed and when it finally was, it really wouldn't be to a great extent. The forum here is full of people who have more smarts than I have. I could probably name 10 very quickly. But, the thing is that we all work together to provide an awesome service. So, while I appreciate the sentiment, it really is not true that anyone would be lost without any one of us. It would all go on just fine and without a glitch. And that's the way it should be.

 

[theKruser]

I was more commenting on the help you have offered me on more than one issue. Perhaps the system would stand. This is by far the best forum I am a part of. honestly, I have stopped using all the others.

As to my previous question, I appreciate the help/info. The question then becomes, is there a way to make the List Box show info listed in the "Caption" field in the report properties? Then the naming convention would be void and the List Box would contain easily discernable list of items. Thanks again.

 

[boblarson]

The question then becomes, is there a way to make the List Box show info listed in the "Caption" field in the report properties? Then the naming convention would be void and the List Box would contain easily discernable list of items.

Not that I've been able to figure out. Hence, that's why I use the naming that I do for reports. If you do figure it out, post it back here so then others (including me) would learn how

 

[theKruser]

Will do. However, I am no where near the Access Master that you are. If you haven't figured it out, chances are I won't either. I will try, though, and if I get it, I will post it. Thanks again.