Access Forms (2000)

Topic

New Reply

Hi. I am a newbie. I have a database with multiple reports and I would like to create a form with list of these reports you can click on one in the list and then click preview or print to print that report. I have looked at the Northwind Database example which has a Sales by Category Detail which is what I want but I can not figure out how it gets it’s information and how to make it work. Please help I am stumped. Than you in advance for any help you can give.

Reply | Quote

Replies

Here’s a basic outline of one approach (reply back if you need more detail):

1. Create a table which includes your report names (this table could be populated by inspecting the CurrentDb().Containers(“Forms”).Documents collection — if you’re familiar with VBA and navigating the object model — otherwise, just fill in the table by hand).

2. Create a form that uses this table as its Record Source. Include a text control that references the table’s report name field.

3. On this form create a “Preview” button. As its OnClick event, specify the following command:

DoCmd.OpenReport Me!txtReportName, acViewPreview

(txtReportName is the Name for the text control on the form that displays the selected report name).

4. Create a similar “Print” button with a simiar OnClick event, but specify acViewNormal instead of acViewPreview

I think something like that would get you what you want. I’ve left out the gory details. If you need them, holler.

Reply | Quote

I think I must not be too smart because I am lost……LOL. I have a table I created with 2 fields first one is report second is report name. I typed in the Report in the first field but what do I put in the second. Second… I have the list box on my form and a preview and print button but when I created the buttons they attached themselves to a specific report HELP! Thanks.

Reply | Quote

I think you’re confused. First, you create all of your reports. Next, create a form and, as Mr. Swanson so expertly advised, add 1 control button to the form for each report. Finally, ‘program’ each button with the code Mr. Swanson supplied, replacing ‘Report Name’ with the actual name of the report. To access the Code Builder for each control button, highlight the button (not the label), right click on the button, select ‘Build Event…’, select ‘Code Builder’, and paste Mr. Swanson’s code in, make sure you specify the correct report name. You should be good to go.

I’m not sure what you’re trying to do with the table you created.

Reply | Quote

I do not want a check box or button for each report there are 25 reports. I want to use a list box for the reports and click on one to highlight it and then hit a print button or hit preview to see the reports. Thank you. I need to know how to do this in a list box. Thanks

Reply | Quote

It sounds as if you are looking for Tom’s method. Tom is creating a listbox that links to a table with your report names.

First, create a table with the names of the reports. The table can have two fields, ID (use autonumber and set as primary key) and name (set as text, size as needed to store the report name) Input the report names in the table such that the report name in the table matches the report name object (i.e, include spaces, commas, etc).

Create the listbox (use the wizard, its easier) and connect to the table so it gets the data for the listbox from the table. Thus, when using the listbox, you will be able to select the name of the report you want.

Then follow the rest of Toms’ directions. This is not as fancy as the Northwinds database, but it works.

Good Luck

Reply | Quote

Thanks. I got that far to set up the List box and it is using the table but I didn’t understand the rest of the directions that TOM gave me. I guess I don’t know half of what I thought I knew about access. Can you please explain in more detail the rest of the process? Thank you again for bearing with a newbie.

Reply | Quote

Hoorayyyy……I figured it out with all you guys help. Thank you everyone for the help. I love the Woody’s Lounge. All the people here are so helpfull every time I need help. Thanks again all.

Reply | Quote

Hi,
This may be too late, but if you want a dynamic list of reports (i.e. not having to add new reports to your table as you create them) you can set your listbox row source to:
SELECT Name from MSysObjects WHERE Type = -32764
and this will then pick up ALL reports in your database. This does have the disadvantage that it will include any subreports in your database, but it’s less effort to maintain!
Hope that helps.

Reply | Quote

Hey Rory,

You Said:
SELECT Name from MSysObjects WHERE Type = -32764

This is pretty cool. I wish I knew about this sooner. Do you use or can you recommend any reference manuals that review items like this???

Reply | Quote

Neil,

Glad you got it figured out. Sorry I didn’t check back sooner to see if you needed more help. Obviously there’s lots of help here in the Lounge, ready and waiting! I knew I had left lots of gaps in my suggestion — sometimes you learn more by figuring things out rather than following a recipe, right? Frustrating at times, sure, but more satisfying in the end.

You might try Rory’s suggestion for “populating” your list box. Rather clever, I think. It keeps the list up to date as you add or remove reports or change report names, and it doesn’t require a separate table. I was trying to figure out a way of doing that but was unsuccessful. If you want to exclude certain reports (e.g., subreports as Rory mentions or other reports used for other purposes), just name your reports in a clever way. For example, begin the names of the ones you want to include with something like “List”, then add the following filter to Rory’s query:

SELECT Name from MSysObjects WHERE Type = -32764 AND Left(Name,4)=”List”;

Good luck.

Reply | Quote

… Another option is to create a form with checkboxes, one for each report. Put the report name next to the checkbox as text for the user to reference. Then build an event for each checkbox that will open the report whenever the checkbox is checked. See Below:

Private Sub Check94_Click()
If Forms!FormName!Check94.Value = True Then
stDocName = “ReportName”
DoCmd.OpenReport stDocName, acPreview
End If

End Sub

… Or you could create a preview button that opens all reports checked using the same logic.
Good luck.

Reply | Quote