List box for reports

  • Thread starter Thread starter Carla
  • Start date Start date
C

Carla

I have seen this done but do not know how to accomplish it. I have 5 reports
in an access 2007 database. I want to have a listbox on a form that lists
the report names :report1, report2, report3,report4,report5 etc. and lets the
user pick the report so it can be printed. I know how to make a list box but
not how to make it list forms or reports instead of items listed in a table
for example. Can you point me in the right direction?

Thanks
Carla
 
I have seen this done but do not know how to accomplish it. I have 5 reports
in an access 2007 database. I want to have a listbox on a form that lists
the report names :report1, report2, report3,report4,report5 etc. and lets the
user pick the report so it can be printed. I know how to make a list box but
not how to make it list forms or reports instead of items listed in a table
for example. Can you point me in the right direction?

Thanks
Carla
Click to expand...

To have a list box (or a Combo Box) display the list of *all* reports
is easy enough.

Set the list box (or Combo Box) RowsourceType to table/query

Then set the List Box Rowsource Property to:

SELECT MSysObjects.Name FROM MSysObjects WHERE
(((Left([Name],1))<>"~") AND ((MSysObjects.Type)=-32764)) ORDER BY
MSysObjects.Name;

Set the Column Count property to 1.
Set the Bound column property to 1.
Set the column Width to 2"

However, this will display all the reports with their (probably) user
unfriendly names, i.e. "rptMonthlyServiceBreakdowns", etc., as well as
reports that you may not wish to be available to the user, such as
reports that are used as sub reports.

A better solution might be to create a table with 2 text datatype
fields.
TableName = tblReports
FieldName1 = [ReportName]
FieldName2 = [FriendlyName]

In the [ReportName] field enter the actual report name,
i.e. rptMonthlyServiceBreakdowns
In the [FriendlyName] field enter a more user friendly name,
i.e. Maintenance

Then code the List Box Rowsource property:
Select tblReports.* From tblReports Order By [FriendlyName];

Set the Column Count property to 1.
Set the Bound column property to 2.
Set the Column Widths property to 0";1"

Now you can enter into the table just those reports you wish the user
to be able to run. The user will only see the FriendlyName.

In either case, code the List Box AfterUpdate event:

DoCmd.OpenReport Me.ListBoxName, acViewPreview

to open the selected report in Preview.
 
Back
Top