Combobox to open a report (1 Viewer)

Hi all, I want to use a combo box to display a selected set of reports in a form, when report is selected from combo box , the report must open. I would also like to use the same principle with forms. How would I go about it.

You could simply store the report names in a table and query the table to populate your combo. This might also give you the opportunity to store a more descriptive name for the report and display that instead of the actual report name.

Alternatively you can query the MySysObjects, to list all the reports in your database.

This might not be what you are after, you could use a naming convention for you reports and restrict the query to only display the ones that matched that?

Opening the report after selection is straight forwards, depending on how you have listed it.

create a New table (say tblFormReports) with this structure:

ID autonumber
objType ShortText (10) 'Report or Form
objName ShortText (100) 'actual name on your database
objDesc ShortText (100) 'descriptive text


Now add the names your reports and forms to this table, ie:

objType objName objDesc
------------------------------------------------------
frmCustomer Form Customer Information
frmSupplier Form Supplier Detail
frmProduct Form Product Data Entry
rptProduct Report Product Listing Report
rptCustomer Report Listing of All Customers
rptInventory Report Inventory Report

to show only Forms to your combo, the rowsource is:

"select objName, objDesc From tblFormReports Where objName='Form';"

on the Click Event of your your command button to show the form (after selecting from the combobox):

Private Sub button_Click()
DoCmd.OpenForm Me.Combo.Value
End Sub

same thing with report:

"select objName, objDesc From tblFormReports Where objName='Report';"

Private Sub button_Click()
DoCmd.OpenReport Me.Combo.Value
End Sub