Command button report selection

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings,

I'm trying to create a Form that based on the users combo box selections,
the correct report will run when a command button is selected.

This is only a two variable model in that I would like "Report A" to run
when "A" is selected in the combo box or "Report B" to run when "B" is
selected.

Is there a way to code the command button to run the correct report based on
the selections?

Thanks in advance!
 
DK,

Not only is it possible, but also easy to take it a step further!
Instead of typing in the possible values for the combo, change its Row
Source Type property to Table/Query, and set the Row Source proprty to:

SELECT Name FROM MSysObjects WHERE Type = -32764

This will force the combo to "read" dynamically the list of reports, so
you don't need to change the form design when you add a new report or
delete an obsolete one. The exact same thing will work with a listbox
instead of a combo, which I personally prefer for this particular use.

Having done this, and assuming the name of the combo is cboReports, the
code behind the command button should be:

vRep = Me.cboReports
If Len(Me.cboReports) = 0 Then
Msgbox "No report selected!", vbExclamation, _
& "Problem running report"
Exit Sub
End If
DoCmd.OpenReport vRep, acViewPreview

to preview the report, or, to print it diretly (to the default printer),
change the last argument form acViewPreview to acViewNormal.

HTH,
Nikos
 
I would not expose a list like this to my users. My report names are not for
public consumption since they use a naming convention like "rptEmpOTHrs"
etc. Also, selecting from msysobjects would list all of my subreports which
are not meant to be viewed alone.

It take a couple minutes to create a table of reports with a structure like:

tblReports
=============
rptRptName ->report object name
rptTitle ->user exposed name
rptComments ->what the report is used for
rptStatus ->active or under development...
rpt... other fields that might help
make the system more manageable

Use the table as the source for the combo box or list box.
 
Nikos,

Thanks. It works GREAT!

Can you please show me how to modify the code...
SELECT Name FROM MSysObjects WHERE Type = -32764
to return only "Report A" or "Report B" in my list box?

I would like users only to be able to select two different reports instead
of the entire list of reports that appears.

Thanks again,
 
Nikos,

Thanks. It works GREAT!

Can you please show me how to modify the code...
SELECT Name FROM MSysObjects WHERE Type = -32764
to return only "Report A" or "Report B" in my list box?

I would like users only to be able to select two different reports instead
of the entire list of reports that appears.

Thanks again,
Click to expand...

It appears you do not wish to have the user select any report other
than report A or report B.
All you need do then is set that combo box Row Source Type to Value
List.
As Row Source, enter
"ReportA";"ReportB"

Make sure The bound column of the combo box is set to 1.
Set the Column Widths property to
1"

Code the Combo AfterUpdate event:
DoCmd.OpenReport Me!ComboName, acViewPreview

I would make it even simpler for the user. Instead of a Combo Box, use
an Option Group with 2 option buttons.
Code the AfterUpdate event of the OptionGroup:
If Me!OptionGroupName = 1 Then
DoCmd.OpenReport "ReportA", acViewPreview
Else
DoCmd.OpenReport "ReportB", acViewPreview
End If

To me it's simpler. The user need not click on the combo to have it
dropdown to select the report. Simply click either option button 1 or
option button 2 labeled with the associated report name, and the
report will open.
 
I appreciate the help to all who posted. I went from not knowing how to do
this, to having three different ways.
 
Duane,

Thanks for the post. I was doing something similar with the Report table
but took it a step further and added a field for the user defined name as my
reports also use the naming convention rpt and not all reports are for users'
eyes.

Thanks for the input.
Marcia
 
Back
Top