Listboxes: How to select reports

[Kath via AccessMonster.com]

Hi there, I have spent some time now searching and have not found the answer. Sorry if this is basic.
I have a form that I have a control for a list box, which is pulling the report names from the mysysobjects table. SQL as follows:
SELECT msysObjects.Name
FROM msysObjects
WHERE (((msysObjects.Type)=-32764))
ORDER BY msysObjects.Name;

This pulls all the names of my reports, however, now I am not sure what I need to do to program the cmd buttons for "preview" or "print" that will refer to the item highlighted. Any ideas would be great.
Thanks!
Kathie

 

[Jeff Conrad]

Hi Kathie,

Hi there, I have spent some time now searching and have not found
the answer. Sorry if this is basic). I have a form that I have a control
for a list box, which is pulling the report names from the mysysobjects table.
SQL as follows:
SELECT msysObjects.Name
FROM msysObjects
WHERE (((msysObjects.Type)=-32764))
ORDER BY msysObjects.Name;

This pulls all the names of my reports, however, now I am not sure what
I need to do to program the cmd buttons for "preview" or "print" that will
refer to the item highlighted. Any ideas would be great.
Thanks!
Kathie

Three possibilities below:

1. Here is some great information on this just recently posted by MVP Allen Browne:Two methods are explained in this article:
List Box of Available Reports
at:
http://members.iinet.net.au/~allenbrowne/ser-19.html
The first is much simpler, as it just uses a query.
[/QUOTE][/QUOTE][/QUOTE]

2. Here is also some information by MVP Dirk "Yoda" Goldgar in the same thread:It's quite possible to build a query that returns a list of all the
report objects in the database, but I find it more useful to have a
table of reports, and use that (or a query of it) as the row source of a
list box. That way, my table can leave out subreports, can include a
descriptive name for each report so the user doesn't have to see the
actual object names, and can have a list-sequence field to control what
the order in which the reports appear in the list box.

Suppose you have such a table, tblReports, with fields ReportName,
ReportDescName, and ListSequence. Then you could create a popup form
with a list box, lstReports, with these properties:

RowSource: SELECT ReportName, ReportDescName
FROM tblReports ORDER BY ListSequence;

ColumnCount: 2
BoundColumn: 1
Width: 2"
ColumnWidths: 0"; 2"
Multiselect: None

The popup form would also have a command button, cmdOpenReport, with
this event procedure for its Click event:

'----- start of code -----
Private Sub cmdOpenReport_Click()

With Me!lstReports
If IsNull(.Value) Then
MsgBox "Pick a report first!"
Else
DoCmd.OpenReport .Value, acViewPreview
End If
End With

End Sub
'----- end of code -----

So any time you want to present the list of reports for opening, you
just open this form.
[/QUOTE][/QUOTE][/QUOTE]

3. Go to MVP Sandra Daigle's site for a working example:

http://www.daiglenet.com/msaccess.htm

Download the "ReportDialog" file.
Should be just what you need.

Hope that helps,

 

[Kath via AccessMonster.com]

Jeff,
Wow...thanks a lot. The downloadable example is great and just what I needed. You saved me some worry lines! Much obliged. ~Kathie

 

[Jeff Conrad]

Jeff,
Wow...thanks a lot. The downloadable example is great and just
what I needed. You saved me some worry lines! Much obliged. ~Kathie

<g>
You're welcome, glad to help!