List of Reports to View or Print Options

[Czeszyn]

Hello Everyone
I would like to know how I can have a form or a pull down list where I can choose what items I would like to have printed, and another pull down or form to chose which ones I would like to view.

I thought that it would be better to have something like this where someone can chose from a list which report they want to print or view, instead of having a whole lot of buttons.

Please if any one can walk me through this.

Thank you in advance.
Tony

 

[MarkK]

There's a hidden system table called MSysObjects that maintains a list of all the objects in your database file. You can find a list of reports in there. To see this table you can show system and hidden objects, or just write a query like . . .

SELECT * FROM MSysObjects

. . . and call it USysObjects or something. Then you can set the RowSource of your selection combo to something like . . .

SELECT * FROM USysObjects WHERE [Type] = -32764

. . . to see only the reports.

Or, if you want to only show some reports, make your own table with a finite list of reports the user might run in that context, and use that table as the rowsource for your combo.

Hope this helps,

 

[jdraw]

Here's a jpg of a form similar to what Markk suggested.

Update:

I have attached a database that has 2 forms
1 to open reports (as per the jpg)
1 to open forms

You can copy/import these into a database to try/use.

Good luck.

 

[Czeszyn]

Thank you MarkK and jdraw, I love this idea and I think this would look nice. Would I be able to only have the reports that I want listed, or will it show everything. Can you please also walk me through how to set this up, I am new the VB part of access.

 

[MarkK]

Can you please also walk me through how . . .

I would describe jdraw's attachments as a "walkthrough." If you are still stuck, can you describe where, or what the issue is?

 

[Czeszyn]

Okay, So first I would create my reports the way I want them correct. Before I forget, there might be 16 reports for one client, so on a form, I would have a pull down list, combo list to show just the reports that would be just for that client. So to do this part would I create each combo box separately, then where would I put the code to just list the reports that I want listed. I hope that this makes sense. It is just we have like 10 clients that each have their own reports, and those reports can run into 5 to 40. I was first doing buttons, but it just makes the form look unprofessional and clutter. So I thought his way would look impressive and neater.

 

[jdraw]

Yes, you could add criteria to restrict reports by name.
If you look at the criteria for the combo rowsource, you can adjust to suit your needs.

 

[MarkK]

there might be 16 reports for one client, so on a form, I would have a pull down list, combo list to show just the reports that would be just for that client.

In this case I would not use the MSysObjects table since your report list is a custom list. In that case I would make a custom table, maybe called tClientReport, that links to the client, and stores the list of report names, and maybe descriptions and other info, available for that client. Then I would base the combo's RowSource on that table. If you need help implementing that, what part would you need help on? First step is make a table tClientReport with fields like ClientReportID, ClientID, and ReportName.
If many clients might share many reports, then you might want a many-to-many relationship in creating this report management system. In that case make a tReport table which defines the report, and add a second table, maybe tClientReport that simply links the Client to the defined Report.
Does that make sense?

 

[Czeszyn]

Okay, thank you jdraw and MarkK, for responding. I am going to do some reports, then try what you have mention above. I will get back to you. I really like this idea too.

 

[Czeszyn]

Okay, I have created some reports for a client. I would like to be able to set it up something like what jdraw had mentioned, where the person can choose which report they would like to view or print. Does this also allow you to chose more than one report at a time? Anyways, I need help in starting to put this part together.

 

[MarkK]

Download jdraw's database, import his forms into your database, and see if you can get them to work. Report back if you get stuck, and where you get stuck. Report back if you want to make changes that you don't know how to make.

 

[Czeszyn]

Hello everyone, I tried to download the jdraw's database but it saved it as a attachement.php, and would not load up. Am I downloading it wrong?
The first one is just a jpeg, which I do like.

 

[jdraw]

Save the accdb I provided to known location.
Open your database and import the forms from the accdb I provided.

The forms I provided should allow you to select any report (other form allows you to select any form). Try them and get familiar --look at the comboboxes.

Since you have a custom set of reports(and forms), you should put the names in a separate table, use the table as rowsource for the combo. The user can then select the form/report.

If you have problems report back with details.

 

[Czeszyn]

I tried to drag and copy it into my folder as you said. Then I imported like you said. But I get this error message: Microsoft Access does not support opening HTML pages.
What am I doing wrong.

 

[Czeszyn]

If it matters I am using MS Access 2010

 

[jdraw]

I'm using 2010 also.
I don't understand this --

Microsoft Access does not support opening HTML pages.

Save the accdb to a known location.
Open your database.
Import the forms from the accdb.

 

[Czeszyn]

I have tried it again on my home computer and I get the same message. When it does the import, it keeps wanting to take it from the site and not from my file where I have it saved.

 

[Czeszyn]

Okay, when I first try to save the file, it wants to save it as a php file, and will not load up in access. If I drag it over, it saves it as a shortcut to the forum, and that is when I get that message about HTML. I noticed that it does not just let me save it by itself. So is there a trick to this?

 

[jdraw]

When I click on the accdb link, it asks me to Save or OpenWith MSAccess.
I choose Save and give the directory where I want it stored.

I then open a different database, go to External Data, Select Access, Browse to the proper Directory where the accdb was saved,Select Import, then OK.

A dialog opens, I select the objects to import.

 

[Czeszyn]

When I click on the link, it goes right to saving it. It doesn't give me that option. I am using IE, should I be using Chrome?