Launching a report from a Form

[Joe]

I am sorry to ask this since I saw it yesterday but I can not find it today.
I have 20 reports I would like to select similar to the function of the
switchboard. I have been using the switchboard with sub-switchboards but
obviously there is a better way. I would have the switchboard call this form
and from the form I would click on the report and it would run.

 

[Daniel Pineault]

Create a form with a listbox which lists all the reports then create a click
or double-click event which opens the selected report using the
Docmd.OpenReport method.

Take a look at http://allenbrowne.com/ser-19.html for an explanation on
how-to build a list of report dynamically.

 

[Joe]

I was just thinking just listing the name on the form and clicking on them. I
assumed I would somehow use the "on dbl click" commend. I do not know what
type of box to use. (commend button?)

 

[Klatuu]

Listing the reports on the form is not a good idea. Each time you add a new
report, you have to modify and redistribute your appliction.

I would suggest using a combo box to select the report. Here is a query
that will return the name of all reports in your database:

SELECT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Type) =
-32764));

Use that as the row source for the combo. Then in the After Update event of
the combo box:

Docmd.OpenReport Me.cboReports, ....

This way, when you add a new report to your database, there is nothing else
to do. It will be picked up by the query.

 

[John Spencer]

If you add a report you have to redistribute the application anyway.

I prefer a form based on a table that lists my reports. The advantage I
find with a table is that I can include a description of the report to
display when the report is selected. Also, I can include other
information in the reports table.

For instance, I have an archive field in the reports table. This allows
me (or an administrator) to remove the report from the list of available
reports. Then when the customer decides that they do want that old
report after all, the adminstrator (or I) unchecks the archive field and
the report is once again available.

For complex applications with many reports I also can sub-categorize
reports by area of interest.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Klatuu]

Good idea, John.
True about the redistribution, but in my world it shortens the QA and
implementatin review process. Only the report has to go through, not the
modified form.

 

[Joe]

You are being very helpful but I am not very smart. I type what you had
except for the letter case and it did not do anything. I entered both of the
lines in the parameters box. I assume certain parts have to have the correct
name instead.
Was I supposed to do somthing after the 3"..." Docmd.OpenReport
Me.cboReports, ....
Sorry for not keeping up.

 

[Klatuu]

I don't understand your question. What parameters box are you talking about?
This actually has nothing to do with parameters. Put the query I posted in
the row source property of the combo box. Then when you open the combo, a
list of all your reports should show. Then with the Docmd.OpenReport in the
combo's After Update event. the report should run once you select it from the
combo.

However, see John Spencer's post. I like his idea of building your own
table with the report names and other info that may be useful.

 

[Joe]

I meant the "proprieties" box. The box that comes up when you right click on
the Combo box.

 

[Klatuu]

Okay. The code for the Query should go directly into the Row Source property.

For the After Update event, you don't put the code there. Right click on
the small command button with the 3 dots just to the right of the property.
Select Event Procedure when the box shows up with the options. The VBA
editor will open with the cursor in the event sub. Put the Docmd.OpenReport
there.

 

[Joe]

I am getting close now I see the list of the reports.
Now when I click on the highlighted report title I get an error.
This is what the screen looks like:

Combo() AfterUpdate
Option Compare Database

Private Sub Combo0_AfterUpdate()
DoCmd.OpenReport Me.cboReports
End Sub

The Error is: Compile error
Method or data member not found

The form is called "Report Select Form"

I appreciate you dealing with me for so long.

 

[Klatuu]

I should have told you to use your own names. cboReport was just a name I
used for the example. It appears yours should be:

DoCmd.OpenReport Me.Combo0

 

[Joe]

Now it works but there is one detail, It now goes directly to the printer
instead of the screen first.

 

[Klatuu]

put the argument in the openreport to make it preview.
See VBA Help for OpenReport method

 

[Joe]

Thank you for sticking with me, I saw how to do the preview.
I thank you so much for all of the help.